October 28, 2022
Adam Hoeksema
Whether you are a small business owner, a first time Controller, a non profit board Treasurer or a seasoned CPA or CFO, my hope with this article is to answer any questions you might have about comparing budget to actual financial results and provide you with helpful tools and tips to make your day just a bit easier.
I plan to cover the following topics:
- What does budget vs actual mean?
- What should a budget vs actual report include?
- How to calculate budget vs actual?
- How to calculate the variance between budgeted and actual?
- What does a budget to actual analysis tell you?
- How to create a budget vs. actual report?
With that as the game plan, let’s dive in.
What does Budget vs Actual mean?
Budget vs. actual refers to the difference between what you budgeted and your actual financial results. Many businesses will have a budget and will then want to compare the budget to the actual results on a regular basis.
What Should a Budget vs Actual Report Include?
Typically a budget vs. actual report will include a budgeted income statement and compare to your actual income statement results. If you are asked for a budget to actual report, it is usually safe to assume that you only need to provide a budget to actual income statement. Although it is possible to create a forecasted or budget cash flow and balance sheet and compare to your actual cash flow statement and balance sheet each month, this is much less common and quite a bit more complex.
Example of a Budget vs Actual Report
Below you will see an example of a budget vs. actual income statement report. You can see that there are 4 columns.
- Budget
- Actual
- $ Variance
- % Variance
There is no hard and fast rule for the order of the columns. In the example below we show the budget first, then actual, but you can flip flop the order if you would like. We also show the $ variance and then the % variance which is probably the most common way to present a budget to actual report, but again isn’t a hard rule.
How to Calculate Budget vs Actual
In order to calculate a budget to actual report, you first need to have a budget in place. If you don’t already have a budget, you can use our Budget vs. Actual template to create a forecasted budget. The model allows you to enter up to 5 years of historical financial data that you can use to build a budget from.
Once you have a budget in place you will need to record your actual financial performance each month in order to be able to compare the two. In order to produce your actual income statement you will probably need to use Quickbooks or some other bookkeeping software. Quickbooks also gives you the ability to run a budget to actual report which is really handy. But Quickbooks isn’t great about allowing you to build a budget based on a series of assumptions. So you have two options:
- Use a spreadsheet template to create a budget with assumptions (which you can do with our template) and then manually input the budget numbers into Quickbooks. Then you can use Quickbooks for bookkeeping to create your actual financial statements. Quickbooks will then allow you to run a budget to actual report.
- Alternatively you could just keep your budget in a spreadsheet template like our budget to actual template, use Quickbooks to complete your actuals and then manually input your actuals into your budget to actual spreadsheet.
Either way it seems like in order to get the best of both worlds you will need to do some manual entry.
How to Calculate the Variance between Budget and Actual
Once you have a column with your budgeted income statement, and a column with your actual income statement, you can add two additional columns that will give you some at a glance insight on your performance:
- Budget vs Actual $ Variance
- Budget vs Actual % Variance
Let me explain how to calculate both of these numbers.
Calculating Budget vs Actual $ Variance
To calculate your budget to actual $ variance, you need to take your actual number and subtract your budget number to produce a budget variance. For example, if you budgeted $100,000, but you only produced $90,000 in revenue for the period, your budget variance should be $90,000 - $100,000 = -$10,000. For income accounts a negative variance means that you generated less revenue than expected. In other words you are behind budget.
In order to calculate your budget variance for expenses you should take your actual expenses minus your budgeted expenses to produce your variance. If you budgeted $100,000 for a certain expense, but you only spent $90,000, your budget variance should be $90,000 - $100,000 = -$10,000. For expense accounts a negative variance means that you spent less than you budgeted. In other words you are exceeding expectations.
For a revenue account you want a positive variance and for an expense account you want a negative variance.
Calculating Budget vs Actual % Variance
To calculate your budget to actual variance on a percentage basis you will need to take your variance number and divide it by your budgeted number. For example, if you had a budget of $100,000 in expenses, but you only spent $90,000 which means you have a negative variance of $10,000, you will need to take your variance of -$10,000 and divide it by the $100,000 budget number. This will produce a negative variance percentage of 10%.
The reason that calculating variance as a percentage is helpful is because it can give context to the variance. $10,000 might sound like a large variance but if the budget line item was $1,000,000 it is only a 1% variance which means you were right on target with the budget. If the budget line item was only $20,000 and you had a variance of $10,000, your budget variance was 50% which means you weren’t close to hitting the budget at all. Sometimes the raw dollar amount variance doesn’t tell the whole story, so providing a variance percentage can be helpful.
What Does a Budget to Actual Analysis Tell You
The purpose of a budget to actual report is to tell you whether you are exceeding expectations or behind budget in each category. It will tell you the absolute dollar amount that you are ahead or behind, but it will also tell you the relative percentage which is helpful in setting context.
How to Create a Budget vs. Actual Report
If you use Quickbooks or another accounting software you will simply need to manually input your budget and then run a budget vs. actual report. If you don’t use accounting software, or you want more flexibility with how to display your budget to actual reports, you might want to use a budget to actual spreadsheet template.
Budget vs. Actual Excel Template
We have produced a budget vs actual spreadsheet template which will walk you through the process of creating a budget forecast and then monitoring your progress by inputting your actual results. You can access our budget vs actual spreadsheet and watch our demo video below.
Let us know if you have any questions or need help customizing your budget to actual report for your business. We are here to help!
