Excel track · Exercise 60 of 86 Paid

Budget versus actual with a variance flag

Size a variance as a share of budget, then flag it with one threshold both ways.

Advanced

Four budget lines with budget, actual and one answer cell

Read-only data · screen 1 of 2 · answer in D2

In D2, report the actual-vs-budget variance of the first line as a share of its budget, to three decimal places.

	A	B	C	D
1	Line	Budget	Actual	Answer
2	rent	2000	2100
3	payroll	5000	4800
4	tools	300	300
5	travel	800	950

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In D2, flag the first line review when its variance is more than ten per cent in either direction and ok otherwise.

	A	B	C	D
1	Line	Budget	Actual	Answer
2	rent	2000	2100
3	payroll	5000	4800
4	tools	300	300
5	travel	800	950

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

Dividing a variance by its budget turns an amount into a share of what was planned. That is what lets one threshold fit every line: 100 over on a 2000 budget is five per cent, and the same 100 over on 300 is a third. ROUND keeps the share at three decimals so two cells can be compared.

The rent line asks for that share directly: 2100 actual against a 2000 budget is 0.05. The second screen turns the same arithmetic into a flag, with the threshold at ten per cent in either direction.

Without ABS the test reads a signed difference, so an underspend never reaches review. Payroll at 4800 against a 5000 budget is four per cent under and would pass unnoticed. Travel at 950 against 800 is the line that does flag, at 18.75 per cent.

Syntax

=ROUND((actual-budget)/budget,3) sizes the variance; ABS lets one threshold catch both directions

Worked example

On Four budget lines with budget, actual and one answer cell, =ROUND((C2-B2)/B2,3) returns 0.05.

=ROUND((C2-B2)/B2,3)

Common errors

Error codes for a rounded variance share, then an IF over ABS so under- and overspends both flag
Error	What it means and how to fix it
`#DIV/0!`	The budget cell is zero, so the variance has nothing to be divided by.
`#VALUE!`	A budget or actual cell holds text, so the subtraction has no number to work with.
`Signed test`	A flag built on the signed difference catches overspends and lets an underspend through, which is what ABS around the difference prevents.

At work

Turning a budget column and an actual column into a variance a reader can compare across lines.