ModelRisk example · General techniques
Probability of Ruin
Could this year's risks ruin the company? A register sorted by expected cost hides the one that can.
From the model’s saved run
Probability of ruin
The chance that this year's loss reaches the threshold on the Inputs sheet.
| Measure | Unit | Value |
|---|---|---|
| Probability of ruin, exact | probability | 0.10% |
| Probability of ruin, simulated | probability | 0.14% |
| Ruin threshold (Inputs sheet) | US$k | 5,000 |
| Risks without which ruin is impossible | J | |
| Expected loss (exact) | US$k | 367 |
| Mean loss (simulated) | US$k | 365 |
Chart: Total loss.
The business question
A small engineering company keeps a register of ten risks, each with a probability for the coming year and a cost if it happens. Like many registers, it is sorted by expected cost, probability × cost, so that the risks at the top get the most attention.
The company's reserves and bank facilities can absorb a loss of up to US$5 million (5,000 thousand) in a year. A larger loss would ruin it. The model simulates the year to find the probability of ruin, calculates the same probability exactly, and shows which risks can cause it. Expected cost and ruin rank the risks very differently.
- Who it is for
- Risk managers, finance directors, and anyone who sorts a risk register by probability × impact.
- Main outputs
- The probability of ruin, simulated and exact · the expected loss · the risks without which ruin is impossible · the probability of a loss above other thresholds · the losses exceeded once in 2 to 2,000 years · the tornado of the risks.
How the model works
Three sheets after this one, read from left to right. Each one feeds the next.
Inputs sheet
The ruin threshold: the loss in one year that the company could not survive.
Risks sheet
The register, sorted by expected cost, with two questions for each risk: can it ruin the company on its own, and is ruin still possible without it? In each iteration VoseBernoulli decides which risks happen and their costs are added up.
The exact probability of ruin adds up the probabilities of every combination of risks whose total reaches the threshold: 2^10 = 1,024 combinations, in one formula.
Results sheet
The probability of ruin, simulated and exact. The exact probability at other thresholds; the losses exceeded once in 2 to 2,000 years; the tornado of the risks.
ModelRisk techniques
Find similar models
- Product
- Type
- Application
- Uses
The results in detail
Ruin at other thresholds
Exact probability that the year's loss reaches each threshold: every combination of the risks.
| Threshold | Unit | Probability | Once in |
|---|---|---|---|
| A loss of 1,000 or more | US$k | 5.75% | 17 years |
| A loss of 2,000 or more | US$k | 0.17% | 591 years |
| A loss of 3,000 or more | US$k | 0.10% | 998 years |
| A loss of 4,000 or more | US$k | 0.10% | 1,000 years |
| A loss of 5,000 or more | US$k | 0.10% | 1,000 years |
Which risks matter
Lowest and highest mean loss over ten equal groups of each risk's cost (VoseSimSensitivityMean): the effect of a risk when it happens.
Tornado chart: the inputs ranked by how far the mean output moves across each input’s range.
The ranking as a table 10 inputs
| Input (ranked by influence) | Lowest mean | Highest mean | Rank |
|---|---|---|---|
| J Fatal accident caused by a delivered machine | 354 | 8,369 | 1 |
| I Fraud by an employee | 356 | 1,341 | 2 |
| D Fire at the main workshop (uninsured part) | 326 | 1,149 | 3 |
| C Product liability claim | 316 | 813 | 4 |
| A Key fixed-price project overruns | 267 | 660 | 5 |
| E IT failure with loss of data | 336 | 632 | 6 |
| B Loss of a major customer | 307 | 502 | 7 |
| G Key engineers leave | 341 | 462 | 8 |
| F Late-delivery penalties | 335 | 436 | 9 |
| H Exchange-rate loss on imported parts | 346 | 409 | 10 |
How to read these results
- Sorted by expected cost, J comes last: 0.1% × 8,000 = 8. Yet it is the only risk that can ruin the company: the other nine together cost at most 3,470.
- So the probability of ruin is J's probability, 0.1%: once in a thousand years. A simulation of 10,000 years sees it only about ten times, and its estimate moves by about a third from one seed to another. The exact figure needs no simulation.
- At lower thresholds the ordinary risks start to combine: a loss of 1,000 or more comes about once in 17 years, almost always without J.
- Expected cost is the right ranking for a budget, not for survival. For ruin, rank the risks by what they do when they happen, as the tornado does, and look at the far end of the return-period curve.
See it move. Open the workbook, change an input on the Inputs sheet and run the simulation: every table and chart on this page updates in Excel.
Inputs and assumptions
Every assumption in the model, with its uncertainty and where it comes from. The figures are illustrative.
Ruin 1 input
The loss in one year that the company could not survive.
| Assumption | Unit | Value |
|---|---|---|
| Loss that would ruin the companyReserves plus the bank's facilities: a loss this large or larger is ruin. | US$k | 5,000 |
Units and conventions
- Money
- US$ thousand (US$k): 5,000 is US$5 million.
- Ruin
- A loss of at least the threshold in one year.
- Return period
- A loss exceeded once in T years on average: the (1 − 1/T) percentile of the annual loss.
Key assumptions and limitations
- Independence
- The risks happen independently of each other.
- Fixed costs
- Each risk costs a fixed amount if it happens, as in the original example.
- One year
- Only this year's losses count, against the reserves at the start of the year.
- Data
- The company and its risks are invented for this example; the probabilities and costs are those of ModelRisk's earlier Probability of Ruin example.
How to use it
- Replace the ten risks on the Risks sheet with your own: probability and cost.
- Set the ruin threshold on the Inputs sheet.
- Run a simulation from the ModelRisk ribbon (10,000 samples with a fixed seed are set).
- Compare the two rankings: by expected cost, and by what each risk does when it happens.
The tornado chart fills in after a simulation with ModelRisk’s classic engine.
ModelRisk functions used
- VoseBernoulli
- Whether each risk happens this year.
- VoseInput, VoseOutput
- Name each risk's cost and the total loss, for the Results Viewer and the tornado.
- VoseSimMean
- The probability of ruin: the mean of the 1/0 ruin outcome over the iterations.
- VoseSimPercentile
- The return-period curve: the loss exceeded once in T years.
- VoseSimSensitivityMean
- The tornado chart: each risk ranked by how far the mean loss moves when it happens.
- SEQUENCE, MMULT (Excel)
- The exact probability: every combination of the risks in one formula.
Further reading
- Sensitivity analysis in Excel: a tornado chart step by step (Vose guide)
- Vose, D. (2008). Risk Analysis: A Quantitative Guide, 3rd edition. Wiley.
- Hillson, D. and Simon, P. (2020). Practical Project Risk Management: The ATOM Methodology, 3rd edition. Berrett-Koehler.
Download the workbook and run it yourself. It opens in Excel with ModelRisk. The free trial runs every example model.
Related example models
- ModelRisk★★★★★Tornado of a Percentile: What Drives a Bad YearWhat drives the downside of the profit, not just its average?One year of a copper mine's profit from six uncertain inputs; the percentile tornado tables come from array formulas.
- ModelRisk★★★★★Correlation Through a Common Factor (Look-up Table)How much later does a project finish when bad weather hits every activity at once?One shared random factor and a look-up table make six durations move together, without a correlation matrix.
- ModelRisk★★★★★Envelope Correlation: Interest and Mortgage RatesHow do you make one variable follow another when you only know its range at a few points?Correlating two variables from expert ranges: the second's range follows the first.
An illustrative example: the figures are invented for teaching and do not describe a real company, project or asset. Last updated 9 October 2026.