Understanding and managing your money is important in both business and personal life. One useful tool for this is budget vs actual analysis. In this guide we explain what budget vs actual means for a business, how to calculate budget variance, the types of variance you will run into, what to do when your plan and your results don’t match up, and the reports that pair well with a budget. We also hand you a free Budget vs actual template from Sheetgo: one Google Sheets file you copy to your own Drive, with no Sheetgo account needed. Plus, we’ll keep it simple and give you practical advice without using confusing words.
What is budget vs actual?
Budget vs actual is a straightforward financial analysis method that helps you compare what you planned (the budget) with what actually happened (the actuals). The gap between the two is called the variance, and it’s what turns a budget from a wish list into a management tool.
How to calculate budget variance?
Step 1: Collect your data
Put your budgeted figures and your actual figures side by side in a spreadsheet, split into income and expenses. Use the same category names on both sides, otherwise you have nothing to compare.
Step 2: Use the variance formula
Variance = actual amount − budgeted amount. Work it out line by line, not just on the bottom line: a total that looks healthy often hides one category that is badly off. It also helps to express the gap as a percentage of the budgeted figure, so a $500 miss on a $1,000 line doesn’t get lost next to a $500 miss on a $50,000 one.
Step 3: Read the sign in context
The same arithmetic means opposite things on either side of the sheet. On income, an actual figure above budget is favorable. On expenses, an actual figure above budget is unfavorable. So label every line favorable or unfavorable before you draw any conclusion from it.
In the free template further down this page, the Budget Analysis, Income Analysis and Expenses Analysis tabs run this comparison for you: forecast next to actual, with the under/over amount and the variation %.
What are the types of budget variance?
Budget variances can be grouped into three types:
Favorable (good news) variance:
Occurs when actual revenues are higher than budgeted, or actual expenses are lower than budgeted.
Unfavorable (not-so-good) variance:
Happens when actual revenues fall short of budgeted amounts, or actual expenses exceed the budgeted figures.
Revenue variance:
This focuses on the difference between what you expected to earn and what you actually earned. It’s worth keeping it separate from the cost side, because the two are usually driven by completely different things: the template gives each its own tab.
What do you do when your budget and actuals don’t line up?
Fixing differences between your budget and actuals involves:
- Taking a close look: Examine each item to find out where the difference is coming from.
- Adjusting future plans: Use what you learn to make your future budgets more accurate.
- Implement controls: To prevent significant variances in the future, implement better financial controls and monitoring systems.
Example: If marketing expenses exceed the budget, delve into specific campaigns, evaluating their success and any unforeseen costs contributing to the variance. Adjust future budgets to accommodate the successful campaign’s impact. Strengthen controls in the marketing department by setting spending limits, requiring pre-approval for large expenses, and enhancing transparency in financial processes. This proactive approach not only resolves immediate issues but also fortifies your financial framework against potential variances in the future.
Reports that complement budgets vs actuals
Boost the power of your budget vs actual analysis with these extra reports:
- Cash flow statement
This shows how much money is actually available to your business, month by month. That guide comes with its own free Google Sheets template. - Income statement
A detailed overview of how well your business is doing financially over a period. That guide comes with its own free Google Sheets template too.
Free budget vs actual template from Sheetgo
To make budgeting easier, Sheetgo publishes a free Budget vs actual template for Google Sheets. It’s a single spreadsheet file: you copy it to your own Drive and it’s yours to edit. Nothing to connect, nothing to set up, and no Sheetgo account needed.
Here’s what you get inside the file:
- Inputs — set the year you want to analyze. The rest of the file keys off it.
- Income Forecast — the income you planned, one row per month and one column per income category. Rename the categories to match your own business.
- Income Actual — the income that really came in, one row per invoice: client, invoice number, amount, date, category and notes.
- Expenses Forecast — the same idea on the cost side: what you planned to spend.
- Expenses Actual — what you actually spent.
- Budget Analysis — the headline comparison. Actual against expected operating profit, actual income against forecast, and actual expenses against budget, month by month plus year-to-date.
- Income Analysis — forecast next to actual for every income category and every month, with the under/over amount, the variation % and a year-end total.
- Expenses Analysis — the same breakdown for your costs.
There’s also a Read me tab you can go back to whenever you need a reminder of how the file fits together.
How to get the Budget vs actual template
Click the button below, then click Make a copy to save your own free Budget vs actual template in Google Sheets by Sheetgo.
Get started
Step 1: Set the year you’re analyzing
Open the Inputs tab and set Year to analyze to the year you’re reporting on. The analysis tabs use that year to lay out their monthly columns, so it’s worth getting right before you type anything else.
Step 2: Enter your budget in the Forecast tabs
Go to Income Forecast and fill in the income you expect, month by month. The column headers are your income categories, so rename them to whatever you actually sell. Then do the same in Expenses Forecast for the money you plan to spend. Together, these two tabs are the budget half of the comparison.
The file arrives with sample figures in it so you can see how everything behaves. Delete the sample rows before you start entering your own.
Step 3: Log what actually happened in the Actual tabs
Income Actual is a running log rather than a grid: one row per invoice, with the client, invoice number, amount, date and category. Expenses Actual works the same way for money going out. Use the same category names you used in the Forecast tabs, otherwise the analysis has nothing to line your actuals up against.
Step 4: Read the headline variances in Budget Analysis
The Budget Analysis tab updates as you enter data. It puts your actual operating profit against the expected figure, your actual income against the forecast, and your actual expenses against the budget, for every month of the year plus a year-to-date column. This is the view to open first: it tells you which side of the business the problem is on.
Step 5: Drill into the detail
Once you know where the gap is, Income Analysis shows you why. Every income category gets a forecast column and an actual column for each month, with the under/over amount, the variation % and a year-end total. Expenses Analysis does the same for your costs. This is where a variance stops being a number and turns into something you can act on: one client who paid late, one category you consistently underestimate.
That’s the whole loop: your plan in the Forecast tabs, reality in the Actual tabs, and the variances calculated for you in the Analysis tabs.
FAQs (Frequently asked questions)
Q: Why is budget vs actual analysis important?
A: It helps you see how well you’re doing financially and makes it easier to decide what to do next.
Q: How often should I do budget vs actual analysis?
A: It’s a good idea to check every month or every few months, depending on how often you want to keep an eye on things.
Q: Can I use budget vs actual analysis for my personal finances?
A: Absolutely! You can apply the same ideas to manage your personal finances and see where your money is going.
In summary, understanding and analyzing the differences between your budget and actual results is crucial for managing your finances effectively. This blog post has given you practical insights, whether you’re new to spreadsheets or have some experience. Use this knowledge to make informed financial decisions and find success in your financial endeavors.
Frequently asked questions
Is the budget vs actual template free?
Yes. It is a Google Sheets file you copy to your own Drive and keep. There is nothing to install and no account to create.
Where do I see the variance?
On the analysis tabs. Once you have filled in the forecast and actual tabs for the year you set in Inputs, those tabs show each line against plan.
Can it flag variances for me automatically?
The spreadsheet shows you the numbers, but it does not send anything. Alerts and follow-ups are what Sheetgo’s finance virtual employee handles.
English 
Español 
Português do Brasil 
Français