Monte Carlo Simulation

Should Harbor Coffee open a thirteenth cafe? The spreadsheet says that it'll make ninety thousand dollars in its first year. But every number in there is a guess. Visitors could be higher or lower. Costs could overrun. A single number gives a false sense of certainty. What you really want to know is, how likely are we to lose money?

• A thirteenth cafe?
• The spreadsheet is full of guesses
• How likely are we to lose money?

Running the future many times

Instead of one guess for each number, we give each one a range. Visitors, spend, and margin use a bell curve. Building cost uses a triangle, with a best case, a likely case, and a nasty worst case, because overruns are more common than savings. Then we work out the result a hundred thousand times, with different random values each time. Run it. Instead of one number, we get a full picture. The average first year loses around eighty thousand dollars, once you include the fit out. There's an eighty nine percent chance of a loss in year one. And in the worst five percent of cases, it loses about a hundred and eighty six thousand. That tells the founder to expect a first year loss, and to have enough cash to survive it. Spreadsheets can't tell you that.

import numpy as np

rng = np.random.default_rng(0)
n = 100000
visitors = rng.normal(90000, 15000, n)
spend = rng.normal(5.2, 0.5, n)
margin = rng.normal(0.62, 0.04, n)
fit_out = rng.triangular(180000, 220000, 320000, n)

year_one = visitors * spend * margin - 130000 - fit_out

print(f"Average result: ${year_one.mean():,.0f}")
print(f"Chance of a loss: {(year_one < 0).mean():.0%}")
print(f"Worst 5%: ${np.quantile(year_one, .05):,.0f}")
print(f"Best 5%: ${np.quantile(year_one, .95):,.0f}")

Average result: $-80,050
Chance of a loss: 89%
Worst 5%: $-186,491
Best 5%: $31,945

Good simulation habits

The result is only as good as your ranges, so ask the people who know, rather than guessing alone. Most people give ranges that are far too narrow. Numbers may also be linked. If visitors are low, spend might well be low too, and ignoring that makes the risk look smaller than it is. And show the result as a chance of success, rather than as a single number.

Summary