Budget vs Actual in Excel: The Sign Convention Everyone Gets Wrong
Actual minus budget is favourable on a cost line and unfavourable on a revenue line, so one subtraction cannot serve both. Here is the account-type formula that fixes it, plus the flexed budget that separates a real cost saving from selling less.
One Subtraction Cannot Serve Both
Spending $35,500 against a $42,000 marketing budget is good news. Booking $452,000 of revenue against a $480,000 budget is bad news. Both are the actual coming in below the budget, and a single formula of actual minus budget labels them identically.
Variance reports that get this wrong produce red cells on lines that are performing well, and executives stop reading them within two months. The fix is an account type column and one IF.
Try the break-even calculatorFind the revenue level a cost structure needs, which is what a variance report is measuring against.The formula
A quarter, reported properly
Swipe sideways to compare columns.
| Line | Budget | Actual | Variance | Var % | F/U |
|---|---|---|---|---|---|
| Revenue | $480,000 | $452,000 | −$28,000 | −5.8% | U |
| Cost of goods sold | $288,000 | $262,160 | $25,840 | 9.0% | F |
| Gross profit | $192,000 | $189,840 | −$2,160 | −1.1% | U |
| Salaries | $96,000 | $99,400 | −$3,400 | −3.5% | U |
| Marketing | $42,000 | $35,500 | $6,500 | 15.5% | F |
| Rent | $24,000 | $24,000 | $0 | 0.0% | — |
| Operating income | $30,000 | $30,940 | $940 | 3.1% | F |
Read as it stands, this looks like a quarter where revenue missed and cost control saved it. The $25,840 favourable variance on cost of goods appears to be the story. It is not.
The flexed budget, which changes the conclusion
Cost of goods sold varies with revenue. Selling less automatically costs less, and a static budget credits that to the purchasing team. To separate the two effects, restate the budget at the volume actually achieved.
Swipe sideways to compare columns.
| Component | Working | Amount | Meaning |
|---|---|---|---|
| Static variance | 288,000 − 262,160 | $25,840 F | What the naive report shows |
| Volume variance | 288,000 − 271,200 | $16,800 F | You simply sold less |
| Efficiency variance | 271,200 − 262,160 | $9,040 F | Genuine cost improvement |
Flagging only what needs attention
A forty-line report where every line has a variance is noise. Flag on both a currency threshold and a percentage threshold, so a $50 variance on a $200 budget and a 0.4% variance on a $2 million line both stay quiet.
Pulling the actuals in
Do not paste actuals over a formula column. Keep the general ledger export on its own sheet as a flat table and pull from it, so refreshing the report is a paste into one place.
Use a structured table for the ledger, created with Ctrl+T. New rows are then included automatically, which removes the most common cause of a variance report that was correct last month and is wrong this month.
The errors that make a variance report untrusted
- One sign convention for the whole report. Costs and revenue need opposite treatment, and mixing them is the fastest way to lose your audience.
- Comparing actual against a static budget on variable costs, without flexing. It credits and blames the wrong teams every quarter.
- Variance percentages on near-zero budgets. A $400 variance on a $200 budget is 200%, which is arithmetically true and analytically useless. Suppress the percentage below a threshold.
- Hard-coded ranges that do not grow with the ledger. Use structured table references.
- Comparing year-to-date actual against full-year budget. Compare like with like, or divide the annual budget by the periods elapsed and say that is what you did.
- Mixing accrual and cash. An unposted invoice makes a cost line look favourable this month and unfavourable next, and it is a timing artefact rather than performance.
What the report does not tell you
- Why the variance happened. The number identifies where to look; the explanation always comes from someone who was there.
- Whether the budget was any good. A large favourable variance often means the budget was padded, not that performance was strong.
- Anything about cash. A favourable expense variance caused by delaying supplier payment improves the report and worsens the relationship.
- Timing versus permanent differences. A marketing campaign moved from March to April creates equal and opposite variances that mean nothing in isolation.
- The trend. A single period is a snapshot. Three consecutive periods of the same-signed variance on one line is a signal; one period is usually noise.
- Mix effects. Selling the same revenue with a different product mix changes the cost of goods percentage, and a two-way variance split will not separate that from an efficiency change.
Should variance be actual minus budget, or budget minus actual?
It depends on the account type, which is why the formula needs an IF. Revenue uses actual minus budget; costs use budget minus actual. Signed that way, a positive number is favourable on every line and one conditional format works for the whole report.
What is a flexed budget?
The budget restated at the activity level actually achieved. It lets you separate the part of a cost variance caused by doing more or less business from the part caused by doing it more or less efficiently. Without it, variable cost variances are mostly a volume story.
How do I avoid #DIV/0! in the variance percentage?
Use =IF(Budget=0, "", Variance/ABS(Budget)) rather than IFERROR. IFERROR would also swallow a genuine error elsewhere in the formula, whereas the explicit test only handles the case you meant to handle.
What threshold should trigger a variance explanation?
Both a currency and a percentage test, so small lines and large lines are treated sensibly. A common starting point is $5,000 and 10%, tightened for a small department. Whatever you pick, put it in a named cell so it can be adjusted without touching formulas.
Should I compare year to date or just the current period?
Both, in adjacent column groups. The period tells you what just happened; the year to date tells you whether it is a trend or a blip. Reporting only one of the two is how a timing difference gets escalated as a performance problem.
How do I keep the report from breaking when new accounts appear?
Drive the row list from the ledger rather than typing it. A UNIQUE formula over the account column, or a PivotTable refreshed from the ledger table, means a new account appears automatically rather than being silently omitted from the totals.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team