Lookup tables

See also: Modeling correlation introduction

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 effected by the weather to varying degrees.

A simple method of modeling such a scenario is to use a spreadsheet look-up table.

Spreadsheet model   Correlation using look-up tables illustrates the example above, showing the values for one particular iteration. The model works as follows:

• Cells D11:D16 list the estimates of duration of each activity if the weather is normal.

• The lookup table F11:J16 lists the percentages that the activities will increase or decrease due to the weather conditions.

• Cell D19 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 E11:E16 add the appropriate percentage change for that iteration to the base estimate time by looking it up in the lookup table.

• Cell E17 adds up all the revised durations to obtain the total construction time.

It is a simple matter to include uncertainty in this technique. One needs simply to add uncertainty distributions for the magnitude of effect (in this case, the values in Cells F11:J16).  A little care is needed if the uncertainty distributions overlap for an activity. So, for example, if we used a PERT(30%,40%,50%) uncertainty distribution for the parameter in Cell F11 and a PERT(20%,28%,35%) uncertainty distribution for the parameter in Cell G11, we could be modeling a simulation where Very Poor weather increases the Archaeological digging time by 31% but Poor weather increases the time by 33%. Using high levels of correlation for the uncertainty distributions of effect size across a task will remove this problem quite efficiently and reflect that errors in estimating the (in this case weather) effect will probably be similar for each effect size.

Read on: Conditional logic



Monte Carlo simulation in Excel. Learn more

Spreadsheet risk analysis modeling


Adding risk and uncertainty to your project schedule. Learn more

Project risk analysis


Enterprise Risk Management software (ERM)

Learn more about our enterprise risk analysis management software tool, Pelican

Enterprise risk management software introduction


For Microsoft Excel

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

Download ModelRisk Basic now


For Primavera & Microsoft Project

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

Download Tamara Basic now