Correlation_using_look-up_tables

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.