Modeling the Compound Effect of Concurrent Occurrences of Risk Events with @RISK

When modeling risk events, it is common that several events could affect the same cost element of a project. During the simulation, two or more risk events can occur at the same time. The question becomes how to calculate the total impact. This type of modeling technique is very common and often needed in project risk analysis, contingency and mitigation studies, reserve estimation and production forecasting.

A common practice is to aggregate the total impact. However, this simplistic approach might not be correct since it ignores the compounding effect of multiple occurrences. For example, if we have two risk events occurring and their respective impacts are a 20% and 40% cost increase, an additive model will calculate the total impact as 20%+40%=60%. On the other hand, a multiplicative (compound) model will calculate the total impact as 1.2 x 1.4 = 1.68; here the total impact is 68%. In other words, the impact of these risk occurrences will be greater than the summation of the individual impacts, which in many cases makes sense.

Using the table below we will show how you can use @RISK (risk analysis software using Monte Carlo simulation in Excel spreadsheets) to can calculate the distribution of the total impact that can affect the cost of a project activity. You can see that in this example we also model the opportunity of a cost reduction.

To model the % cost increase and reduction we use a Triangular distribution. Here we say that the maximum impact is the value in column F (the user can use other distributions or parameters as needed). With this logic the distribution of the impact of Risk 1 looks like:

and the total impact distribution results in:

The following table contrasts the results obtained using a compound and an additive model:

Something to note in the table above is that in the opportunity side, the maximum cost reduction using the compound model is less conservative than the additive model.

As seen here, coding this type of model is not difficult. My suggestion is that when you have multiple occurrences of risk events you explore better alternatives than the additive model. 

When modeling risk events, it is common that several events could affect the same cost element of a project. During the simulation, two or more risk events can occur at the same time. The question becomes how to calculate the total impact. This type of modeling technique is very common and often needed in project risk analysis, contingency and mitigation studies, reserve estimation and production forecasting.

A common practice is to aggregate the total impact. However, this simplistic approach might not be correct since it ignores the compounding effect of multiple occurrences. For example, if we have two risk events occurring and their respective impacts are a 20% and 40% cost increase, an additive model will calculate the total impact as 20%+40%=60%. On the other hand, a multiplicative (compound) model will calculate the total impact as 1.2 x 1.4 = 1.68; here the total impact is 68%. In other words, the impact of these risk occurrences will be greater than the summation of the individual impacts, which in many cases makes sense.

Using the table below we will show how you can use @RISK (risk analysis software using Monte Carlo simulation in Excel spreadsheets) to can calculate the distribution of the total impact that can affect the cost of a project activity. You can see that in this example we also model the opportunity of a cost reduction.

To model the % cost increase and reduction we use a Triangular distribution. Here we say that the maximum impact is the value in column F (the user can use other distributions or parameters as needed). With this logic the distribution of the impact of Risk 1 looks like:

and the total impact distribution results in:

The following table contrasts the results obtained using a compound and an additive model:

Something to note in the table above is that in the opportunity side, the maximum cost reduction using the compound model is less conservative than the additive model.

As seen here, coding this type of model is not difficult. My suggestion is that when you have multiple occurrences of risk events you explore better alternatives than the additive model.