To learn more about EpiX Analytics' work, please visit our modeling applications, white papers, and training schedule.

Page tree

 

 

Your company has been invited to bid for a project to design and construct a line of 16 electricity pylons. Estimates are based on PERT distributions (using absolute minimum, most likely and absolute maximum) unless indicated.

 

Design

Your designers estimate the following:

 

The design work could be done in-house. However, if you are awarded an upcoming contract in France that your company has bid for, you will not have the resources to design this job and will have to sub-contract out.

 

 

Minimum

Most Likely

Maximum

In-house design cost:

Ј150 000

Ј165 000

Ј190 000

Sub-contracted costs:

Ј180 000

Ј200 000

Ј235 000

Probability of French job:

 

40%

 

 

Foundation construction

 

Your foundation engineers estimate the following:

 

 

Minimum

Most Likely

Maximum

Material costs/pylon:

Ј6 200

Ј7 000

Ј7 900

Man hours/pylon:

Normal(600,80) dependent on individual ground conditions at pylon sites

Cost/man hour

 

Ј7.50

 

Total plant cost:

Ј250 000

Ј300 000

Ј380 000

 

Pylon construction

 

Your steel work engineers estimate the following:

 

 

Minimum

Most Likely

Maximum

Steel cost/pylon at construction time:

Ј52 000

Ј54 000

Ј58 500

Man hours/pylon (dependent on final pylon design)

200

240

270

Cost/man hour

 

Ј13.00

 

 

Time constraints

 

The project's components are estimated to take the following time (weeks):

 

 

Minimum

Most Likely

Maximum

Design:

10

12

15

Foundations:

14

16

21

Time from foundation completion to last pylon completed:

4

4.5

5.5

 

The bid document has a clause specifying a 35 week completion time with a penalty of Ј25 000 per week or part thereof for overrunning.

 

The objectives of the model are:

 

  1. Produce distributions of the total project cost and duration

  2. Use a scatter plot to illustrate the relationship between the project's cost and duration. Plot iterations above and below 35 weeks separately on the same graph and fit regression lines through the two data sets.

  3. Redo the model but, to make it more realistic,  make the design time and cost 50% correlated and the time to do the foundations 40% correlated with the associated plant cost and 50% correlated with the total foundation man hours. What is the effect of including these correlations?

 

The solution to the first two questions is provided in the model Pylons, and the solution to the third question is provided in the model Pylons Correlated

The links to the Pylons & Pylons Correlated software specific models are provided here:

  Pylons

 

There are two particular points to notice in this model:

 

  1. Central limit theorem is applied in cell M22. It takes a varied amount of time to construct the foundations, so one can be lower and another one can be high. Thus you cannot model the total amount of time as: y = pylons * Normal(600 , 80), because that would assume the same amount of time to construct each of the foundations. Instead, the following formula should be used: y = Normal(pylons*600,(pylons^0.5)*80)
    More information concerning this topic can be reached here: Central Limit Theorem.

  2. Cell M17 uses a Binomial distribution and an IF-function to return values from "In-house" and "Sub-contracted" with corresponding probabilities. A common mistake here is to multiply the values by their probabilities and then take the sum : y = In_house*60%+Sub_contracted*40%. This is incorrect because it is taking fractions of both scenarios and adding them together, where in reality either one or the other scenario must occur. This would result in the decrease of the spread in the final outcome and underestimation of the risk arising from that particular risk factor.

In order to construct the scatter plot for question 2, we need to export our output data from Crystal Ball into the spreadsheet. Sorting it according to the total duration, we plot the two columns against each other to get the following scatter plot:

 

 

Dots in blue show the values for under 35 weeks (no penalties), while dots in red show the values with penalties.

If we add linear trend lines to these two data sets we can see that the second data set (in red) has a much higher correlation, a slope of about zero (in fact, even a slightly negative slow) under 35 weeks, and a slope of about Ј24,300/week after 35 weeks.

 

 Pylons_Correlated

As for question 3, plotting the output data the same way as for question 2, we get:

 

 

Notice that the correlation has increased for both data sets due to correlations between time and costs of different stages.

  Pylons (The outputs are located in cells I19 and I20)

 

There are two particular points to notice in this model:

 

  1. Central limit theorem is applied in cell F23. It takes a varied amount of time to construct the foundations, so one can be lower and another one can be high. Thus you cannot model the total amount of time as: y = pylons * RiskNormal(600 , 80), because that would assume the same amount of time to construct each of the foundations. Instead, the following formula should be used: y = RiskNormal(pylons*600,(pylons^0.5)*80)
    More information concerning this topic can be reached here: Central Limit Theorem.

     

  2. Cell F19 uses a RiskDiscrete function to return values from "In-house" and "Sub-contracted" with corresponding probabilities. A common mistake here is to multiply the values by their probabilities and then take the sum : y = In_house*60%+Sub_contracted*40%. This is incorrect because it is taking fractions of both scenarios and adding them together, where in reality either one or the other scenario must occur. This would result in the decrease of the spread in the final outcome and underestimation of the risk arising from that particular risk factor. The correct way to model this variable is:  y = RiskDiscrete ({ In_house, Sub_contracted}, {60%,40%})

In order to construct the scatter plot for question 2, we need to export our output data from @RISK into the spreadsheet. Sorting it according to the total duration, we plot the two columns against each other to get the following scatter plot:

 

 

Dots in blue show the values for under 35 weeks (no penalties), while dots in red show the values with penalties.

If we add linear trend lines to these two data sets we can see that the second data set (in red) has a much higher correlation, a slope of about zero under 35 weeks, and a slope of about Ј25,000/week after 35 weeks.

 

 Pylons_Correlated

As for question 3, plotting the output data the same way as for question 2, we get:

 

 

Notice that the correlation has increased for both data sets due to correlations between time and costs of different stages.

 

 

More on modeling correlations is provided here.

 

 


  • No labels