Sparklines make headlines

BY J. CARLTON COLLINS, CPA

Q: We produce a monthly report in Excel summarizing the previous 12 months’ accounts-receivable balances for each of our 2,600 customers, and it is my job to analyze this data to identify growing A/R balances that indicate customers who might require special attention. Ideally, I’d like to chart each of the 2,600 rows of data so I can visually identify customers whose outstanding balances are trending upward, but creating and updating this many charts is too time-consuming to be practical. Can you suggest a different approach?

A: Excel 2010 provides a new tool called Sparklines, which enables you to create a small chart within a single cell and quickly copy that chart to other cells. Using this approach, you could create 2,600 charts in just a few seconds. As an example, I created a workbook containing 12 months of A/R balances for 2,600 customers (pictured in columns A through M below). To create the Sparkline, I placed my cursor in cell N2, and, from the Insert tab, I selected Line from the Sparklines tools group, highlighted the data range (B2:M2 in this example), and clicked OK.

This action produced a small line chart in cell N2. Next, I copied the Sparkline in cell N2 and pasted it to the remaining 2,599 cells below to display 2,600 line charts depicting trends for each customer’s A/R balance.

Note: Sparklines are sometimes easier to read if you increase the column width and row height of the cells containing Sparklines. Additional Sparkline settings allow you to change a Sparkline’s color and width, emphasize high points, display the Sparkline using a column format, or adjust other settings. Two examples of these additional settings are pictured below.

While Sparklines may add readability to your analysis, I also would recommend adding a formula to help you identify specific customers with growing A/R balances. For example, in column O below, I used the IF function to add the calculation =IF(M2/AVERAGE(J2:L2)>1.1,"Attention","") comparing each customer’s most recent A/R balance to the average balance for the previous three months. If the current A/R balance exceeds the average balance for the preceding three months by more than 10%, then the word “Attention” is displayed. (Of course, you would need to decide how many months to use in computing your average, and the percentage threshold that warrants attention.)

SPONSORED REPORT

How to audit high risk areas

Revenue recognition, internal control over financial reporting, accounting estimates and going concern are areas of audit that have emerged as particularly challenging and complex.

NEWS

Revenue recognition revisited

A reexamination of new revenue recognition rules has led to tinkering with the standard that is considered the biggest achievement of the convergence efforts of FASB and the International Accounting Standards Board.

INTERVIEW

Staying focused at the top

Olivia Kirtley, CPA, CGMA, an accomplished corporate director with almost 20 years of experience serving on boards, talks about strategic, risk, and compliance issues that keep board members up at night.