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 details today will place emphasis strictly to the technical part of representation. Let’s start!
On the picture below the Plan Actual variance chart can be seen and in this article we will show you step by step the Excel knowledge needed for its creation.
The described time span will be the usual 12 months, which is one business year.
Because short term planning most often covers one year we also have chosen this timespan for 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 header, sub-header and also footer that can be used for inserting text notes onto the chart. Also after printing they will help interpretation.
For example this can be the name of the project, timescale, creator’s name …just 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
Plan value is represented by the light grey column chart 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 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) so that we can show the direction and amount of the differences by the appropriate arrows.
Let’s return to the data worksheet! On the picture below we will review the calculations that will help us proceed.
We will show you by two examples the basis of logic for the calculation of differences. 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 values of January in column B1. The +14 value means that we have chosen the bigger one from Plan / Actual values.
The +4 represents the positive difference. We determined the bigger number with the use of 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 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 a 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 very good solution for this issue. And this is the IF() logical formula. In our case the use of it is really 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 wouldn’t write out anything if the result is zero because there is no difference. If the final result is not zero we get the difference as result.
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. If you are creating 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. At the end of most of our article there are free to download templates and it isn’t different today either, here is you link to the free Excel template.