Vertex42 The Excel Nexus

The risk analysis package @Risk provides a convenient way of performing quantitative risk analysis calculations. @Risk is a simulation package that embeds within a standard Excel spreadsheet. @Risk has several useful features.

• It allows distributions and probability trees like the examples shown in Figure 20.5, Figure 20.6 and Figure 20.7 to be specified and incorporated in an estimating spreadsheet.

• It allows simulations to be run, taking samples from the input distributions and generating output distributions for the cost totals of interest.

• It facilitates graphical display of output distributions and allows sensitivity analyses to be performed. Figure 20.8 was generated using an @Risk simulation.

A ; B |
C |
D |
E | ||

5 |
Uncertainty (VAR) |
0.25 | |||

12 |
Num. |
Item |
Base cost |
Var Dist |
Expected |

17 |
2.3.2.1 |
Valve Supply |
$300,000 |
1 |
$300,000 |

Figure 20.9—@Risk representation of the uncertainty in valve supply cost

Figure 20.9—@Risk representation of the uncertainty in valve supply cost

As an example, Figure 20.9 shows how the distribution for valve supply, Element 2.3.2.1 in the irrigation case study, was represented in an Excel spreadsheet using @Risk. The element valve supply has a base cost estimate of $300 000. The @Risk function RiskTriang was used in cell D17 to represent a triangular variation distribution, with a most likely value at the estimate and a range from 0.75 to 1.25, i.e. a range of plus or minus 25% specified in cell C5 named VAR. The expected value of the cost distribution is displayed on the screen in cell D17. The distribution for the valve supply cost is in cell E17.

@Risk can also incorporate sophisticated dependence links if required. If software like @Risk is required, project and estimating staff are advised to get expert help in the first instance.

Was this article helpful?

## Post a comment