Project: Budget Against Actual

About this lesson

A budget-against-actual sheet is actual minus budget for the variance, and a guarded division for the percentage. A nested IF gives the flag. SUMIF and COUNTIF over those columns give the summary.

By the end you can

build a variance sheet with a guarded percentage and a flag.
total only the overspends.

Practises IF SUM SUMIF COUNTIF

The idea

The sheet every department gets once a month. Actual minus budget, so overspending is positive. The percentage is guarded against a zero budget. A flag with a tolerance. And a summary that adds only the overspends. A manager asking "how much are we over" does not want underspends taken off.

The flag's tolerance is typed into the formula here to keep the step short. On a real sheet it belongs in one locked cell. Then changing 10% to 5% is one edit and not six.

The mistake to watch for

Taking the underspends off. A manager asking how much the department is over wants the positive variances added on their own. That is SUMIF with a greater-than-zero condition. And the tolerance in the flag belongs in one locked cell, not typed into six formulas. Then changing it is one edit.

Where this comes up again

Putting It Together Conditional Math
What a Pivot Table Does Pivot Tables
The SUM Function Spreadsheet Basics
The Six Comparisons Logic

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In D2, the variance on rent: actual minus budget, so that overspending is positive. This fills down.

To begin, type it exactly:

=C2-B2

Row	A	B	C	D	E	F
1	Line	Budget	Actual	Variance	Var %	Flag
2	Rent	1200	1200
3	Wages	4800	5150
4	Stock	2200	1980
5	Marketing	600	910
6	Utilities	350	372
7	Travel	0	140
8
9	Total
10	Overspend
11	Lines over

Every step

1

In D2, the variance on rent: actual minus budget, so that overspending is positive. This fills down. To begin, type it exactly: =C2-B2.

Hint. Actual first.
2

In E2, the variance as a fraction of the budget, guarded. If the budget is 0, show the text n/a instead of dividing.

Hint. Test the budget first.
3

In F3, flag the wages line. Over if the variance is more than 10% of budget. Under if it is more than 10% below. And ok otherwise.

Hint. Nested IF, three outcomes.
4

In B9, the total budget with SUM. C9 will take the same formula one column across.

Hint. B2 to B7.
5

Read =SUMIF(D2:D7,">0") and say what it returns: the total overspend, counting only the lines that went over.

Hint. Positive variances only.
6

In B11, write a formula using COUNTIF that counts the lines flagged Over.

Hint. "Over".