Planned vs Actual Chart In Excel - A complete tutorial

How Planned vs Actual Chart In Excel Can Ease Your Pain

The planned vs Actual chart in excel will give you an edge over traditional tabular analysis. In fancy terms, we call it as a Budget vs Actual analysis or Variance analysis.

Indeed, it’s quite essential to assess the performance of our plans against actual results periodically. So, in this step by step tutorial, we are going to take a look at how to build planned vs actual graph in excel.

Planned vs Actual Chart In Excel - A complete tutorial

Steps to build Planned vs Actual Chart in Excel:

Step#1

It all starts with the data, your data should be in a nice tabular format like the one I have below.

Plan vs Actual graph data

Step# 2

Select A2:C6 range → Insert → Bar Chart → Clustered 2D bar chart

The planned vs Actual Graph Example #1

Step#3

Remove unnecessary grid lines to have a white space around the graph.

Step #4

Right click on ‘Plan’ bar and click on ‘Format data series’ and then select Secondary access from the dialog window.

Plan vs Actual#3

Step #5

Select actual bar → Right Click → Format data series → On the ‘Gap Width’ select 106% → Change fill color according to your requirement.

Step #6

Now select ‘Plan bar’ → Right Click → Format data series → On the ‘Gap Width’ make it 314% →Change fill color according to your requirement.

Also read: How to visualize your data with the help of excel heatmap

Step #7

Select secondary horizontal axis from the top and remove axis scale, you may also want to remove the border from the Graph to increase white space.

So, after all the adjustments planned vs actual graph should look like this.

Plan vs Actual#4

The narrative behind Excel chart Goal vs Actual:

Building Target vs Actual chart in excel is one step closer to storytelling, but it’ll quickly become useless unless it relates to your story.

As you can see, in our above graph Orange color represents ‘Plan’ and ‘Gray’ represents ‘Actual’. If you look at Q116 we were not able to achieve what we had planned.

Similarly, in Q416 we had overachieved to our planned number.

 Also read: 12 Excel skills that are in demand

Conclusion:

Storytelling can be easier and attractive with the help of visualization, especially when presenting budget vs actual numbers during business updates meeting.

If you choose to present data in a tabular format, I guess there is a high chance that your reviewer will get bored.

Instead, you may present data in a structured way with the help of some visual effects to yield better results.

Consider our Planned vs Actual chart in excel tutorial as a base and try to create your own charts. It’s relatively simple and effective.

There is an excellent tutorial on HBR about how to tell a story with the help of data.

Have you tried anything similar to the above? if so why not share your success story with us in the comments section below.

6 thoughts on “How Planned vs Actual Chart In Excel Can Ease Your Pain

  1. Interesting. I think I would switch the Actual vs. Budget/Plan designations myself,
    since that is what I assumed before reading the legend at the bottom. I also prefer to
    use 1Q16, 2Q16, 3Q16, 4Q16 since I think that makes easier to know what you mean.
    Just my two cents.

  2. Excel is a good tool for doing a chart like this however the main problem I see with Excel is that you only get out what you put into it. You do not get any real insight or see anything really unless you do it yourself and excel as a tool is notorious for human error.

    You would be much better off using a software solution on top of your existing Excel to get better produced reports with more accuracy, much better insight and of course the whole process is much faster.

Comments are closed.