An example of a Monte Carlo simulation risk analysis model for general risk analysis
Technical difficulty: 2
Techniques used: Monte Carlo simulation in Excel
ModelRisk functions used: VoseRiskEvent
Consider the example where we have a portfolio of risks from A to J, and we've given the minimum, most likely and maximum values for the size of the impact. Each of these risks represents an event that causes a harmful effect on the project (i.e. bad weather, strike, machine breakdown, storm, etc. ). 80% and 150% of the most likely value define the minimum and maximum correspondingly for simplicity.
Of course, if this was a real world event we wouldn't always use the same percentage for each of the risks, we would use expert elicitation methods to estimate the impact of each of those risks. The values for the minimum, the most likely and the maximum allow us to create a Pert distribution for each of the risks and then we also have the probability of occurrence for each of the events. The risks in this example model are correlated in the following ways: if risks A or C have occurred then D increases it's probability to 50% and if A or D occurs then E increases it's probability to 45%. If all of B, C, E, F occur, H increases in size by 320% and to 13% probability. In other words these are risks that are dependent on other events occurring, effectively it becomes a knock on effect. Our task is to calculate the total risk of the portfolio.
We use the VoseRiskEvent function to model risk events, and Excel's logic functions (IF, AND, OR) as shown:
Conclusion from the model: A knock on effect happens when the occurrence of one risk makes another risk more likely to occur or it makes the size of the impact for another risk much greater if it does occur. Failing to recognize these knock on effects means that we underestimate the importance of the various risks that we've already identified. And that is a big mistake, leading to over- or underestimation of the total risk.
Click on the button below to download the Excel model. ModelRisk needs to be installed in order for the model to work.