A budget vs actual report shows, for each line and each month, what you planned, what happened, the variance (actual minus budget), and the variance as a percentage of budget, with year-to-date columns alongside. Every mainstream accounting system can produce one as long as the budget is loaded into it: Xero through Budget Manager, QuickBooks Online on the Plus and Advanced plans, Sage 50 and Sage 200 through their budget features, Business Central through G/L Budgets and Financial Reports. The report becomes a monthly spreadsheet job for three reasons: the budget lives in Excel and never gets loaded, the actuals need reclassifying to match how the budget was built, and the commentary has to be added by hand. A version that updates itself fixes the first two in a model and leaves the third to a person, which is where it belongs.
What should a budget vs actual report show?
The minimum that works in a board meeting:
- Month and year to date. Budget, actual, variance, variance percent, for the month and cumulatively. Full-year budget and a forecast column if you have one.
- By the lines people manage. Not 400 nominal codes. Revenue by stream, cost of sales, gross margin, then overheads grouped the way the managers think about them. The nominal codes roll up into these.
- By the dimensions that have owners. Department, site, project, whichever your business runs on. A department head should be able to see their lines alone.
- Favourable and adverse marked consistently. A negative variance on a cost line is good news; on a revenue line it's bad. Colour and sign have to agree with that, every time, or people misread it.
- Materiality. A rule for what gets commentary: variances over £X or over Y percent. Everything else is noise.
- Commentary. Written by a person, next to the variances that pass the materiality rule. What happened, whether it's timing or permanent, what's being done.
How do you calculate the variance?
Because this is asked constantly:
- Variance = Actual minus Budget.
- Variance % = (Actual minus Budget) ÷ Budget × 100.
- For cost lines, a positive variance is adverse (you spent more). For revenue lines, a positive variance is favourable. Most finance teams flip the sign on cost lines so that "positive is good" everywhere, and label it favourable/adverse rather than plus/minus. Pick one convention, write it on the report, keep it.
- Percentage against a zero budget is undefined; show the absolute variance and leave the percent blank rather than printing a nonsense number.
Where does the budget live in each system?
This is the part that decides how hard the report is to automate. If the budget is in the system, the system's own report works and Power BI can read it. If the budget is in a spreadsheet, it has to be loaded somewhere first.
| System | Budget feature | Grain | Own budget vs actual report | The catch |
|---|---|---|---|---|
| Xero | Budget Manager | Account × month, by tracking category, multiple budgets | Budget Variance report; budget column on P&L | Import via CSV template; only P&L accounts, not balance sheet |
| QuickBooks Online | Budgets (Plus and Advanced plans) | Account × month, by class or location | Budget vs Actuals; Budget Overview | Not on Simple Start or Essentials |
| Sage 50 Accounts | Budgets on nominal records | Nominal × month, departmental budgets | Budget reports in the report library, budget columns on P&L | Entered per nominal; import needs the CSV route |
| Sage 200 | Nominal budgets, by cost centre and department, multiple budget versions | Nominal × period × cost centre/department | Budget vs actual in Financial Statements and Excel Integrated Reporting | Versions need managing; period budgets follow your period setup |
| Business Central | G/L Budgets, multiple named budgets, by dimension | G/L account × date × up to 4 dimensions | Financial Reports (formerly Account Schedules) with budget columns; Trial Balance/Budget | Powerful but Financial Reports need setting up; the Finance Power BI app includes budget vs actual |
Source: each vendor's product documentation, read 21 September 2026. Plan names and feature availability change; check your version.
The pattern: every system can hold a budget at account-by-month grain with at least one dimension. The reason the report is done in Excel is almost never capability. It's that the budget was built in Excel at a different grain (by manager, by quarter, by product) and nobody wants to re-key it.
Why does it end up as a spreadsheet every month?
Three reasons, in the order I see them:
The budget is in Excel and it's at a different grain. Built by department heads in a template, rolled up by finance, approved by the board, and never loaded into the accounting system because the accounts don't match the template. So each month someone exports actuals, pastes them into the template, and fights the mapping.
The actuals need reclassifying. The budget says "marketing"; the ledger has six nominal codes for it, and one of them is shared with sales. A mapping table exists in someone's head.
The commentary. Which is fine. That's a person's job and always will be.
The first two are modelling problems with modelling answers. The third isn't a problem.
What does a version that updates itself look like?
The shape, whichever tools you use:
- The budget lives in a table at the same grain as the actuals. Account (or reporting line), month, dimension, amount. If the business builds the budget in Excel, fine: the Excel template's output is that table, and it's loaded once a year (and once per reforecast). Power BI can read the Excel file directly if that's where it stays.
- A mapping table turns nominal codes into reporting lines. One row per nominal code: which reporting line it belongs to, which group, what order it prints in. This table is the single most valuable thing in the model and it's usually a hundred rows. Once it exists, the budget and the actuals both map onto the same lines and the report writes itself.
- A calendar table so month, year-to-date and full-year columns are one measure each rather than twelve.
- Measures for budget, actual, variance and variance percent, with the favourable/adverse sign handled once, in the measure, so every visual agrees.
- Row-level security so each department head sees their lines.
- A commentary table a person maintains (month, line, note), joined in so the notes sit next to the numbers.
- A reconciliation page: the model's actual total against the ledger's P&L total, difference shown, every refresh.
That last one isn't decoration. A budget vs actual report that doesn't visibly tie to the ledger gets argued with in the meeting, and the argument is the whole meeting.
If the ledger is the only source (budget in the ledger or in one Excel file), this is a single-source build and it's what the First Report is for: one system, one report, headline number reconciled to the penny, £1,950 fixed, ten working days, details at the First Report. The Power BI licence for each viewer is £10.80 per user per month on Microsoft's UK page as of September 2026.
Common questions
What is a budget vs actual report?
A report comparing planned figures with what actually happened, by line and by period, showing the variance and the variance as a percentage. It's the standard management report for controlling spend and tracking revenue against plan.
How do you calculate budget variance?
Actual minus budget gives the variance; divide by budget and multiply by 100 for the percentage. On cost lines a positive variance is adverse, on revenue lines it's favourable; most teams re-sign cost lines so that positive always means good, and label it favourable or adverse.
Can Xero do budget vs actual?
Yes. Load the budget through Budget Manager (account by month, by tracking category if you use them) and run the Budget Variance report or add a budget column to the P&L. Balance sheet budgets aren't supported.
Can QuickBooks Online do budget vs actual?
Yes, on the Plus and Advanced plans. Budgets are by account and month, optionally by class or location, and the Budget vs Actuals report compares them. The feature isn't on Simple Start or Essentials as of September 2026.
Does Business Central have a budget vs actual report?
Yes. G/L Budgets hold multiple named budgets by account, date and dimensions, and Financial Reports (the feature previously called Account Schedules) present budget alongside actual. Microsoft's free Finance Power BI app for Business Central also includes budget vs actual pages.
Why is our budget vs actual always in Excel?
Usually because the budget was built in Excel at a different grain from the ledger and never loaded, so actuals get pasted into the template every month. The fix is a mapping table from nominal codes to reporting lines and a budget table at the same grain, after which the report can produce itself.
Should the commentary be automated?
No. The numbers should update themselves; the meaning is a person's job. Give the commentary a home in the report (a small table of month, line, note) so it sits next to the variances, and let a person write it.