Larkfield Vets' October pack said the month finished £21,950 ahead of budget, in green. It finished £15,750 behind. One formula, Actual minus Budget, had scored every cost overrun as good news.
| October 2026 | Figure |
|---|---|
| Net variance in the pack | +21,950 |
| Real profit variance | −15,750 |
| Locum overspend shown in green | 18,400 |
| Lines in the wrong colour | 6 of 10 |
| Budget profit | 87,700 |
| Actual profit | 71,950 |
Scope.
IF,ABS,SIGN,SUMIFSandSUMPRODUCTwork the same in every version of Excel on Windows, Mac and the web.Throughout, the sheet is in A1:G12: line in A, Revenue or Cost in B, budget in C, actual in D. Rows 2 to 11 are the ten lines and row 12 is the total.
01The Pack Said £21,950 Ahead
The practice manager built the variance column once and filled it down. It subtracted budget from actual on every line, and a conditional format coloured positives green and negatives red.
=D2-C2
The total at the bottom, =SUM(E2:E11), read +21,950 in green. Nobody asked how a month with a surgery shortfall could beat its budget by that much.
Ten Lines, One Subtraction, Six Wrong Colours
Larkfield Vets' October budget-versus-actual sheet, in A1:G12. Column B says whether each line is Revenue or Cost, C holds the budget and D the actual. Column E is what the pack reported, Actual minus Budget, and G is that figure as a percentage of budget. Column F is the variance signed by line type, positive when the line helped profit. Row 12 totals E and F.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
The partners read the green total and kept the locum agency booked through November. The locum line, £18,400 over budget, was the greenest figure on the page.
02One Subtraction Cannot Score Both Kinds of Line
Actual minus budget has no opinion. Whether a positive figure is good depends on what the line is. More revenue than planned helps profit; more cost than planned hurts it.
| Line | Actual − Budget | Effect on profit |
|---|---|---|
| Consultations (revenue) | +6,600 | Helps |
| Locum cover (cost) | +18,400 | Hurts |
| Vet salaries (cost) | −1,500 | Helps |
| Surgery (revenue) | −6,800 | Hurts |
The three revenue lines came out right by luck: for revenue, actual minus budget happens to point the right way. Every cost line that moved came out backwards.
Scenario: Put the ten lines in A2:D11 and enter =D2-C2 in E2, filled down. Read E6 for Locum cover and E5 for Vet salaries, and decide which of the two the partners should have worried about.
03Sign Each Variance by Line Type
Give every variance one meaning: positive helps profit, negative hurts it. Revenue is actual minus budget. Cost is budget minus actual. One IF reads the type in column B and picks the order:
=IF(B2="Cost",C2-D2,D2-C2)
The same thing written as a sign multiplier is =(D2-C2)*IF(B2="Cost",-1,1). Either way, the locum line now reads −18,400 and Vet salaries +1,500.
| Line | Actual − Budget | Signed variance |
|---|---|---|
| Vet salaries | −1,500 | +1,500 |
| Locum cover | +18,400 | −18,400 |
| Drugs & consumables | +3,900 | −3,900 |
| Marketing | −4,000 | +4,000 |
The convention has names in management accounting: favourable for a positive signed variance and adverse for a negative one. Put the words in the column header so nobody has to guess.
Scenario: Enter =IF(B2="Cost",C2-D2,D2-C2) in F2 and fill it down to F11. Check that F6 reads −18,400 and F5 reads 1,500, then type "Revenue" over "Cost" in B5 and watch F5 change sign.
04Signed Variances Add Up to the Profit Variance
The real test of a variance column is its total. Signed variances add up to actual profit minus budget profit. Raw ones add revenue gains to cost overruns and produce a number that means nothing.
=SUM(F2:F11) → −15,750
=SUMIFS(D2:D11,B2:B11,"Revenue")
-SUMIFS(D2:D11,B2:B11,"Cost") → 71,950
=SUMIFS(C2:C11,B2:B11,"Revenue")
-SUMIFS(C2:C11,B2:B11,"Cost") → 87,700
| Check | Figure |
|---|---|
| Revenue variance | +3,100 |
| Cost variance | −18,850 |
| Signed total | −15,750 |
| Actual profit − budget profit | −15,750 |
| Raw total in the pack | +21,950 |
The pack's +21,950 is revenue beating budget by 3,100 plus costs overrunning by 18,850. It adds a gain to a loss as if both were gains.
Scenario: Enter =SUM(F2:F11) in F12 and the two SUMIFS profit formulas in spare cells. Subtract budget profit from actual profit and confirm it matches F12 to the pound.
05Count the Lines Wearing the Wrong Colour
Before rebuilding a pack, find out how much of it was wrong. SIGN returns 1, −1 or 0, so comparing the sign of the raw column with the signed one finds every line whose colour flipped.
=SUMPRODUCT(--(SIGN(E2:E11)<>SIGN(F2:F11)))
On October's sheet it returns 6. Every cost line that moved is in the count; Rent & rates hit budget exactly and is not. The three revenue lines agree, which is why the pack looked believable.
Scenario: Enter =SUMPRODUCT(--(SIGN(E2:E11)<>SIGN(F2:F11))) beside the sheet and check it returns 6. Then change Rent & rates' actual to 23,000 and see the count go to 7.
06Percentage Variances Need ABS and a Zero Check
A percentage variance divides the signed variance by the budget. Two lines break the plain division: one with no budget and one with a negative budget.
=IF(C2=0,"n/a",F2/ABS(C2))
Equipment repairs had a budget of zero, so any division returns #DIV/0!. The IF writes "n/a" instead, and the 2,750 still shows in the variance column where it belongs.
ABS matters for a line budgeted as a loss. Budget a disposal loss of −1,500, make a loss of −600, and the variance is +900 favourable. Divided by −1,500 it reads −60%; divided by ABS of the budget it reads +60%.
| Line | Signed variance | Budget | % with ABS |
|---|---|---|---|
| Locum cover | −18,400 | 12,000 | −153.3% |
| Marketing | +4,000 | 4,000 | +100.0% |
| Equipment repairs | −2,750 | 0 | n/a |
Scenario: Enter =IF(C2=0,"n/a",F2/ABS(C2)) in H2, fill it down and format it as a percentage. Check that H11 reads n/a and H6 reads −153.3%.
07Colour the Signed Column, Not the Raw One
Once every variance means the same thing, one conditional formatting rule fits every line. Select E2:H11 and add a formula rule that looks only at the signed column:
=$F2<0
Format it red, and add a second rule =$F2>0 in green. The $ before F keeps every cell in the row looking at its own signed variance.
A text label helps anyone reading a printout in black and white:
=IF(F2>0,"Fav",IF(F2<0,"Adv","On budget"))
Scenario: Delete the old rule on column E, then add =$F2<0 as a red rule over E2:H11. Check that the Locum cover row turns red and the Vet salaries row does not.
08Six Traps
- One subtraction for every line.
=D2-C2scores revenue correctly and costs backwards. - Colouring the raw column. A green cost overrun is the most convincing wrong number on a page.
- Totalling raw variances. The sum adds gains to losses and matches no profit figure.
- Dividing by a zero budget. Unbudgeted lines return
#DIV/0!and hide their overspend. - Dividing by a negative budget. A budgeted loss that shrinks reports a negative percentage.
- Typed line types. "cost " with a trailing space fails
B2="Cost"and gets scored as revenue.
09Four Exercises
- Reproduce it. Build the raw column and confirm the total reads +21,950.
- Sign it. Add the
IFcolumn and check that F12 reads −15,750. - Prove it. Reconcile F12 to actual profit minus budget profit with
SUMIFS. - Audit it. Count the flipped lines with
SIGN, then add the percentage column withABS.
What to Take Away
Every number in Larkfield's sheet was correct. The budget, the actuals and the subtraction were all right. The sign meant different things on different lines, and the colour trusted it.
Decide once that positive means good for profit, build that into the variance with the line type, and prove the column by checking its total against the profit line.