Please, feel free to download:
> Depending on your navigator, you may need to right-click on the link to download files.
This example illustrates the use of Monte Carlo simulation and spreadsheet modeling to estimate the risk of cost overrun on a project.
A project management team has listed with risk experts a set of potential risks likely to be realized during a project. Each potential risk can be qualified regarding to 2 aspects:
To make simple, we will not try to justify the assumptions made about each potential risk.
We just want to model these assumptions with Excel and Statscorer and measure the outcome for the project.
Assumptions about the potential risks
Risk experts have identified a set of 26 potential risks.
A risk may be realized or not depending on a given probability. As it is difficult for them to figure out precisely, experts think that this probability follows roughly a uniform distribution, ranging from a minimum value pmin to a maximum value pmax.
They have defined 5 categories for the whole set of risks which provide values for pmin and pmax.
Thus, each potential risk is more or less probable depending on the category it belongs to. For instance, a risk belonging to category no. 3 may be realized according to a probability between 0 and 0,1. It can be 0,05 or 0,06 as well as any other value of the interval, in equiprobable way.
If a risk is realized, this one leads to a cost overrun. Risks can have a more or less important impact on cost overrun but the cost globally follows a Pert distribution (here, a Beta distribution with parameters α=2 and β=3) ranging from Cmin to Cmax.
Experts have also defined 5 categories for the whole set of risks which provide values for Cmin and Cmax.
Thus, each potential risk is more or less severe depending on the category it belongs to.
Note: categories are independent regarding the occuring probability aspect and the cost aspect. That is: 2 potential risks that belong to the same probability category may belong to completely different cost categories.
Below is the list of the 26 potential risks with their related probability category and cost category:
Modeling inside the Excel sheet
First, we have to define somewhere on the sheet the 3 tables listed above:
List of potential risks, their related probability and cost, defined inside the Excel sheet
Then, we have to notice that our model will be composed of:
Note: the parameter will take the values 1 to 26 and the output variable will be summed over this set of values, using the operator "Sum" available in Statscorer.
The Excel model allows to retrieve data from the tables while Statscorer generates values (yellow and green cells) and records the output (red cell)
As shown in the picture above representing the Excel sheet, the yellow cell will take the value of the main model parameter.
According to the value of this cell, we can retrieve the probability and cost categories thanks to the very useful Excel function "VLOOKUP".
Then Statscorer will generate a probability for X1 according to a uniform distribution, a value (whether 0 or 1) for X2 indicating the risk has been realized or not and a cost for X3 following a Beta distribution. These values are generated by Statscorer then written in the green cells.
Finally, Statscorer records the values taken by the red cell that stands for the output Y.
Output values are summed over the set of risks.
Results show that potential risks may lead to a possible cost overrun. According to the hypotheses experts made, the average cost overrun is estimated at 2890 €.
As for the value at risk (VaR), the cumulative function gives a VaR5% of 7800 € which means that 95% of the smallest possible cost overruns are less than 7800 € or we have 5% chance to be greater than 7800 €.
Cost overrun above 13000 € represents less than 0,8% of the possible outcomes. We limited on purpose the plot range to 13000 € to have a better view on what is going on below this value.
Statscorer plots the frequencies of the possible cost overrun and the cumulative distribution function (in red)
Optionally, we could also measure with Statscorer the number of risks that may be realized simultaneously during the project. The file downloadable online does not include this 2nd output but here is what we would get:
Number of simultaneous risks likely to be realized during the project.
We see that we have a probability of only 3,86% that no risk ever be realized.
On the opposite, the probability to have more than 6 simultaneous risks realized is less than the probability to have none of them. Cases where the number of realized risks is greater than 9 (from 10 to 26) are negligible (less than 0,02 % of all cases), though possible.
In conclusion, Monte Carlo simulations can be valuable for risk and decision analysis involved by project management activity. Results are all the more accurate as potential risks have been comprehensively identified and characterized regarding probability and gravity. With results like those described here, it is possible for a project management team to put aside a reasonable budget especially intended for risks ("risk provision").
Note: a similar study would have been possible with delay instead of cost. Monte Carlo method and stochastic modeling would have allowed us to set dependencies between risks, precisely like we would have done in MS Project with tasks, and estimate the total expected delay on a project.
Add a comment