Spreadsheets and risk simulation

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Most estimators use spreadsheets as a matter of routine. Spreadsheets provide:

• good presentation facilities;

• flexibility and adaptability for different tasks and different projects;



Potential Risk








Note: This is to be used in conjunction with standard estimating worksheets.

Figure 20.10—Estimating risk worksheet

Note: This is to be used in conjunction with standard estimating worksheets.

Figure 20.10—Estimating risk worksheet

• ease of modification as new information becomes available;

• backup facilities; and

• ability to incorporate risk and uncertainty.

For risk purposes, the ability to incorporate distributional information in the spreadsheet and evaluate the net effect of multiple distributions are the main features of interest.

Uncertainties can be included in estimating spreadsheets, with the accuracy set as a standard parameter in the absence of more precise information. An example of this was shown in Figure 20.9, where cell C5 contained a variation parameter.

Dependence should also be included in the spreadsheet, with high positive dependence assumed for early estimates in the absence of more precise information. Using @Risk, the level of dependence may also be set as a parameter.

If uncertainty distributions and dependence linkages are built into the estimating spreadsheet in this manner, the risk software can be used to simulate the uncertainty in the total cost. The total cost uncertainty, or the uncertainty for any sub-total, can be displayed graphically. The graphs can be used to assess the appropriate contingency allowance for the cost as a whole, taking into account the individual uncertainties and linkages.

The results from the risk simulation can be compared with the contingency assessed by traditional methods. The sensitivity of the estimate to key assumptions can be tested.

The process of estimating need not change much with the inclusion of risk and uncertainty information. For most organizations with a sound process of cost-estimating, no fundamental changes are needed although additional information may have to be gathered or examined more formally during the estimating process.

Estimators already think about risk and uncertainty, but they do not always do so explicitly. Recording the assumptions about uncertainty built into the estimate is an important first step towards doing so. If spreadsheets are used for estimating, then information about uncertainty in estimates can be included in the process without difficulty.

Was this article helpful?

0 0

Post a comment