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.)

MANAGEMENT ACCOUNTING

Developing finance leaders

A good leader recognizes that part of the job is developing the next generation of leaders. Veronica McCann, CGMA, a former division CFO at Commerzbank in Singapore, shares tips on developing future finance leaders.

PROFESSIONAL ISSUES

Belicia Cespedes: A CPA at 17

Through hard work and determination, Belicia Cespedes earned the credential before she was even eligible to vote.

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.