Budget vs Actual Excel Chart
Creating a budget vs actual comparison for your company will help you as a business owner to compare your projected budget value against its actual value. One of the main benefits of why this is necessary is that it will help you as a manager to see if you are on track to meet your financial goals over time.
It can furthermore assist you to see if your budget goals were actually realistic enough to achieve. A lot of times business owners tend to set unrealistic budget for a certain project or operation. Then when that goal is not met they tend to get discouraged. They see that they have gone way over their budget estimate.
If they had taken the time, to analyze their situation by even doing a budget vs actual estimate. They could’ve prioritized their timeframes to see the different times when most of their budget was on or off track. At the times when it can be a bit off they could further dissect the situation to see what really caused it.
Excel is probably one of the easiest software you can use to create a budget vs Actual chart. It’s also pretty simple to use once you get a hang of certain features. If you want to move further up the pyramid in terms of skill. You got to be ready to put in a lot of work.
Looking on the bright side of things, this tutorial is going to be pretty simple and if you’re a beginner. You will have no problem executing these steps below.
Excel Chart Budget vs Actual:
We are going to create a budget vs actual chart based off of monthly targets but you can use this as a template to modify it to your needs once it’s done.
Step 1
Firstly create three columns these are going to be months, actual and target.
Step 2
You are then going to populate the cells with the different data. Actual budget should be in the actual cell and your estimated budget in the target cell for each month.
Step 3
Now it’s time for the main part which is the charts. You are going to use a contrast chart because it enables you to see the data more easily. You are going to have the actual values in shade probably a dark shade to help differentiate between the two values. First off select all the data. Click on the insert tab, select charts. Next go to the column option and choose a 2-d clustered column.
Step 4
Choose any of the actual value bars. Next you are going to format the data by right clicking and select format data series.
Step 5
You will now see a format series dialogue box pop up. Click on the secondary axis option which is at the bottom in the plot series area.
Step 6
Choose one of the target value bars. Then you are going to format it by right clicking and select format data series.
Step 7
Now you are going to increase the width of the bars which is to make it kind of wider than it usually is. So on the format data series dialogue, you are going to reduce the gap width to your preferences but for this one you can move it to 100%.
Step 8
Next choose one of the actual value bars. Apply the same process which has been shown in step 7 above by right clicking then go on to the format series dialogue box and change the gap width to 200%.
Step 9
Most of the work is now done. All you got to do now is shade the bars on the target area in a lighter color than the actual bar. This will help you to see the difference between the two data better.
In summary this is a very simple strategy you can use to create a budget vs actual chart in Excel. If you want to you can even download a template off of the internet by doing some quick searching on google. But this tutorial is pretty simple and if you are a beginner this can help you out a lot. You can see an example below.