An example of a Monte Carlo simulation risk analysis model for cost modeling

Technical difficulty: 1

Techniques used: Monte Carlo simulation in Excel

Model description

A question often asked is how the budget and contingency set for a project as a whole should be distributed back among the cost items to set budgets for each project component. This knowledge will help the project manager to keep an eye on how the project is progressing. One approach is to distribute back the budget and contingency costs so that the figures associated with each cost item have the same probability of being exceeded. Using this approach will give each cost item the same chance of coming in within its budget figure or its (budget + contingency) figure and will avoid controllers of some cost items being given almost impossible targets to meet and others easy targets. This method of distributing budget and contingency costs among cost items is demonstrated in the following example, using this cost model.

The figure below shows the cumulative distribution of the project's total cost:

The mean of the generated values $303 677 is selected as the budget and the (80 percentile - budget), i.e. $308 836 - $303 677= $5 159 is selected as the risk contingency. The budget is then the cost that the organization will realistically try to achieve or better, and the contingency is the additional amount put aside should the need arise. If the cost risk model is accurate, there is a 54% chance that the budget will be sufficient and an 80% chance that the project's costs will not exceed the budget + contingency.

In order to be able to distribute the budget and contingency back among the cost items, each cost item must be nominated as an output of the model. The generated data points from each cost item are then output to a spreadsheet:



and each column of values is then ranked separately in ascending order. Then, the costs generated for all items in each row are summed to give a total project cost (see sheet Sampled values):



Next, the budget and (budget + contingency) values are looked up in the column of summed costs and the values matched to the target totals. A separate sheet in the model (Automated 1000 samples) shows how this process can be automated using some MdelRisk functions: