Back

Post a comment

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:

• the probability to occur
• the cost which would result from it

• the average cost overrun on this project
• the value at risk (VaR) 5%, that is: the cost overrun C_5% for which there is a 5% probability to be greater than C_5%.

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.
The purpose of this example is not to explain the project risk management matter but the way quantitative analysis can help project management face to risk issues and decision making.

  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 p_min to a maximum value p_max.

They have defined 5 categories for the whole set of risks which provide values for p_min and p_max.

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 C_min to C_max.

Experts have also defined 5 categories for the whole set of risks which provide values for C_min and C_max.

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:

Then, we have to notice that our model will be composed of:

• a main parameter variable : the risk index, ranging from 1 to 26
• a 1^st input variable X₁ : the probability for the risk to be realized
• a 2^nd input variable X₂ : according to X₁, the fact that the risk has been realized (X₂=1) or not (X₂=0)
• a 3^rd input variable X₃ : the cost consecutive to the risk if this one is realized
• an output variable Y : the effective cost, that is Y = X₂ . X₃

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.

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 X₁ according to a uniform distribution, a value (whether 0 or 1) for X₂ indicating the risk has been realized or not and a cost for X₃ 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.

  Simulation results

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 VaR_5% 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.

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 2^nd output but here is what we would get:

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.

 Back

Add a comment