Scenarios and sensitivity

Drive one cost model from a scenario switch, build a two-way sensitivity block and identify which input moves the result further, and report a modelled figure with its range at a precision its inputs support.

A model produces a number to the penny from inputs that are guesses. The number is not more certain than the guesses, and it does not look less certain, which is the problem.

"The summer programme will cost £4,560.00" is a sentence a spreadsheet will happily produce from an enrolment nobody has confirmed and a coaching rate nobody has negotiated. The illustrated low and high scenarios produce about £2,800 and £7,100 — a spread nearly as large as the £4,560 base figure. These selected outcomes do not establish where the actual cost will fall. Reporting the pennies is not a small overstatement; it is a claim of precision the model cannot support, and people commit to budgets on the strength of it.

The remedy is not to refuse to give a number. It is to give the number with its range, to know which input the range mostly comes from, and to round to where the model is actually informative.

One model, one switch. Three scenarios kept as three copies of a sheet will diverge, because somebody will fix a formula in one of them. Keep one model and a small table of inputs — one column per scenario — and have the model's active assumptions fetch from that table according to a switch cell.

=INDEX(B5:D5, MATCH($B$2, $B$4:$D$4, 0))

MATCH finds which column the switch names; INDEX reads that position out of the row. A chain of IFs does the same job and is easier to read for three scenarios; INDEX and MATCH do not get longer as scenarios are added.

The test that it works is one edit: change the switch, and every active assumption and every derived figure moves together. If an active value fails to update, inspect its formula and references; it may be typed or may point at the wrong cell.

A scenario is a coherent set of assumptions, not a single dial. This exercise chooses fewer learners, fewer weeks and a lower coaching rate for its low scenario. That is an illustrative bundle, not evidence that those inputs generally move together. That is what makes it a scenario rather than a sensitivity.

Sensitivity holds the other inputs fixed while varying selected inputs. This two-way block varies two inputs across a grid, so you can see the shape of the answer rather than three points on it. It is the mixed-reference exercise from Module 1 in its natural home: one axis anchored to a column, the other to a row, everything else anchored on both.

=$A22*$B$11*B$21 + $B$11*$B$14

The learner count comes from column A, the rate from row 21, and the number of weeks from the active scenario, anchored so it does not move.

With the switch set to base, put its enrolment and rate in the middle of the block and compare the centre with the model above. Both use ten weeks then. After switching to low or high, the grid keeps its enrolment and rate axes but uses the newly active weeks, so its centre need not equal the active model.

Which input matters is a measurement, not an opinion. Take the spread along each axis: the range of results across the enrolment axis at the middle rate, and across the rate axis at the middle enrolment. For the selected input ranges at ten weeks, enrolment moves the total by 1,680 and the coaching rate by 672. That identifies the larger effect in this comparison; deciding which uncertainty to reduce also depends on how credible and controllable those ranges are.

Then report honestly. Three things belong together:

• A rounded headline. If the spread is thousands, the pennies are noise. ROUND(total, -2) to the nearest hundred says what the model actually knows.
• The scenario bounds. State the selected low and high outcomes as a range and name the assumptions behind them. They are not a probability interval or a guarantee on actual cost.
• What the range does not cover. Three scenarios are three points, not a distribution, and nothing here says how likely any of them is. A model that has not been given a probability cannot produce one.

The temptation to resist is the one the spreadsheet creates: it will give you 4,560.00 whether or not that is meaningful, and 4,560.00 reads as a more careful answer than "about 4,600, and the selected scenarios run from about 2,800 to 7,100". The second one is the careful answer.

The scenario table, and the switch that drives the model:

                     low    base    high
Enrolment             72      96     120
Weeks                  8      10      12
Coaching rate       3.20    3.50    3.90

Active scenario:  base

Enrolment       =INDEX(B5:D5,MATCH($B$2,$B$4:$D$4,0))     96
Weeks           =INDEX(B6:D6,MATCH($B$2,$B$4:$D$4,0))     10
Coaching rate   =INDEX(B7:D7,MATCH($B$2,$B$4:$D$4,0))   3.50

Coaching   =$B$10*$B$11*$B$12                        3,360.00
Venue      =$B$11*$B$14                              1,200.00
Total                                                4,560.00

Change the switch to high and the three active assumptions and the three derived cost figures shown here change in one edit; the switch itself changes too. That is the whole benefit, and it is worth the ten minutes it costs to set up the first time.

The three totals are 2,803.20 low, 4,560.00 base, 7,056.00 high. Notice how far apart they are. The base case is not in the middle of that range — it is nearer the bottom — because this example’s high scenario combines three larger input values.

The sensitivity block varies enrolment down the side and coaching rate across the top, at the base scenario's ten weeks. Venue is 120 a week.

Predict these three.

the centre cell:   96 learners at 3.50
the top-left:      72 learners at 3.20
the spread across the enrolment axis, at 3.50

A model with three scenarios usually starts as three sheets. Combining them is one change of habit:

B10   96

becomes

B10   =INDEX(B5:D5,MATCH($B$2,$B$4:$D$4,0))

and the number 96 moves into the scenario table where it belongs, beside its low and high siblings.

For the sensitivity block, the anchoring is the whole trick:

=$A22*$B$11*B$21+$B$11*$B$14

$A22 — the enrolment, pinned to column A, free to move down. B$21 — the rate, pinned to row 21, free to move across. $B$11 and $B$14 — the weeks and the venue rate, pinned on both axes, because the block is priced at one number of weeks throughout.

Get the last one wrong and the block still fills with plausible numbers. Compare the centre cell with the model only while the switch is on base; for other scenarios, check the grid formula and the active weeks separately.

Finally the reporting cells, which are the ones nobody thinks to build:

=B5*B6*B7+B6*$B$14      low scenario total,   2,803.20
=D5*D6*D7+D6*$B$14      high scenario total,  7,056.00
=B33-B32                spread,               4,252.80
=ROUND(B19,-2)          rounded headline,        4,600

ROUND(x, -2) rounds to the nearest hundred. The negative second argument is worth knowing: -1 is the nearest ten, -3 the nearest thousand. Choosing which one to use is a judgement about how much the model knows, and it is a judgement worth making explicitly rather than by leaving the default.

A committee is shown a model with low, base and high scenarios. The high case is cheaper than the base case. Nobody can see why, and the meeting stalls.

1. Inspect each input and formula. Read the scenario table column by column and check what the labels mean. A lower high-scenario total can reflect an intended offset, a mislabeled assumption or a formula error; the total alone does not identify one reversed row.
2. Ask what "high" means for each row. Document the scenario’s purpose and how each input contributes. A coherent bundle may mix increases and decreases, such as higher enrolment with a negotiated lower rate; label those choices so readers can judge them.
3. Change one row at a time. Set the low and base to the same value for every row but one, and check the total moves the way you expect. Repeat per row. Five minutes, and it finds the row.
4. Check the switch reaches every assumption. If an active assumption is typed or references the wrong scenario cell, the high result may mix inputs; inspect each fetch formula.
5. Check the block's fixed inputs. A sensitivity block anchored to the wrong cell can produce plausible errors. With the switch set to base, compare its centre with the active model; otherwise inspect the grid references and active weeks directly.

The general shape: a scenario needs an explicit story for its assumptions and a verified formula path to its total. A surprising total calls for checking both.

Drive the model from the switch, build the sensitivity block, measure which input matters, and decide how to report the result.

The last cell is a judgement rather than a calculation. The two above it give you what you need to make it.

Take any estimate you have made recently — a project's duration, a cost, an amount of something needed — and give it a range.

1. Write the estimate down as a single number, as you originally made it.
2. Identify the two inputs it depends on most.
3. For each, write a low and a high that you would not be surprised by. Not a worst case; a value you would not find remarkable.
4. Compute the estimate at low-low and at high-high.
5. Round the original estimate to the precision that range supports, and write the sentence: "about X, and plausibly between Y and Z".

Step 5 is the deliverable. Most estimates are given as a single number because that is what was asked for, and most of the value is in the range that was not.

Sign in to track your progress on this exercise.

This module is finished when you can do all of these.

• Explain why three scenarios in one model beats three copies of a model, and describe the one-edit test that it works.
• Say what makes a scenario different from a sensitivity.
• Describe the anchoring in a two-way sensitivity block, and say which references are pinned on both axes.
• With the switch on base, say what the centre of this sensitivity block should equal; explain why the comparison changes under another switch setting.
• Explain how to measure which of two inputs matters more, and what you would do with the answer.
• Given a base figure of 4,560.00 and a range of 2,803 to 7,056, say how you would report the result and why.
• Say what a three-scenario range does not tell you.

Sign in to track your progress on this exercise.

A model is honest when one switch drives it, when a two-way block shows the shape of the answer rather than a point on it, when the larger effect among the tested input ranges has been measured, and when the headline figure is rounded to what the inputs actually support and quoted with its range.

That completes the fourth module. You can now build a cost model whose assumptions are visible, compare alternatives that are not quoted in the same terms, and say how far the answer could move.

Module 5 is about the last step, and it is the one where the most damage is done: putting the result in front of somebody. Summaries that flatter, charts that mislead without lying, and dashboards confident about data that only covers half the programme.