Build a budget with variances

Build every planned figure from an assumptions block, split the plan into one-time and recurring cost with a check that the halves add back to the whole, and report a variance where there was no plan and where there has been no spend without inventing a figure for either.

Everything up to here has described something that already happened. A budget is the first thing in this course that describes something that has not, and that changes what "correct" means. Before actuals arrive, check whether the model expresses the intended assumptions. This exercise also supplies recorded actual spend, so variances compare those actuals with the plan.

The question a budget gets asked, always, is "what if?" What if twenty-four more learners enrol. What if the programme runs two weeks longer. What if the contingency goes from five percent to seven.

A budget built from formulas over an assumptions block answers all three in one edit each. A budget of typed numbers cannot answer any of them, and the honest response to being asked is that somebody will rebuild it, which is how a spreadsheet becomes a thing people are afraid to touch.

Every planned figure is a claim about what drives cost. Writing it as a formula makes the claim visible and testable; writing it as a number hides it.

Coaching at three pounds fifty per learner per week says: this cost scales with both enrolment and duration. The venue at a hundred and twenty a week says: this scales with duration and not with enrolment. Insurance says: this scales with neither. Those three claims are the actual content of the budget, and a column of typed numbers contains none of them.

The test is the one from Module 1, applied to a model rather than to data: change an assumption and watch what moves. If enrolment rises and the venue cost rises with it, the model is wrong about the venue. If enrolment rises and coaching does not move, coaching is a typed number.

One-time and recurring cost behave differently and need to be separable. The type label classifies the budget line; its formula states what drives its amount. Here coaching, venue and refreshments scale with weeks. Equipment hire and insurance are fixed assumptions. Contingency is classified one-time but still changes when the duration-sensitive costs it covers change. Keeping a type against each line lets you describe those categories separately. To price another month, change the weeks assumption and compare the full totals, including the resulting contingency change; the type split alone is not the incremental cost. Check that the two totals add back to the whole, then inspect every type label separately. Net agreement alone can miss an untyped zero-valued line or offsetting errors.

A percentage-of-something line has to read the something. A five-percent contingency written as a number stops being five percent the moment anything else changes. Written as a formula over the lines it covers, it stays five percent, and it is also why raising the enrolment moves the total by more than the two lines that obviously changed.

Variance is a subtraction, and two cases are not.

Write variance as actual minus planned, so an overspend is positive, and say so in the heading — the opposite convention is equally common and the sign of a number is not self-explanatory.

No plan. Something was spent that nobody budgeted for. The variance is the whole amount. The variance percentage is that amount divided by zero, which is not a large percentage; it is not a percentage at all. #DIV/0! is the correct output, and the absolute figure beside it is what to report.

No spend yet. A contingency that has not been drawn on mid-way through a programme is not an underspend. Reporting it as one says it was not needed, when what is true is that it has not been needed yet. Give the cell a third outcome in words, exactly as the decision rules in Module 3 did.

Both of those are the same discipline: do not let a formula turn an absence into a figure.

The assumptions, and three of the lines that depend on them:

Learners enrolled                    96
Weeks in the programme               10
Coaching, per learner per week     3.50
Venue, per week                     120
Contingency, share of C12:C16       5%

Coaching       =$B$2*$B$3*$B$4      3,360.00    scales with both
Venue          =$B$3*$B$5           1,200.00    changes with weeks or venue rate; not enrolment
Contingency    =SUM(C12:C16)*$B$9     314.30    scales with everything above it

Now raise the enrolment to 120 and watch. Coaching goes to 4,200.00 because it depends on enrolment. The venue stays at 1,200.00 because this model holds the weekly venue rate fixed when enrolment changes. And the contingency moves to 363.50 without anybody touching it, because it is a share of a subtotal that moved.

That third movement is the one worth noticing. The total rises by more than the lines that visibly changed, and it does so correctly. A contingency typed as 314.30 would have stayed at 314.30 and quietly stopped being five percent of anything.

The budget has a Transport line: nothing was planned, and 240.00 was spent. It has a Contingency line: 314.30 was planned, and nothing has been spent.

Predict what each of these returns.

=D18-C18        transport variance,   actual 240, planned 0
=E18/C18        transport variance share
=D17-C17        contingency variance, actual blank, planned 314.30
=E17/C17        contingency variance share

A budget line as most people first write it:

3360

Two things are wrong with it and only one is obvious. It cannot answer a what-if. And it does not say why it is 3,360, so nobody can tell whether it should have changed when the enrolment did.

=$B$2*$B$3*$B$4

Now the line states its own reasoning: learners, times weeks, times the rate. Anybody can read it and disagree with it, which is the point.

The contingency is the one that repays the most care:

=SUM(C12:C16)*$B$9

Not =6286*0.05, and not =314.30. Both of those are five percent of a subtotal that existed on the day they were typed.

And the variance cells that need a third branch:

E17  =IF(ISBLANK(D17),"not spent yet",D17-C17)
F17  =IF(ISBLANK(D17),"not spent yet",E17/C17)

Text in a numeric column is a real cost — a SUM will skip it — and here that is the right trade. A column that totals cleanly by pretending an undrawn contingency is an underspend has bought its tidiness with a false statement. If the total must be numeric, compute it from the two column totals rather than from the variance column, which is what the exercise does.

A finance officer asks why the budget total did not change when they raised the enrolment from 96 to 120. They did it themselves, in the assumptions block, and watched nothing happen.

1. Change one assumption at a time and note every cell that moves. Not whether the total moved — which cells. Ten seconds, and it identifies the problem class immediately.
2. Look at any line that should have moved and did not. Click it and read the formula, not the value. A typed number is a typed number however sensible it looks.
3. Look at any line that moved and should not have. This is the opposite error and it is rarer and worse: a venue cost that scales with enrolment produces a plausible number that is wrong in a direction nobody questions, because bigger programmes costing more is what everybody expects.
4. Check the derived lines specifically. A contingency, an overhead recovery, a percentage-for-management: these are the lines most often typed as numbers, because they were calculated once in a different tool.
5. Check the total's range. If a line was added at the bottom, =SUM(C12:C17) still covers six rows.

The general shape: a model is defined by what moves when you change an input, not by the numbers it currently shows. Two budgets showing identical totals can be completely different models, and the only way to tell them apart is to change something.

Rebuild the planned column from the assumptions, work out the variances against the actuals already recorded, and split the plan by type with a check that the halves add back.

Two rows need special interpretation. The notes beside them say what happened; what to write is yours.

Take a budget or a quote you have — for a project, a trip, a piece of work — and do the assumption audit on it.

1. List every number in it that is not derived from another number. Those are your assumptions, whether or not they are labelled as such.
2. For each, write down what it would take for that number to be wrong by a quarter. Some will be very hard to move; some will move if one person changes their mind.
3. Pick the assumption that is easiest to be wrong about, change it by a quarter on a copy, and note the new total.
4. Write one sentence: "If [assumption] turns out to be [value], the total becomes [figure]."

That sentence is the most useful output of most budgets, and it is almost never in them.

Sign in to track your progress on this exercise.

Move on when you can do these.

• Explain what a planned figure written as a formula says that the same figure typed as a number does not.
• Give the ten-second test that finds a typed number pretending to be a calculation, and say what the opposite error looks like.
• Say why a contingency must read the lines it covers, and what happens to it when it does not.
• Explain what a variance share should show when nothing was planned, and give the specific harm of showing zero instead.
• Say why an undrawn contingency is not an underspend, and what should be in that cell instead.
• Describe the check that the one-time and recurring halves add back to the whole, and say how to write it so it can actually fail.

Sign in to track your progress on this exercise.

A budget is finished when every planned figure states which assumptions drive it, changing one assumption moves exactly the lines it should, the split by type reconciles to the whole, and special cases are explicit: words for the undrawn contingency, and a visible undefined-percentage error beside the unbudgeted transport variance.

Next: Compare plans on total cost. One budget is a model of one plan. The next step is two plans quoted in different units, with fixed charges that make the cheaper one depend on how much you buy.