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

Taking stock of artificial intelligence

Artificial intelligence is either the greatest thing to ever happen to human work or the dread of our existence. This independently written report explores how AI will reshape the workplace and how analytically minded individuals can stand out.

PODCAST

How tax reform will impact individual taxpayers

Amy Wang, a CPA who is a senior technical manager for tax advocacy at the AICPA, answers to some of the most common questions on how the new tax reform law will impact individual taxpayers.