The Excel Plan Actual Variance Chart lets you display the monthly variance using custom data visualization.
Previously we have made a chart template on this subject, and this time we will demonstrate the essential tool of company analysis and control from a completely different view. We will emphasize the technical part of the representation because we have introduced the basics in detail today. Let’s start!
In the picture below, the Plan Actual variance chart can be seen, and in this article, we will show you the Excel knowledge needed for its creation step by step. The described period will be the usual 12 months, one business year.
Because short-term planning most often covers one year, we have chosen this period as our analysis’s starting point. The first step is to fill the previously defined data into the Plan, Actual, and Months fields on the data worksheet. Part of it you can see below.
Set up your data
As can be seen, this is not that complicated. On the table, you can place a header, sub-header, and footer that can be used to insert text notes onto the chart. Also, after printing, they will help with interpretation.
For example, this can be the project’s name, timescale, and creator’s name. It is important to note that the template is print-ready, so you will see what you see on the screen in print.
Plan Actual Variance Chart – Elements
The light grey column chart shows the plan value series, and we use dark grey for the Actual value series. We apply a little trick displaying Actual value by choosing the “Series Options” menu. Place a small marker at the end of the thinned column (which almost becomes only a vertical line).
When done with this operation, we already have the diagram displaying the Plan and Actual values and the basics of Excel Dashboards.
We already know the Plan / Actual values for each month; we have to start calculating the differences (positive and negative) to show the direction and amount of the differences by the appropriate arrows. Let’s return to the data worksheet! The picture below will review the calculations to help us proceed.
We will show two examples of the basis of logic for calculating differences. First, the variance section contains three lines and 12 columns; the latter represents the number of months.
Let’s take the month of January when the Actual value (14) exceeded the Plan value (10), so the chart should be displayed a green-colored positive difference. We have processed the importance of January in column B1. The +14 value means choosing the bigger one from Plan / Actual values.
The +4 represents the positive difference. We determined the bigger number using the MAX() formula.
Let’s examine the month of March when the Plan wasn’t realized (20 / 13). The MAX() formula will give the number 20 because now, out of the two numbers, the value of the Plan is larger. In this case, the difference is -7.
With the help of a formula, we have quickly determined the larger number of the two, but how will we know the direction of the difference? Excel has a perfect solution for this issue; the IF() logical function. Here is the formula:
The formula checks the difference between the actual value and the Plan value. It won’t write anything if the result is zero because there is no difference. But, if the final result is not zero, we get the difference.
Copy the formula; those points will emerge, which will be the basis of the green and red arrows on the final chart. So if you create a financial template, we recommend using this chart type.
In the latest version of our data visualization add-in for Excel, we’ve created a stunning solution to create variance charts in seconds. We created this article not only for those who like to sink into the detailed creation of the Plan Actual Variance Chart. There are free-to-download templates at the end of most of our articles, which isn’t different today either.