How often will the reserve you set fall short?

Every factor looks tame: shipments near forecast, failure rate in a band, repairs near typical. Multiplied together they grow a tail, and the reserve that covers the average fails far more often than intuition says.

Operations Starter Monte Carlo Pro engine

Install free (opens the Google Workspace Marketplace in a new tab) How Monte Carlo simulation works

After you install, this is the model to open.

Is the Warranty Reserve Enough?

  1. In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
  2. Click Start from a template and put that name in the search box.
  3. Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.

This one runs on a Pro engine, and every free install includes five full-quality runs on your own numbers, shared across all five Pro engines rather than five for each. After that, Pro is $199/year.

The answer

Twenty thousand simulated years:

Warranty cost, mean
$130K
Reserve
$140K
Reserve short
39% of trials

The reserve sits above the mean and still fails four years in ten, because multiplication grows tails and averages never warn you. Type $205K into the reserve row and rerun: the failure rate drops to about 5%, and now percentile-based reserving has made its own argument.

The model

Units shipped Normal around 40,000, failure rate uniform 1.2%-3.1% (a new product's honest band), repair cost PERT $110-$145-$220, against a $140K reserve.

Units shippedNormal(40,000, 4,500)
Failure rateuniform 1.2%-3.1%
Repair costPERT $110-$145-$220
P(reserve short)simulated

Once it is in your sheet

  1. The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
  2. Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
  3. Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.

Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.

Next question

Every model like this one, and the method behind them: Monte Carlo simulation.

Install free (opens the Google Workspace Marketplace in a new tab) All 379 templates