"Just add 10%." It's still the most common way to set contingency, and it's quick. It's also a guess. The good news: doing it properly isn't nearly as hard as it sounds, and you can start in Excel today.
To calculate contingency with Monte Carlo, you simulate the total project cost thousands of times using ranges and risk events, read off the cost at your chosen confidence level (say P80), and subtract the base estimate. What is left is your contingency. Below are the steps, with Excel formulas and a worked example. For the reasoning behind each step, read how to set a P80 contingency.
What you need before you start
- A base estimate broken down into cost areas. Ten to thirty lines is plenty.
- For each line, a minimum, most likely and maximum value.
- A risk register with a probability and an impact range for each material risk.
- An agreed confidence level for the budget (see P50, P80 and P90 explained).
Step 1: Put a range on every estimate line
Each cost line gets three values. The triangular distribution is the simplest option and easy to explain to anyone. PERT puts less weight on the extremes and suits items you know well. Whichever you pick, be honest about how wide the ranges are. Ranges that are too narrow are the weakest spot in many models.
Step 2: Draw a random value for each line
In Excel, put a random number in its own cell, for example =RAND() in a cell named U. Then turn it into a triangular value with minimum A, most likely M and maximum B:
=IF(U<(M-A)/(B-A), A+SQRT(U*(B-A)*(M-A)), B-SQRT((1-U)*(B-A)*(B-M)))
In words: below the turning point (M−A)/(B−A) the value rises from A towards M; above it, it runs from M towards B.
Step 3: Add the risk events
A risk either happens or it does not. For a risk with probability P and a triangular impact:
=IF(RAND()<P, [triangular formula for the impact], 0)
Use one random number for whether it happens and another for how much it costs. And before you add a risk, check that it isn't already hiding inside one of your estimate ranges. Counting it twice quietly inflates the contingency.
Step 4: Add correlation
Cost lines don't move independently. When one goes up, others often follow. A simple way to build that into a spreadsheet is a shared random factor. With one common standard normal draw Zc, one draw Zi per line and a correlation ρ:
=NORM.S.DIST(SQRT(ρ)*Zc + SQRT(1-ρ)*Zi, TRUE)
Draw each Z with =NORM.S.INV(RAND()). Use the result as U in Step 2, and every pair of lines will be correlated at roughly ρ. On large engineering programs, values between 0.3 and 0.6 are common, and 0.4 is a frequent default for cost when you have nothing better. Skip this step at your peril: Book (1999) showed that with 30 lines correlated at only 0.2, assuming independence understates the spread of the total by about 60%. More in our guide on correlation.
Step 5: Run it thousands of times
Add all lines and risks up in one total cost cell. Then let Excel recalculate it over and over:
- In an empty column, number the rows 1 to 10,000.
- Next to the first number, link to your total cost cell.
- Select both columns and go to Data > What-If Analysis > Data Table. Use any empty cell as the column input cell.
- Excel now recalculates the model once per row and stores every total.
Ten thousand iterations is usually enough for stable P80 and P90 values. A quick check: recalculate twice. If the P80 moves by more than a small fraction, add iterations.
Step 6: Read the confidence levels
Use =PERCENTILE.INC(results, 0.5) for the P50 and =PERCENTILE.INC(results, 0.8) for the P80. Sort the results and plot them against their rank, and you have your S-curve.
Step 7: Calculate the contingency
Contingency at P80 = P80 − base estimate.
Worked example: the Millbrook Connector
Illustrative case. The project and figures are fictional.
The fictional Millbrook Connector has a base estimate of $120 million across seven cost lines, six quantified risks with a combined expected value of $7.1 million, and a correlation of 0.4 between the cost lines. The full input is in the practitioner's guide. Running 100,000 iterations gives:
| Result | Value ($M) |
|---|---|
| Base estimate (sum of most likely values) | 120.0 |
| Mean of estimate lines alone | 129.2 |
| Mean including risk events | 136.3 |
| P50 | 135.8 |
| P80 | 143.4 |
| Contingency at P80 | 23.4 (20% of the estimate) |
Two things surprise most people here. The risk register only adds $7.1 million on average; most of the contingency actually comes from the skew in the estimate ranges. And without correlation, the P80 would have come out at about $141.9 million, so you would have been some $1.5 million short without knowing it.
Step 8: Decide who holds the contingency
A number on its own doesn't protect anything. How you govern it does. Three rules that hold up in research and in practice:
- Pool it. Splitting contingency across work packages makes overruns more likely, even when the total is big enough (Kujawski, 2002).
- Layer it. For example, the project manager controls up to P50, the sponsor holds the step to P80, and the board decides beyond that.
- Release it. When a risk passes without happening, give its share back to the reserve and run the model again.
Contingency versus management reserve
Contingency covers known risks and estimate uncertainty within the agreed scope. A management reserve covers what the model can't see, like scope changes or events nobody thought of. Keep them apart in your reporting, so each one can be justified on its own terms.
Frequently asked questions
Can I do this without special software?
Yes. The steps above work in plain Excel. Dedicated tools just make correlation, auditing and reporting faster and less error-prone; see Excel tools, add-ins or dedicated risk software.
Triangular or PERT?
Triangular is simpler and a bit more conservative in the tails. PERT suits items where you trust the most likely value. Honestly, the choice matters less than having honest ranges.
How often should I recalculate?
At every stage gate, after major contract awards, and whenever a top driver changes.
Quantil's QRCIM does all of this in a single Excel workbook, including correlation, drivers and a board report. Try the free demo.
References
- AACE International. Recommended Practice 41R-08: Risk Analysis and Contingency Determination Using Range Estimating.
- Book, S. A. (1999). Why correlation matters in cost estimating. 32nd Annual DoD Cost Analysis Symposium, Williamsburg, VA.
- Kujawski, E. (2002). Why projects often fail even with high cost contingencies. Systems Engineering, 5(2), 151–155.
- Kujawski, E., Alvaro, M. L., and Edwards, W. R. (2004). Incorporating psychological influences in probabilistic cost analysis. Systems Engineering, 7(3), 195–216.