Back to the examples page  Back

Post a commentPost a comment

 

Please, feel free to download:

Excel spreadsheet model

> 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:

  • the probability to occur
  • the cost which would result from it
The project management team wants to estimate:
  • the average cost overrun on this project
  • the value at risk (VaR) 5%, that is: the cost overrun C5% for which there is a 5% probability to be greater than C5%.

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 pmin to a maximum value pmax.

Uniform distribution

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

Prob. category pmin pmax
1 0% 50%
2 0% 20%
3 0% 10%
4 0% 5%
5 0% 1%

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.

Beta distribution

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

Cost category Cmin Cmax
1 0 € 500 €
2 0 € 2000 €
3 1000 € 5000 €
4 2000 € 10000 €
5 5000 € 100000 €

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:

Risk index Prob. cat. Cost cat.
#1 1 1
#2 3 4
#3 2 1
#4 3 2
#5 1 1
#6 2 1
#7 2 2
#8 3 1
#9 2 1
#10 3 2
#11 3 1
#12 3 4
#13 1 3
Risk index Prob. cat. Cost cat.
#14 3 3
#15 5 5
#16 1 1
#17 2 1
#18 3 2
#19 2 1
#20 2 2
#21 3 3
#22 2 1
#23 1 2
#24 1 2
#25 2 3
#26 2 2

  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

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:

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

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.

Excel model of the project risk analysis

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.


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

Expected project cost overrun

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:

Expected number of risks realized during the project

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.


 

Back  Back


 

Add a comment

Your username:

Your email address: (will not be displayed)

Your comment:

The posting of advertisements, profanity, personal attacks is prohibited. www.statscorer.com reserves the right to delete any message infringing this rule as well as irrelevant or non understandable contributions.