Many companies have a difficult time creating charts of spending variances. In fact, many companies rely on charts that are nearly useless for that purpose.
The following chart shows an example of a worthless chart that far too many companies use. This one compares actual and budgeted spending for January through October, on a year-to-date basis.
The problem with this chart is that it hides actionable information; it doesn’t display it.
This chart’s most obvious information, expressed by the upward-sloping lines, is that year-to-date (YTD) budgets and spending ratchet upwards during the year. That is, the chart is designed to emphasize a mathematical certainty.
The only useful information this chart attempts to convey is the amount of the YTD variance, which is the difference between the two lines shown. But because so much vertical space is used to display the YTD totals, the relatively small amount of variance is virtually impossible to estimate.
To understand how effectively this chart hides information, put yourself in the position of a manager reviewing the chart. If you were going to quiz an employee about this sales performance, you would have virtually no facts on which to base a question. And the employee would have no facts on which to base an explanation.
In contrast, the following chart presents the same underlying data, but in a format that communicates actionable information.
In this combo chart, the tan area plot shows the monthly budget for the year. And the black line shows actual monthly spending.
The olive green columns in this chart show the YTD variance. Unlike YTD spending and budgets, variances don’t necessarily ratchet upward. In this chart, for example, the YTD variance was unfavorable (less than zero) during the first four months of the year. And recently, the YTD variance declined in August and September.
Unlike the first chart, this one provides enough information to alert managers to developing problems. It also provides managers with enough detail to begin a reasonable discussion about the causes of the spending variance.
Creating the Chart
The following figure shows the worksheet staging area that supports the chart. When you create your chart, you will select the range B2:E15, as shown here:
The first formula in the DateText column is:
B3: =TEXT(A3,”mmm”) & CHAR(13) & TEXT(A3,”‘yy”)
Because CHAR(13) is the Carriage Return character, this formula causes the year’s text value to wrap below the month’s text value. Excel doesn’t recognize the carriage return character in worksheet formulas, which is why we don’t see it in column B.
Formulas return data for all the columns. Therefore, when there’s no data yet for the numbers in column C and E, the formulas return the #N/A value. This causes the chart to display gaps for the line and column plots.
Initially, I set up the previous and following chart as a line chart. And then, to assign the chart types shown, in the Chart Design, Type group, I chose Change Chart Type. And then, at the bottom of the Change Chart Type dialog, I made the changes shown here:
To allow the gridlines to be seen behind the area plot, I set its fill color to 50%.
The rest of the chart is fairly standard.
Although this chart is a significant improvement on the first one, it still has several problems:
- It’s useless, or nearly so, in the first few months of a fiscal year.
- It mixes monthly and YTD data, which could confuse hurried readers.
- It requires manual Excel work, or some clever formulas, to update the chart’s staging table each month.
- Because charts like these don’t show long-term trends, they often cause management to waste too much energy on random variations in sales variances.
That’s why I prefer rolling 12-month charts, like this.
Here, we easily can see three trends for rolling 12-month data—trends for sales, budgets, and variances. In this version of the chart, the rolling 12-month budget variances have wandered around the zero line for the past year, and the variances are no cause for concern.
The line for actual sales has been growing more quickly than it appears in the chart. Notice that one year ago, in October 2023, annual sales were about $4400. But this month, sales are at $5800, which is more than a 30% increase.
And finally, therefore, I thought it would be useful to plot the growth rate in the rolling 12-month sales.
Because the charts in this article use artificial data, the data I randomly generated gave me an unusually high growth rate—particularly for rolling 12-month performance.
But even so, if you ignore the data and look at the charts themselves, I think you’ll agree that the first chart—which is the most common in my experience—is terrible, and the other charts do offer worthwhile improvements.
