Excel Variance Analysis

An alternative charting technique

It is common in accounting to compare numbers. Either Actuals vs Budget or this year vs last year. There are different charts that people use for this comparison. How about plotting the variance instead?

When you are comparing two data sets you typically want to know what happened in the current period and Year To Date (YTD). Let’s do a Current Year (CY) and Last Year (LY) comparison.

Sometimes you chart both measures, but that doesn’t really help you identify problems and how the YTD variance is progressing.

The table below contains the figures I will use for the demonstration. A positive variance means that this year is above last year’s result.

Variance table

To create the chart I used the Ctrl key to select the two ranges as shown below. Select A1:A9 as normal then hold the Ctrl key down and use the mouse to select D1:E9.

Variance Ctrl key selection

Click the INSERT tab. In Excel 2013 clicking on the Recommended Charts icon will provide a quick solution – see below.

Recommended ChartsVariance Chart recommended

This chart shows the monthly variances as column charts as well as the YTD variance as a line chart. I removed the chart Gridlines on the chart below ( I don’t like gridlines – personal preference).

Variance Chart

The only time this chart is not useful is when the monthly variance is only one way eg always negative. That causes the vertical axis scale to increase for the YTD line as the year progresses. This causes the column charts to become smaller and less readable as the year progresses.

The above chart allows you to see each month’s variance, whether it is positive (good) or negative (bad), as well as the cumulative YTD variance.

The chart shows that the year started well, but has been running below last year for most of the year.

You can download the above chart at the link below.

Variance example file

Please note: I reserve the right to delete comments that are offensive or off-topic.