# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-budget-variance-analysis
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the break-even calculator](https://dothecalculation.com/calculators/break-even-calculator) — Find the revenue level a cost structure needs, which is what a variance report is measuring against.

## The formula

**Variance, signed so that positive always means good**

```
=IF(AccountType="Revenue", Actual−Budget, Budget−Actual)
```
- Revenue: coming in above budget is favourable, so actual minus budget.
- Cost: coming in below budget is favourable, so budget minus actual.
- Now a positive number is favourable everywhere, and one conditional format serves the whole report.

**Variance percentage, without the division error**

```
=IF(Budget=0, "", Variance/ABS(Budget))
```
- ABS on the denominator keeps the sign of the variance intact on negative budget lines.
- Returning a blank rather than #DIV/0! keeps the report readable when a line has no budget.
- A line with zero budget and a real actual is worth flagging separately, since it is unbudgeted spend.

## A quarter, reported properly

**Static budget against actual**
| 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.

**The flexed budget for a variable cost**

```
Flexed budget = Actual revenue × (Budget cost ÷ Budget revenue)
```
- 452,000 × (288,000 ÷ 480,000) = 452,000 × 60% = $271,200
- In Excel: =IF(Behaviour="Variable", ActualRevenue*BudgetCost/BudgetRevenue, BudgetCost)
- Add a Behaviour column with Fixed or Variable beside the account type column.

**Splitting the cost of goods variance**
| 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 |

> **What that split is worth** — Two thirds of the apparent cost saving is an accounting artefact of missing the revenue target. The real improvement is $9,040, a 3.3% efficiency gain against the flexed budget. Reporting $25,840 credits the purchasing team for a sales shortfall, and the following quarter it will be blamed for the reverse.

## 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.

**The review flag**

```
=IF(AND(ABS(Variance)>MaterialityDollars, ABS(VariancePct)>MaterialityPct), "Review", "")
```
- Both thresholds in named cells, so the report can be tightened without editing formulas.
- Typical starting point: $5,000 and 10% for a departmental report.
- Add a comment column and require a written explanation on every flagged line.

> **Conditional formatting that reads at a glance** — One rule on the variance column: green fill where positive and flagged, red fill where negative and flagged, no fill otherwise. Unflagged variances stay plain, so the eye goes to the four lines that matter rather than to the forty that do not.

## 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.

**Pulling one account for one period**

```
=SUMIFS(GL[Amount], GL[Account], $A2, GL[Period], B$1)
```
- The mixed references let one formula fill the whole grid of accounts by periods.
- SUMIFS returns 0 for an account with no transactions, which is correct and distinguishable from a lookup error.
- For year to date, use a period range: GL[Period], ">="&StartPeriod, GL[Period], "<="&EndPeriod.

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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-budget-variance-analysis). Quote freely with attribution and a link to this page._