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.
The model Correlation using look-up tables illustrates the example above, showing the values for one particular iteration.
The links to the Correlation using look-up tables software specific models are provided here:
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.
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.
