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:
Produce distributions of the total project cost and duration
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.
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:
There are two particular points to notice in this model:
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.- 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.
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:
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.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.
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.
