Learn Monte Carlo simulation for Excel models

Worked examples with free workbooks, and guides to Monte Carlo simulation for the models you already have in Excel. The numbers on these pages come from real runs of the xellstorm engine.

Worked examples

Each model opens in the app with its inputs, outputs and targets set, and its workbook can be downloaded.

• Business case and investment

  What is the chance of a negative NPV?

  A business case with a base-case NPV of USD 562k has a negative NPV in 29.6% of simulated outcomes. How to simulate NPV and IRR, with the workbook.
• Project cost and schedule

  How much contingency does a construction project need?

  Worked example: a building estimate’s most likely costs are exceeded in 94.2% of simulated outcomes. Size the construction cost contingency with Monte Carlo.
• Risk management

  How much contingency does a risk register need?

  Probability × likely impact sums to 291; the simulated mean loss is 359 and the worst 5% average 1,436 (USD thousands). Size contingency from the tail.
• Inventory

  Which reorder point balances cost against stockouts?

  Raising the reorder point from 180 to 250 units cuts the chance of a stockout year from 28.0% to 5.1% and adds 345 to an average annual cost of 2,133.
• Mechanical design

  Tolerance stack-up analysis: worst case, RSS or Monte Carlo?

  A housing and five parts: worst case lets the gap shrink to 0.120 mm; RSS predicts 3.29% of gaps below 0.4 mm and 20,000 Monte Carlo trials find 3.21%.
• Reliability engineering

  How likely is a series system to fail within a year?

  Four components in series with Weibull lifetimes: 79.0% of units fail within a year (8,760 h) and mean life is 5,712 h. Simulated vs exact.
• Inventory

  How many units should you order when demand is uncertain?

  Demand averages 1,000 units (sd 200); price 10, cost 4, salvage 1 per unit: the critical fractile orders 1,086.1 units, a Monte Carlo search 1,090 units.

Guides

• Guide

  Monte Carlo simulation in Excel, with or without add-ins

  Three ways to run a Monte Carlo simulation on an Excel model: RAND() and a data table, an add-in, or a browser tool with no add-in. PERT formula included.
• Guide

  P50, P80 and P90: what they mean and which to budget at

  P80 is the cost 80% of simulated outcomes stay under. Six building cost items’ P80s add up to 2,843, but their sum’s P80 is 2,756 (USD thousands).
• Guide

  PERT distribution in Excel: formula, mean and when to use it

  A PERT is a beta distribution from min to max with mean (min + 4 × likely + max)/6. The Excel BETA.INV formula, and PERT vs triangular.
• Guide

  Tornado charts and sensitivity analysis explained

  A tornado chart moves each input alone, from P10 to P90 by default in xellstorm. In a building estimate, one risk moves the cost by 150 (USD thousands).
• Guide

  Schedule risk analysis: why projects finish late

  A building schedule of most likely durations says week 55, yet only 14.3% of simulated outcomes finish by then. Why, and how to set a P80 finish.
• Guide

  Moving a model from @RISK, ModelRisk or Analytic Solver

  xellstorm reads 164 distribution functions of @RISK, ModelRisk and Analytic Solver and lists what it converted and why any formula was left.

xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.