Free resource · Monte Carlo for finance
Modelling theunknowable.
Forecasting with confidence when the future refuses to sit still.
MELISSA WHIPP · NAKED FINANCE · FIRST PRESENTED WITH PLUM SOLUTIONS
First presented with Plum Solutions
Modelling Uncertainty in Excel, September 2026, hosted by Danielle Stein Fairhurst.
plumsolutions.com.au →The thesis
Forecasting is an argument about the future.
Most models refuse to describe uncertainty.
We can move beyond "by how much?" and "by when?" into the question that actually matters to a board: how confident are we, and across what range?
ONE NUMBER
£250m
One future. Nothing about how likely it is, or how far off it could be.
THREE NUMBERS
£100m · £250m · £300m
Worst, base, best. Better, but still three points with nothing in between and no sense of how likely each one is.
THE SHAPE
Every possible future
The full range of what could happen and how likely each outcome is. Your three scenarios were always three points inside this.
01 · Fragility
What makes a forecast fragile.
No anchor
With no reliable history, every assumption is a judgement call, and judgement calls compound.
False precision
A number to two decimal places looks certain. The confidence is in the formatting, not the forecast.
Hidden single points
One base-case number per line hides the range, so nobody sees how wrong it could quietly be.
Correlated assumptions
Inputs move together in real life. Flex them one at a time and you miss how they gang up.
02 · The mindset
Stop hiding the uncertainty. Start describing it.
The instinct is to pick the single "right" number and defend it. The honest move is the opposite.
Ranges, not points
Give every key assumption a low, likely and high, not one hopeful figure.
Show your working
Make the assumptions visible and challengeable, so the debate is about them, not the output.
Confidence, stated
"Most likely X, but realistically anywhere from Y to Z" beats false certainty.
03 · The familiar tools
Scenarios and sensitivities, and where they run out.
Scenarios, sensitivities and two-way data tables are the tools you already use to pressure-test a model, and they are worth doing well before reaching for anything cleverer. Even two inputs flexed together only give a grid, never a likelihood. A tornado chart ranks the biggest swings, but still flexes one driver at a time.
| -10% | -5% | 0% | +5% | +10% | |
|---|---|---|---|---|---|
| -10% | 32.4 | 34.2 | 36.0 | 37.8 | 39.6 |
| -5% | 34.2 | 36.1 | 38.0 | 39.9 | 41.8 |
| 0% | 36.0 | 38.0 | 40.0 | 42.0 | 44.0 |
| +5% | 37.8 | 39.9 | 42.0 | 44.1 | 46.2 |
| +10% | 39.6 | 41.8 | 44.0 | 46.2 | 48.4 |
THREE WALLS YOU HIT
Only a few futures
Three scenarios are three points in an infinite space of outcomes.
No likelihood
A grid shows what could happen, never how probable any of it is.
One thing at a time
Real uncertainty moves together. These tools cannot combine it all at once.
What if every input could vary at once, and we could see how likely each outcome really is?
04 · The method
Instead of one guess, run ten thousand.
Define the uncertainty
Give each input a range and a shape instead of one value, or sample it straight from your own history.
Run it thousands of times
Each run draws a random value from every range at once. Thousands of runs, thousands of possible futures.
Read the distribution
The results form a shape. Now you see the full range of outcomes, and how likely each one is.
A quick reminder
Not every uncertainty has the same shape.
A distribution is just the shape of what's likely. Choosing the shape is how we tell the model what kind of uncertainty we are dealing with. That is the judgement call; the rest is arithmetic. The workbook takes a fifth route: no shape at all. It draws each driver straight from 32 months of history with PERCENTILE and RAND, and lets the data supply the shape.
Normal
Symmetric bell. Most outcomes cluster around the middle and tail off evenly. A typical forecast: most likely near the estimate.
Lognormal
Leans one way with a long tail. Cannot go below zero, but can spike. Costs and durations that overrun far more than they undershoot.
Uniform
Flat. Every outcome equally likely across a range. Use when you truly have no view within the band.
Triangular
Minimum, most likely, maximum. The estimator's friend: quick to define from three simple guesses.
SKEW
Which way the tail points. Right-skewed costs mean the average understates your typical outcome, and the tail is where the nasty surprises live.
KURTOSIS
How heavy the tails are. Fat tails are where crises live; models that assume a neat bell routinely underestimate how often extremes occur.
SPREAD
The width of the uncertainty. Two forecasts can share an average and feel completely different: standard deviation is the workhorse measure of risk.
Live demonstration
See it move.
Describe three assumptions as ranges, run ten thousand futures and watch the distribution build. Then move a range and see the floor, the midpoint and the probability of hitting target shift with it. The workbook goes one step further and samples each driver from real order history; three ranges are enough to see the idea.
DESCRIBE THE UNCERTAINTY
CASH AT MONTH 12 · 0 RUNS
P10 · floor
—
P50 · midpoint
—
P90 · ceiling
—
P(cash ≥ £750k)
—
Illustrative SME: £600k opening cash, £200k/month revenue, £30k headcount, £15k overhead. Inputs are triangular (low / likely / high). Bars between P10 and P90 are highlighted; red bars are futures where cash goes negative.
05 · In practice
No add-ins. No black box. Just Excel and RAND.
You do not need specialist software. Everything here is built in plain Excel with functions you already have. The workbook takes 32 months of order history from a single-number forecast to a full distribution, one step at a time.
RAND()The random draw behind every cell. Press F9 and one complete, volatile year rearranges itself.
PERCENTILETurn a random number into a value drawn from your own history, or use NORM.INV for a shape you chose.
RANDARRAY()Excel 365 spills a thousand futures from a single formula. On older Excel, write the model out one row per future, every formula visible.
COUNTIFSBucket the results into a histogram and answer "what is the chance we hit plan?"
06 · Reading it
What the distribution tells a board.
P10 / P50 / P90
The range that matters. A realistic floor, the midpoint, and a realistic ceiling, not one hopeful line.
Probability of target
"What is the chance we actually hit plan?" Answerable, at last, as a number.
The shape of risk
Where the downside clusters, how fat the tails are, whether the mean flatters or misleads.
07 · Where it earns its keep
Anywhere the answer is really a range.
Revenue and demand
Pipeline, conversion, price and volume that never behave exactly as planned.
Project cost and runway
Overruns, timing and burn. How long does the money really last?
Capex and investment
Payback and return under uncertain assumptions, before you commit.
Scenario stress-testing
Not three neat scenarios, but the full spread between them.
08 · The honest bit
A thinking tool, not a crystal ball.
Garbage in, garbage out
The output is only as good as the ranges you feed it. Assumptions still do the heavy lifting.
Precision is not accuracy
Ten thousand runs of a wrong model gives a very confident wrong answer.
The distribution is a choice
Normal, triangular, uniform or your own history: the shape you pick shapes the result. Sampling history assumes the next twelve months look like the last thirty-two.
The shift
Stop asking "what's the number?" Start asking "how confident are we, and across what range?"
If you are modelling something with no neat history to lean on, get in touch. This is the kind of problem Naked Finance was built for.
Back to Training and resources
First presented with Plum Solutions
Modelling Uncertainty in Excel, September 2026, hosted by Danielle Stein Fairhurst.
plumsolutions.com.au →