Your First Model

 

ModelRisk is a risk analysis and Moonte Carlo simulation add-in for Excel. This topic is aimed at the risk analysis novice and introduces the very basics of building a Monte Carlo simulation model to get you started.

We begin with the following spreadsheet model for the cost of building a house. The finished model can be downloaded here. Column C contains your best guess at how much each element of the project might cost, summing to a total of $396,000 in Cell C13.

However, these are just best guesses and the actual cost could be higher or low. For example, you might already have agreed purchase of the land, so the price is known, but the cost of laying the foundations might be up to 10% lower, or 25% higher. We can build a couple of extra columns showing the percentage range:

Add distributions

In another column we now add ModelRisk functions that will generate random values around those ranges with a most likely value of 100% by clicking the Select Distribution button:

This opens up a dialog in which we can chose from a very wide range of distributions. In this case, the Subjective group of distributions is most appropriate because these are subjective estimates:

 

The most common choices would be a PERT or Triangle distribution because they are defined by their minimum, mode (most likely) and maximum values – the information that we have in this model. We’ll pick both by using CTRL-click and then OK.

ModelRisk plots these two distributions together. We can link each distribution’s parameter values to cells in Excel:

Let’s say that the Triangle distribution better reflects your opinion because it gives more probability to the right hand side of the range. Select the Triangle (by clicking on its name, highlighted here in pink) and then click on to insert the Triangle distribution into the correct model cell. There are several options available at this point:

‘Distribution’ is the most commonly used, which will insert a function in Excel that will randomly generate values from this distribution. Cell F4 (the selected location) now displays a VoseTriangle distribution with minimum, mode and maximum values of 90% (D4), 100%, and 125% (E4) respectively.

Define inputs

We will name this cell as an input distribution to the model by clicking on the Output/Input button:

 

The following dialog box appears:

Here we select Cell B4 for the Name field, select Input rather than Output, and click OK. The cell formula has now changed to include a VoseInput function. This function does not alter the calculation in any ways, but is useful in a later stage discussed below.

We can now copy this formula through the rest of the column:

Next, we write a new formula to calculate the total project cost with these random variations from the most likely values. In this case, we will use Excel’s SUMPRODUCT function:

Define outputs

Finally, since this is the focus of our problem, we name the cell as a ModelRisk output – using the same Outputs/Inputs dialog as before but now selecting the Output rather than Input option. The final formula in cell F13 now becomes:

=VoseOutput(D13)+SUMPRODUCT(C3:C11,F3:F11)

The model is finished. Now it is time to analyze what it can tell us.

Run the model

In order to understand how much uncertainty there is in the total cost of the project we need to run a Monte Carlo simulation – which results in a large set of probabilistically weighted ‘what-if’ scenarios by picking different random values from each of the model’s distributions and calculating the total cost each time.

To run a simulation in ModelRisk, simply select the number of samples to run in the ribbon dialog (in the screenshot above it is set to 100, which we’ll change to 50,000) and then click on:

ModelRisk will then run 50,000 Monte Carlo ‘samples’, which takes about 14 seconds.

View the results

When the simulation has finished, ModelRisk will open the Results Viewer window:

On the left is a list of the named outputs and inputs of the model (i.e. those cells containing a VoseOutput or VoseInput function). On the right is a graph of the output (total cost) and at the bottom a list of pages. One can add more pages by clicking the right-most tab.

The original $396,000 estimate based on adding the best guess values is quite far to the left, meaning that there is a high probability of the project costing more. We can see what that probability is, by moving the sliders, and also find a more realistic budget by clicking the  icon above the graph which opens the following dialog:

Here, we have entered the original $396,000 value and asked for a budget for which there is a 90% probability the actual cost will fall below. Click OK, and the sliders move to reflect these changes:

It shows that, given the assumptions made earlier, there is only about a 6% chance of falling below the original estimate, and that there is only 10% probability of exceeding a more conservative budget of $415,000.

Sensitivity analysis

The histogram plot shows that the total cost might lie anywhere between around $390,000 and $425,000. You might well be interested in knowing which of the costs is driving this uncertainty, which is the purpose of performing a sensitivity analysis. ModelRisk offers many variations on sensitivity analysis because it is a very important component of risk-based decision making. We’ll look at just two here.

The first type of sensitivity analysis is a tornado chart, which ModelRisk will generate by clicking this icon:

resulting in the following plot:

This plot shows the sensitivity of the 90th percentile of the total cost distribution to each input distribution. It shows that roofing costs drive the project cost uncertainty the most. If the roofing cost is low, the project cost’s 90th percentile is around $404,000, and if the roofing cost is high, the project cost’s 90th percentile is around $422,000 – a wider range than for any other input variable.

The second type of sensitivity analysis is called a Spider Plot, which ModelRisk will generate by clicking this icon:

resulting in the following plot:

This plot gives more detailed information than the Tornado Plot. Here we are looking at the sensitivity to each input of the mean total cost (the mean is the ‘balance point’ of the histogram distribution, we could also look at a percentile or other statistical attributes). Again, it shows that roofing costs are dominant because it gives the greatest vertical range. In this problem, we are dealing with costs so there is a linear relationship between the inputs and the output, reflected in lines that increase from bottom left to top right in the plot, but in more involved problems a spider plot can reveal more complex relationships.

From analysis to decision

The analysis clearly provides some important information for a decision maker:

1. The budget should be set closer to $420,000 to be reasonably sure of having the cash available to complete the project

2. It is probably worth investigating whether it is possible to reduce the uncertainty on roofing costs (as well as the wall construction and laying the foundation) because these will firm up the cost estimate considerably.

Next steps in learning to use ModelRisk and risk analysis

ModelRisk has a very extensive range of risk analysis tools for you to explore. For example, in the model described in this document, perhaps the major driver behind the roofing and wall construction uncertainty is the competence of the contractor – and the same contractor is doing both parts of the project. That means that if the contractor turns out to be incompetent it will affect both parts of the project adversely – in other words, there is a correlation between these two input variables that needs to be described because it will increase the uncertainty of the total cost estimate. ModelRisk offers a range of correlation tools to build correlation relationships.

You might have a lot of data you wish to use to support your risk analysis. ModelRisk offers advanced yet user-friendly tools for fitting distributions, correlations, and time series – as well as a range of features to statistically and visually explore your data.

ModelRisk also comes with a very extensive help file that you can browse and search through. There are a wide variety of example models you can work through too. Vose Software (www.vosesoftware.com) and our reseller network also provide in-house and public training courses in building risk analysis models – and using them to make decisions. The courses are written and presented by professional risk analysts, so while you learn to use ModelRisk you will also benefit from the real world experience of a seasoned risk analyst.

You can also download this topic in PDF format.

 

ModelRisk

Monte Carlo simulation in Excel. Learn more

Tamara

Adding risk and uncertainty to your project schedule. Learn more

Navigation

FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download ModelRisk Basic now

FREE PROJECT RISK SOFTWARE

For Primavera & Microsoft Project

Download your free copy of Tamara Basic today. Professional quality project risk software and no catches.

Download Tamara Basic now
-->