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

By Ali, Founder, Excel Lessons

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

Actual minus budget makes overspending positive.

In `D2`, type:

=C2-B2

This fills down to D7.

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

Actual minus budget makes overspending positive. In D2, type: =C2-B2. This fills down to D7.

Hint. Actual first.
2

In E2, divide the variance by the budget, guarded by IF. Shape: =IF(test, "text", value). Remember: text goes in quotes, numbers do not.

Hint. Test the budget first.
3

Over means more than 10% over budget. Under means more than 10% under budget. In F3, flag the wages line with IF. Shape: =IF(test, "text", IF(test, "text", "text")). Otherwise it is ok.

Hint. Nested IF, three outcomes.
4

In B9, total B2:B7 with SUM. C9 takes the same formula one column across.

Hint. B2 to B7.
5

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

Hint. Just the lines over budget.
6

In B11, count the lines flagged Over with COUNTIF.

Hint. The word Over goes in quotes.