An example of a Monte Carlo simulation risk analysis model for correlation

Technical difficulty: 2

Techniques used: Monte Carlo simulation in Excel

ModelRisk functions used: VoseDiscrete

Model description

There may be times when it is necessary to model the simultaneous effect of an external factor on several parameters within a model. An example is the effect of poor weather on a construction site. The times taken to do an archaeological survey of the land, dig out the foundations, put in the form work, build the foundations, construct the walls and floors and assemble the roof could all be affected by the weather to varying degrees.

A simple method of modeling such a scenario is to use a spreadsheet look-up table, as shown with this example. The model works as follows:

• Cells D14:D19 list the estimates of duration of each activity if the weather is normal.
• The lookup table F14:J19 lists the percentages that the activities will increase or decrease due to the weather conditions.
• Cell D22 generates a value for the weather from 1 to 5 using a Discrete distribution that reflects the relative likelihood of the various weather conditions.
• Cells E14:E19 add the appropriate percentage change for that iteration to the base estimate time by looking it up in the lookup table.
• Cell E20 adds up all the revised durations to obtain the total construction time.