01 / 10The Pack Said £21,950 Ahead

Variance AnalysisReportingConditional Formatting

Budget vs Actual Variance in Excel: Favourable or Adverse

The October Pack Said the Clinics Were £21,950 Ahead of Budget and They Were £15,750 Behind, Because Actual Minus Budget Calls a Cost Overrun Good News

1 Nov 20267 min read

UsesIFSIGNABSSUMIFS

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 2026Figure
Net variance in the pack+21,950
Real profit variance−15,750
Locum overspend shown in green18,400
Lines in the wrong colour6 of 10
Budget profit87,700
Actual profit71,950

Scope. IF, ABS, SIGN, SUMIFS and SUMPRODUCT work 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.

ABCDEFG
1
Line
Type
Budget
Actual
Actual − Budget
Signed variance
Reported %
2
Consultations
Revenue
142000
148600
6600
6600
4.6%
3
Surgery
Revenue
96000
89200
-6800
-6800
-7.1%
4
Pharmacy sales
Revenue
54000
57300
3300
3300
6.1%
5
Vet salaries
Cost
118000
116500
-1500
1500
-1.3%
6
Locum cover
Cost
12000
30400
18400
-18400
153.3%
7
Drugs & consumables
Cost
41000
44900
3900
-3900
9.5%
8
Rent & rates
Cost
22500
22500
0
0
0.0%
9
Utilities
Cost
6800
6100
-700
700
-10.3%
10
Marketing
Cost
4000
0
-4000
4000
-100.0%
11
Equipment repairs
Cost
0
2750
2750
-2750
#DIV/0!
12
Net variance
21950
-15750

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.

LineActual − BudgetEffect on profit
Consultations (revenue)+6,600Helps
Locum cover (cost)+18,400Hurts
Vet salaries (cost)−1,500Helps
Surgery (revenue)−6,800Hurts

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.

Try it in the grid

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.

LineActual − BudgetSigned 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.

Try it in the grid

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

Try it in the grid

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.

Try it in the grid

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

LineSigned varianceBudget% with ABS
Locum cover−18,40012,000−153.3%
Marketing+4,0004,000+100.0%
Equipment repairs−2,7500n/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%.

Try it in the grid

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.

Try it in the grid

08Six Traps

  1. One subtraction for every line. =D2-C2 scores revenue correctly and costs backwards.
  2. Colouring the raw column. A green cost overrun is the most convincing wrong number on a page.
  3. Totalling raw variances. The sum adds gains to losses and matches no profit figure.
  4. Dividing by a zero budget. Unbudgeted lines return #DIV/0! and hide their overspend.
  5. Dividing by a negative budget. A budgeted loss that shrinks reports a negative percentage.
  6. Typed line types. "cost " with a trailing space fails B2="Cost" and gets scored as revenue.

09Four Exercises

  1. Reproduce it. Build the raw column and confirm the total reads +21,950.
  2. Sign it. Add the IF column and check that F12 reads −15,750.
  3. Prove it. Reconcile F12 to actual profit minus budget profit with SUMIFS.
  4. Audit it. Count the flipped lines with SIGN, then add the percentage column with ABS.

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.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge