Why Spreadsheets Understate Risk, and How to Fix It

By the XLsheetAI Team · Updated September 27, 2026 · 12 min read

The short answer

Spreadsheets understate risk because a cell holds one value, so an uncertain quantity has to be flattened into a single best guess before the model will accept it. The output is then a single number that carries no information about how wrong it might be, and readers treat the absence of a stated range as evidence that there is not much of one. On top of that, a model containing any cap, floor, threshold or product does not return the average outcome when fed average inputs, so the single number is usually biased and not merely imprecise. The remedy is not a more careful estimate. It is to make the model carry a range from input to output, which plain Excel can do in four escalating steps, none of them requiring an add-in.

The uncertainty is discarded before any calculation happens

Consider what happens when someone builds a forecast. Sales volume next year is genuinely unknown — it might be 8,000 units, it might be 12,000. To get it into the model, they type 10000. Unit cost might be anywhere from £18 to £26 depending on the supplier contract and currency. They type 22.

The model now runs perfectly on numbers that were never facts. And this is the important part: once 10000 is in the cell, nothing downstream can tell that it was a guess with a ±20% range around it. The cell looks identical to a cell holding a figure copied from last year's audited accounts. The spreadsheet has no type for "approximately," no way to mark a cell as uncertain, and no mechanism for carrying a range forward through an arithmetic operation.

So when the model produces a profit figure of £68,420, that number inherits the visual authority of a calculated result. It is presented to a board, minuted, and becomes the basis of a commitment. Nobody in the room has any way of knowing whether the honest answer was "somewhere between £20,000 and £115,000."

This is the core mechanism, and it is worth being precise about the blame. The arithmetic is not wrong. The formulas may be flawless. The information loss happened at data entry, before a single calculation ran, and no amount of checking the formulas will recover it.

The flaw of averages, with numbers you can check

Here is the part that turns an imprecise answer into a biased one. Most people assume that if you feed a model its most likely inputs, you get its most likely output, or at least something close to the average. That is only true when the model is a straight line from input to output. Real models rarely are.

Example one: a capacity cap

Demand next quarter is equally likely to be 800, 1,000 or 1,200 units. You can produce a maximum of 1,000. Margin is £50 a unit.

The single-number model uses average demand, 1,000 units, sells all of them, and reports £50,000 of margin.

Now work out what actually happens in each of the three equally likely cases. At demand of 800 you sell 800. At 1,000 you sell 1,000. At 1,200 you sell 1,000, because that is all you can make. Average units sold is (800 + 1,000 + 1,000) ÷ 3 = 933.3, so average margin is £46,667.

The single-number model overstates the expected result by £3,333, about 7%, and gives no indication that it has done so. The reason is structural: the cap truncates the upside while nothing truncates the downside. Every model containing a capacity limit, an inventory constraint, a contractual maximum, a tier boundary or a minimum order quantity has this shape, and every one of them is optimistic at average inputs.

Example two: things that must all go right

A project has three tasks that must all complete on schedule. Each has, honestly assessed, a 50% chance of doing so.

A spreadsheet built on most-likely durations shows every task hitting its date, so the project hits its date. The plan looks achievable.

The actual probability that all three finish on time is 0.5 × 0.5 × 0.5 = 12.5%. The plan the spreadsheet endorses has roughly a one-in-eight chance of happening. Add a fourth such task and it is one in sixteen.

Nothing in the spreadsheet is wrong. Each individual duration is the most likely value for that task. The error is in treating a conjunction of most-likely events as itself likely, which is the single most common way project and launch plans become fiction. Note also the direction: this failure mode is always optimistic, never pessimistic.

Example three: inputs that move together

Suppose your model has sales volume and input cost as separate cells. In a downturn, volume falls. In the same downturn, your currency weakens and input cost rises. In reality these two are linked through the same underlying cause.

A spreadsheet treats them as two independent numbers, and so does a naively built simulation. Both therefore assume that low volume and high cost landing together is an unlikely coincidence, when it is in fact the normal shape of a bad quarter. The consequence is specific: correlation neglect barely changes the average outcome but badly understates the tail, which is precisely the part you were trying to measure.

A worked warning: when the risk model is the risk

There is a documented case of exactly this failure at the largest possible scale, and the primary source is worth reading rather than the secondhand versions.

JPMorgan's own Management Task Force report into the 2012 Chief Investment Office losses describes a Value-at-Risk model that "operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another." It then records the defect: "the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR."

Two things deserve emphasis, because both are usually reported wrongly. The Task Force says it is "unclear by exactly what amount" the VaR was lowered. And the losses are attributed to the trading strategy, not to the spreadsheet — the spreadsheet fault removed a warning light rather than causing the crash.

That distinction is the lesson. A risk model is the one part of a model whose job is to tell you when to worry. When it is built from manually copied sheets, a defect in it does not announce itself as a broken number; it announces itself as calm. The report also notes that the model review group had already called the computation "error prone" and "not easily scalable," and approved it on a promise of automation that never arrived.

The same report is one of the cases in the costliest spreadsheet errors ever made, alongside several others where the file kept working perfectly while being badly wrong.

The four-step ladder out

You do not need a simulation package, and for most decisions you do not need a simulation. Climb only as far as the decision justifies. Each step is worth more than the one after it.

Step 1: replace every point with three numbers

The cheapest improvement available. For each uncertain input, put three cells side by side instead of one: Low, Base, High. Do not calculate anything with them yet. Just write them down.

This does two things immediately. It forces the estimate to be honest — writing "8,000 to 12,000" is a different act from writing "10,000" — and it makes the model's uncertainty visible to anyone reading it, which is often the entire benefit. Ask for the low and high as the levels you would be genuinely surprised to fall outside, rather than a vague worst and best case, so the range means something specific.

Add a source column next to them. An input estimated from three years of history and an input somebody guessed in a meeting should not look alike on the page.

Step 2: sensitivity, to find the inputs that actually matter

Most models have twenty or more inputs. Usually two or three of them drive nearly all the variation in the answer, and effort spent refining the others is wasted. A one-way data table finds them in under a minute.

Put a column of candidate values for one input down a column, put a reference to your output cell one row above and one column to the right of them, select the whole block including both, and choose Data → What-If Analysis → Data Table, entering that input's cell as the Column input cell. Excel recalculates the entire model once per row and writes each result back.

Do this for each uncertain input in turn, then compare how much the output swings across each one's plausible range. The result is often uncomfortable: the input everybody argued about in the meeting turns out to move the answer by 2%, while the one nobody questioned moves it by 40%.

Step 3: scenarios, not one-at-a-time wiggles

Sensitivity tables vary one input while holding the rest at base, which is useful for ranking inputs and misleading as a picture of risk — because in a genuine downturn, several inputs go wrong together. This is the correlation problem from earlier, and scenarios are the low-tech answer to it.

Build three or four internally coherent worlds. Not "volume down 20%," but "recession: volume down 20%, price pressure up, currency weaker, so cost up 8%, and payment terms stretch." Each scenario is a named set of inputs that could genuinely co-occur. Use a column per scenario and a single selector cell that switches which column feeds the model, with =CHOOSE($B$1, low, base, high) or =INDEX(range, , $B$1).

Then add the question most models never ask: at what value does the decision change? Use Data → What-If Analysis → Goal Seek to find the volume at which the project stops clearing its hurdle rate. "We need 7,400 units to break even and we are forecasting 10,000" tells a board something a profit figure cannot.

Step 4: simulate, if the decision earns it

Plain Excel runs a Monte Carlo simulation with no add-in. Worth doing when the model has several interacting uncertainties and the decision is large enough to justify the care.

Two practical notes. RAND() is volatile, so every recalculation reshuffles every number, including while you are reading them — copy the results column and paste as values before quoting anything. And volatile functions across thousands of rows are the usual reason a model becomes unbearably slow, which why is my Excel file slow covers in detail.

The output that matters is not the average. It is the sentence you can now write: "there is roughly a one-in-five chance this returns less than our cost of capital." That is a statement a decision can be made against.

Where simulation lies to you as well

This section matters more than the previous one, because a simulation produces charts, and charts are persuasive in proportion to their smoothness rather than their truth.

• It propagates your assumptions; it does not test them. If you invented the standard deviation, the output is a precise picture of an invented world. That is more dangerous than an honest single number, because it looks like evidence.
• Distribution choice does most of the work. A normal distribution has thin tails. Many business quantities — project overruns, claim sizes, demand shocks — have fat ones and are skewed. Assuming normality is itself a decision to understate extremes, and it is usually made by default rather than on purpose.
• Independence is the default and is almost always wrong. Unless you explicitly link them, every input is drawn independently, so the simulation quietly assumes your bad news arrives one item at a time.
• It cannot see defects in the model. Run the JPMorgan VaR sheet a million times and it returns a beautifully stable distribution of understated volatility. Simulation validates nothing about the formulas it is running.
• More iterations buy precision, not accuracy. Going from 1,000 to 100,000 runs tightens the estimate of your assumptions' consequences. It gets you no closer to reality.

Held properly, a simulation is a tool for showing your assumptions their own consequences — which is genuinely valuable, and is a much smaller claim than the chart implies.

Why pricing models get this wrong most often

Pricing deserves its own note, because it concentrates every shape that breaks single-number analysis.

Demand responds to price non-linearly, so a small price change does not produce a proportional volume change. Volume discounts and tier boundaries create thresholds, where one unit of difference moves a whole order into a different band. Capacity caps the upside while nothing caps the downside. Fixed costs mean profit is highly geared to volume, so uncertainty in volume is amplified on its way to the bottom line. And cost and volume are usually correlated through the same economic conditions.

Each of those on its own is enough to make profit-at-average-demand differ from average profit. Together they mean a pricing spreadsheet's headline number is close to meaningless without a range beside it. The minimum honest output for a pricing decision is three figures: the expected margin, the margin at the fifth percentile, and the probability of landing below the hurdle. All three come out of step 4 above, and the first two come out of step 3.

Building a sensitivity table or a percentile formula?

Describe what you need in plain English and XLsheetAI writes the formula, explains why each argument is there, and lets you practise it — from PERCENTILE.INC to the INDEX switch that drives a scenario selector.

A short checklist for any model that informs a decision

• Every input cell is either a fact with a source, or an estimate with a stated low and high. No unlabelled third category.
• The output is reported as a range with a probability attached, never as a single figure to two decimal places.
• You have run a one-way sensitivity table and know which two or three inputs drive the answer.
• You can name the value at which the decision reverses, not just the value you expect.
• Inputs that share a cause are linked in the model, not varied independently.
• Anywhere the model contains a cap, a floor, a threshold or a product, you have checked whether the average-input answer is biased — because it will be.
• Someone other than the author has read the formula layer. Press Ctrl + ` to show every formula at once, and look for one that differs from its neighbours in a column.

The last point connects to the wider issue: a model can be perfectly calibrated about uncertainty and still be arithmetically broken. Those are separate problems needing separate controls, and the reconciliation-check approach used for pay reviews works just as well on a forecast. If the reason your model is hard to experiment with is that it takes thirty seconds to recalculate, Power Query versus formulas covers moving the heavy data preparation out of the formula layer entirely.