Let's 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 just for simple reasons of ease. 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 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 dependant on other events occurring, effectively it becomes a knock on effect. Our task is to calculate the total risk of the portfolio.
The model Correlated risk portfolio shows the solution to this example.
The links to the Correlated risk portfolio software specific models are provided here:
The logic of the model is illustrated below:
Cells in column D show the most likely values for each of the risks, and with values in column C (for the minimum) and E (for the maximum) describe the parameters for the PERT distribution. Column F gives the output of the Pert distribution to create a distribution for each risk (see here for an explanation how to create a PERT with Crystal Ball). Column G shows the probability of each risk occurring, with constant figures for risks A,B,C,F,G,I and J, and variables for risks D, E and H. Cell G13 for example will return the value of 50% if either of the cells I10 and I12 is greater than zero, i.e. if risks A or C occur, and the value of 15% otherwise. The same type logic has been applied to the cells G14 and G17, with the latter returning 13% if all of the cell H11, H12, H14 and H15 are non-negative numbers and returning 5% in any of those cells has a zero value.
The syntax of Excel allows skipping the ">=0" notation, so the expression "=IF(H10>=0, 50%, 15%)" can be reduced to "=IF(H10, 50%, 15%)".
Cells in column I calculate the impact of each of the risks if they occur, of return zero otherwise. For all cells except I17, the formula is a multiplication of two components: a) the binomial distribution with the number of trials equal to 1 and the probability of success taken from the corresponding cell in column G; b) the impact of each risk from the corresponding cell in column F.
Finally, Cell I17 checks if the risks B, C, E, F occur (determined with a Binomial(G17,1) in Cell O17, which is the same as a Yes/No(G17)), and if they do, increases the probability of occurrence by the factor of 3.2.
The logic of the model is illustrated below:
Cells in column D show the most likely values for each of the risks, and with values in column C (for the minimum) and E (for the maximum) describe the parameters for the PERT distribution. Column F uses the @RISK's RiskPert function to create a distribution for each risk. Column G shows the probability of each risk occurring, with constant figures for risks A,B,C,F,G,I and J, and variables for risks D, E and H. Cell G13 for example will return the value of 50% if either of the cells H10 and H12 is greater than zero, i.e. if risks A or C occur, and the value of 15% otherwise. In the screen shot above we see that risk C has occurred, and the probability of the occurrence of D has increased to 50%.
The same type logic has been applied to the cells G14 and G17, with the latter returning 13% if all of the cell H11, H12, H14 and H15 are non-negative numbers and returning 5% in any of those cells has a zero value.
The syntax of Excel allows skipping the ">=0" notation, so the expression "=IF(H10>=0, 50%, 15%)" can be reduced to "=IF(H10, 50%, 15%)".
Cells in column H calculate the impact of each of the risks if they occur, of return zero otherwise. For all cells except H17, the formula is a multiplication of two components: a) the binomial distribution with the number of trials equal to 1 and the probability of success taken from the corresponding cell in column G; b) the impact of each risk from the corresponding cell in column F.
Cell H17 checks if the risks B, C, E, F occur, and if they do, increases the probability of occurrence by the factor of 3.2.
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.
