The Plan Actual Variance Chart enables a very spectacular variance representation.
Previously we have made a chart template in this subject, and this time we will demonstrate the essential tool of company analysis and control from a completely different view.
Because we have introduced the basics in detail today, we will emphasize the technical part of the representation. 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 time will be the usual 12 months, one business year.
Because short-term planning most often covers one year, we have chosen this timespan as the starting point for our analysis.
The first step of creating the template is filling up the previously defined data to Plan, Actual, and Months fields on the data worksheet. Part of it you can see below.
Plan Actual Variance Chart – Setup
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 interpretation.
For example, this can be the project’s name, timescale, creator’s name to mention a few. It is important to note that the template is print-ready, so in print, you will exactly see what you see on the screen.
Plan Actual Variance Chart – Elements
The light grey column chart represents plan value, and the Actual value is represented by dark grey. We have applied a little trick portraying Actual value by choosing the “Series Options” menu, and we have placed a little distinguishing mark at the end of the thinned column (which is almost become only a vertical line).
When we are done with this operation, we have already got the diagram displaying the Plan and Actual values, the basics of the Excel dashboard.
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! In the picture below, we will review the calculations to help us proceed.
We will show you by two examples the basis of logic for calculating differences. First, the variance section contains three lines and 12 columns; of course, the latter represents the number of months.
Plan Actual Variance Chart – Basic Calculations
Let’s take the month of January when the Actual value (14) exceeded the Plan value (10), so on the chart should be portrayed 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 as a result, because now out of the two numbers, the value of the Plan is larger. In this case, the difference is -7.
This is all well done. 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. And this is the IF() logical formula. In our case, the use of it is not complicated. Here is the calculation: IF(C11-C10=0;””; C11-C10). And what is this formula doing exactly?
It checks the difference between the actual value and 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.
Bringing this formula throughout the chart, those points will emerge, which will be the basis of the green arrows pointing upward and the red arrows pointing downward on the final chart. So if you create a financial template, we recommend using this chart type.
In the latest version of UDT (our pro chart add-in), we’ve created a stunning solution to create variance charts in seconds.
Fortunately, this page wasn’t only created 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, and it isn’t different today either. So here is your link to the free Excel template.