one number is the least useful answer to a question about risk

Why the single number is always optimistic

You are estimating a project. Twelve cost items. For each one you write down your best guess, you add them up, and you get a total.

The trouble is that each of those guesses is a range, not a number, and the ranges are not symmetric. Things can go a bit better than expected. They can go enormously worse. Adding up twelve best guesses gives you a total that is roughly the middle, which sounds fine until you notice you have no idea how far the far end is.

And there is a second problem that is easy to miss. Those twelve items are not independent. If steel gets expensive, several of them get expensive together. If the schedule slips, several costs rise at once.

Adding up independent best guesses lets the highs and lows cancel each other out, which makes the total look far more predictable than it is. Correlated inputs do not cancel — they pile up.

How much correlation actually matters

This is the workbook's central claim, so it is asserted by the checker rather than assumed. If you sum inputs that move together, the spread of the total is wider than the independent sum would predict.

Roughly 60% more spread than an independent model predicts. That gap sits entirely in the tail — which is the region you built the model to understand. An estimate that ignores correlation is not slightly optimistic; it is confidently wrong about exactly the scenario you are trying to protect against.

How it works without an add-in

The whole thing rests on inverse-transform sampling. Every distribution has an inverse cumulative function; feed it a uniform random number between 0 and 1 and out comes a draw from that distribution. One formula per draw, and RAND() supplies the uniform number. That is why no add-in is needed.

Correlation uses a single-factor Gaussian copula, which is a fancy name for a simple trick:

Z_i = rho x Z + sqrt(1 - rho^2) x NORMSINV(RAND())
U_i = NORMSDIST(Z_i)

Every variable shares a common factor Z, and takes its own independent part alongside it. Z_i is standard normal by construction, so U_i stays uniform — which means every marginal distribution is preserved exactly and only the joint behaviour changes. Set rho to zero and you get independent sampling back.

The percentiles come from PERCENTILE() over the real trials, never from a normal approximation. The sum of skewed, correlated inputs is itself skewed, and fitting a normal curve to it would understate precisely the tail you care about.

How to run a Monte Carlo simulation in a spreadsheet

What is inside the file

Eight tabs. Every random draw is a visible cell, which is unusual: most simulation tools are a black box with a button.

Opening it in Excel, Google Sheets or Numbers

It is one .xlsx file. There are no macros, no add-ins and nothing to install, which is what makes it portable — a macro-driven template would be Excel-only.

This one leans on CORREL, MEDIAN, NORMINV, NORMSDIST, NORMSINV, PERCENTILE, RAND, RANK and STDEV — the functions people most often worry about losing in another app. All of them exist in Excel, Google Sheets, Apple Numbers and LibreOffice Calc, so the file works the same in all four.

The arithmetic, written out

draw            = INVERSE_CDF(distribution, U)      # inverse-transform sampling
U               = RAND(), clamped to [1e-6, 1 - 1e-6]

Z_i             = rho x Z + SQRT(1 - rho^2) x NORMSINV(RAND())
U_i             = NORMSDIST(Z_i)                    # single-factor Gaussian copula

P80             = PERCENTILE(all trial totals, 0.8)
standard error  = STDEV(totals) / SQRT(trials)

U is clamped away from 0 and 1 so the normal inverses stay finite at the extremes — without it a single draw at exactly 0 produces an infinity that poisons the whole run.

And the copula construction is what keeps this honest: because Z_i is standard normal by construction, U_i is uniform, so each variable's own distribution is exactly preserved. The correlation changes how variables move together and nothing else.

How I know the numbers are right

A stochastic model cannot be checked by matching values, so this one is checked by asserting the properties that must hold for a correct sampler and would fail for a broken one. The workbook is recalculated in LibreOffice with real draws.

Last three independent runs: 0 statistical failures, 0 formula errors.

• Every marginal matches theory — mean within five standard errors, standard deviation within 18%, for all 12 variables across triangular, uniform, normal and lognormal. A mis-transcribed inverse CDF fails here immediately
• Hard bounds hold: triangular draws never escape [min, max], lognormal never goes negative
• The shared factor Z is standard normal (mean ~0, sd ~1)
• Correlation actually bites — the observed spread of the total exceeds the independent-sum prediction, by a ratio of 1.59 to 1.64
• Percentiles are monotonic, and the sheet's P50 matches the empirical median
• The histogram accounts for every single trial
• The top sensitivity driver is genuinely among the highest-variance inputs

That histogram assertion caught a real bug. The bins were defined as > lo and <= hi, which silently drops any trial sitting exactly on a boundary — including the minimum. Three trials in a thousand were vanishing. The bins now tile [lo, hi) with the last one closed.

Compared with the alternatives

Questions people ask before buying

Related spreadsheets