Budget vs actual template
By the Analistable team · Updated · 3 min read
A free Excel and Google Sheets workbook with a Budget sheet, an Actuals sheet for raw transaction lines and a Variance sheet. SUMIFS totals actuals per cost centre and account, then shows the variance, variance % and a Review flag above 10%. Paste your data over the samples and the formulas update.
Part of our guide: How to reconcile data in Excel
What the template does
Most budget vs actual reports go wrong at the same step: someone totals the ledger by hand and types the numbers next to the budget. This template keeps the raw transaction lines on their own sheet and lets formulas do the totalling, so you can trace every variance back to the lines behind it. It follows the method in comparing budget vs actuals in Excel and is part of the reconciling data in Excel guide.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| Budget | Month, Cost centre, Account, Budget | Yes — one row per budget line |
| Actuals | Date, Cost centre, Account, Description, Amount, Budget line found | Yes — first five columns |
| Variance | Cost centre, Account, Budget, Actual, Variance, Variance %, Flag | Only the first two columns |
How to fill it in
- Paste the budget for the month into Budget, one row per cost centre and account.
- Export the month's transactions from your accounting system and paste them into Actuals. Spell the cost centre and account exactly as in the budget.
- Check the Budget line found column: any row saying 'No — add to budget' is spend that has no budget line and will be missing from the variance.
- List the cost centre and account pairs on Variance; the other columns calculate.
How the formulas work
Actual spend for a budget line is a two-condition SUMIFS over the Actuals sheet:
=SUMIFS(Actuals!$E$2:$E$500,Actuals!$B$2:$B$500,A2,Actuals!$C$2:$C$500,B2)Variance % divides by budget, and IFERROR stops a zero budget from showing #DIV/0!:
=IFERROR(ROUND(E2/C2,4),0)The flag marks anything more than 10% away from budget in either direction:
=IF(ABS(F2)>0.1,"Review","OK")If your budget is spread over several monthly tabs, the SUMIFS across multiple sheets article shows how to extend the same formula.
Worked example
The sample data is one month for three cost centres. The Variance sheet produces:
| Cost centre | Account | Budget | Actual | Variance | Variance % | Flag |
|---|---|---|---|---|---|---|
| Sales | Travel | 2000 | 2150 | 150 | 7.5% | OK |
| Sales | Software | 1500 | 1500 | 0 | 0.0% | OK |
| Sales | Entertainment | 800 | 420 | -380 | -47.5% | Review |
| Marketing | Advertising | 5000 | 5450 | 450 | 9.0% | OK |
| Marketing | Software | 1200 | 1200 | 0 | 0.0% | OK |
| Operations | Rent | 4000 | 4000 | 0 | 0.0% | OK |
| Operations | Utilities | 600 | 585 | -15 | -2.5% | OK |
| Operations | Supplies | 400 | 475 | 75 | 18.8% | Review |
| Total | 15500 | 15780 | 280 | 1.8% |
Overall spend is only 1.8% over budget, but the line view shows supplies running 18.8% over and entertainment well under — the kind of detail a single total hides.
Limits and when to move on
The template handles one month and a few hundred lines comfortably. It gets awkward when actuals arrive as several exports (one per entity or per month) or when account names differ between the budget and the ledger. At that point, stack the exports with Power Query, or load the budget and ledger files into Analistable and ask 'which accounts are more than 10% over budget this quarter?' — the files stay in your browser.
Flip the sign of the variance if you report income lines, where over budget is good.
Frequently asked questions
- Does it work in Google Sheets?
- Yes. It uses only SUMIFS, COUNTIFS, IF, IFERROR, ABS and ROUND, which behave the same in Excel 2016, Excel 365 and Google Sheets. Upload the .xlsx to Drive and open it with Sheets.
- Can I compare a full year?
- Add the month as a third condition in SUMIFS, or put a year-to-date budget on the Budget sheet and paste year-to-date actuals.
- Why is an actual missing from the variance?
- Its cost centre or account is spelled differently, or there is no budget line for it. The Budget line found column on Actuals shows which rows are affected.