Variance analysis in Excel: budget vs actual, explained
Variance analysis is the heart of every management report: how did actuals compare to budget, and why. The maths is simple; the slow part is writing the commentary that explains each swing. Here’s how to build it in Excel — and how to auto-generate the explanation.
The variance formulas you need
Three columns turn a budget-vs-actual table into an analysis.
Variance analysis compares what happened (actual) to what you planned (budget or forecast). The base calculation is one subtraction: Variance = Actual − Budget. Add % Variance = (Actual − Budget) / Budget to size it, and a favourable/adverse flag so the sign is read correctly — for revenue, actual above budget is favourable; for costs, actual above budget is adverse.
That’s the whole mechanic. In practice you lay it out as a table — line item, budget, actual, variance, % variance, F/A — and repeat it for each period. Conditional formatting on the variance column (green favourable, red adverse) makes the outliers jump out.
The part that eats an afternoon isn’t the formulas — it’s explaining them. A number tells you marketing was 18% over budget; it doesn’t tell you it was driven by a paid-social overspend in the last week of the quarter. That narrative is what management actually reads, and it’s the part worth automating.
A variance analysis table, laid out
The columns that turn raw actuals into a report.
| Column | Formula / content | Purpose |
|---|---|---|
| Line item | (label) | Revenue / cost line |
| Budget | (planned figure) | The plan |
| Actual | (reported figure) | What happened |
| Variance | =Actual − Budget | Absolute swing |
| % Variance | =(Actual−Budget)/Budget | Relative size |
| F/A | =IF(sign favourable,"F","A") | Read the sign correctly |
| Commentary | (written) | Why it moved — the bit people read |
Favourable vs adverse: getting the sign right
The single most common variance mistake.
The classic error is treating every positive variance as good. For a revenue line, actual above budget is favourable. For a cost line, actual above budget is adverse — you spent more than planned. If your report doesn’t distinguish the two, a ‘+£40k’ on costs looks like a win when it’s the opposite.
Build the F/A logic once, keyed off whether the line is revenue or cost, so the flag and the colour are always right. This matters most when a board glances at the report for ten seconds — the colour has to mean the same thing everywhere.
Automating the commentary
The report writes itself from the numbers.
This is where DataHub Pro changes the job. Upload your budget-vs-actual file and it computes every variance and % variance, applies the correct favourable/adverse logic, and then writes the commentary — ‘Marketing was £42k (18%) over budget, adverse, concentrated in paid social’ — grounded in the actual rows, not invented. Every figure it cites is auditable back to your data.
The output is a branded Word or PowerPoint variance report you can send as-is. Because the analysis and the narrative come from the same upload, refreshing next month is another upload, not another afternoon. That turns the monthly variance pack from a manual build into a one-click deliverable.
Frequently asked questions
How do you calculate variance in Excel?
Variance = Actual − Budget. Add percentage variance = (Actual − Budget) / Budget to size it, and a favourable/adverse flag so the sign is read correctly. Lay these out as columns next to each budget line.
What is favourable vs adverse variance?
Favourable means the result is better than plan; adverse means worse. For revenue, actual above budget is favourable; for costs, actual above budget is adverse. The same number can be favourable or adverse depending on whether the line is income or expense.
How do I write variance commentary quickly?
Commentary explains why each material variance happened. Instead of writing it by hand, upload your budget-vs-actual file to DataHub Pro, which computes the variances and drafts the explanation from the underlying rows, then exports a branded report.
What percentage variance is significant?
Most teams set a materiality threshold — often 5–10% or an absolute amount — and only comment on variances above it. Flag those automatically so you focus the narrative on what moved the numbers.
Can I automate budget vs actual reporting?
Yes. A tool like DataHub Pro reads the budget-vs-actual file, calculates all variances, applies favourable/adverse logic, and generates the written report, so each month is a re-upload rather than a rebuild.
What’s the difference between variance analysis and forecasting?
Variance analysis is backward-looking — it explains actual vs plan. Forecasting is forward-looking — it projects future periods. Good FP&A does both: explain last period, then reforecast the next.
Explore related guides
More finance and reporting guides.
Turn budget vs actual into a written report
Upload your budget-vs-actual file and get every variance, the favourable/adverse logic, and the commentary explaining why — as a branded report. Free tier, then $14.99/mo.
Try it free on your file →