An example of a Monte Carlo simulation risk analysis model for Statistical Modeling

This simple model considers the uncertainty in the price of the key cost elements to arrive at a total cost for the finished house construction.

Technical difficulty: 1

Techniques used: Monte Carlo simulation in Excel, PERT distribution

ModelRisk functions used: VosePERT, VoseInput, VoseOutput

Model description

The risk analysis model is built using Excel with our Monte Carlo simulation add-in called ModelRisk. It uses the following formatting convention:

This example models the cost of nine items (from the purchase of land to fixtures & fittings). For each cost item, the most likely (Best Guess) cost appears in the left column (in $000). The Low % and High % columns estimate how much the actual cost may differ below or above the most likely estimate. The column Actual simulates a variation around that Best Guess using a PERT distribution.

At the bottom of the table, two outputs are calculated: the total price for these uncertain cost items; and the difference from the fixed cost estimate (the sum of the Best Guess values).

Results

The model takes about 2 seconds to run 5,000 samples. It is set up to directly show five reports within the ModelRisk ResultsViewer at the end of the simulation, which are described below.

The first tab shows a histogram plot of the probability distribution of the total cost:

One can see, for example, that the most likely cost is about $400k. The second tab shows the cumulative distribution for the total cost:

One can see, for example, that the cost has a 95% chance of being less than $410k, and that there is a 20% chance it will be less than $396k (the sum of most likely values), so 80% chance it will be more. The cumulative plot is great for setting a realistic budget.

The third tab compares the uncertainty in all the cost items (with the purchase of land excluded as that price is fixed):

The fourth tab looks at how much each cost item contributes to the total cost uncertainty – the longer the bar, the more risk that cost item adds:

For example, roofing is contributing about $14k of uncertainty ($409k - $395k, the positions of each end of the bar). The tornado sensitivity plot is great at showing where to focus to try to reduce overall uncertainty. The last tab shows the difference between the risked cost and the best guess. This shows that if one budgeted according to the sum of best guess costs there’s a good chance (around 80%) that you’d have gone over budget.