The figure below shows the location on a roof where high, medium, and low pressure polyethylene chlorothene lines are routed from a pump to drop points through the roof, for use in the building. Each drop point is fixed at the coordinate given in the table below. The pump itself can be located anywhere on the roof. The lines leading to drop points 1, 2, and 6 are high pressure lines, and cost $13/foot. Medium pressure lines are required at drop points 3 and 4, and cost $8/foot. Drop point 5 requires only a low pressure line costing $2/foot.

High pressure lines come in 20 foot lengths with $80 joints, medium pressure lines come in 40 foot lengths with $50 joints, and low pressure lines come in 50 foot lengths with joints which cost $40 each. Each pipe requires a joint on each end, as well as those needed to connect the individual lengths of pipe from the pump to the drop. Thus a 60 foot long high pressure line will require 4 joints, and a 63 foot long high pressure line will require 5 joints. The pipe vender will sell you any length you need. So if you need 63.6 feet, they will sell you that exact length.

New safety regulations require the addition of special reinforcing pipes at each point of entry through the roof. These “cans” are 6″ x-strong pipe, 3 feet long of A53 Grade B steel and cost $450/foot.

Determine the optimum location for the pump to minimize the cost of the project. Solve it using Excel. Set the solution up in a general fashion – i.e. use a table of pipe/joint properties (cost/foot, cost/joint, lengths available, etc.) such that entry changes in the table will be reflected in the final solution for cost. No formal engineering report is required. Also, since you handed in a hand solution to this problem earlier, no hand solution is required with this solution. Submit only a print out of your final answer.

Hints:

- You may need the truncate function =trunc(cell location)
- If you look under Excel’s “Tools” you will find a “Solver” which will let you optimize functions.
- See note below.

Drop |
Y Coordinate |
X Coordinate |
Line |
Pipe |
Manufactured |
Cost of |
Cost of Reinforcing Cans| $/Each |

1 |
0 |
0 |
High |
$13 |
20 | $80 | $1350 |

2 |
800 |
0 |
High |
$13 |
20 | $80 | $1350 |

3 |
0 |
300 |
Medium |
$8 |
40 | $50 | $1350 |

4 |
400 |
350 |
Medium |
$8 |
40 | $50 | $1350 |

5 |
800 |
550 |
Low |
$2 |
50 | $40 | $1350 |

6 |
150 |
650 |
High |
$13 |
20 | $80 | $1350 |

Note to students:

Well, that’s really strange.

On some of your spreadsheets, if you initially put in 7,7 or 40,40 for the initial pump location, it gets stuck close to that spot. Evidently he just cannot see that 10 or 15 feet down the road things get cheaper, and so he thinks he has the problem solved. So, as with any software, beware, and fully understand the complexities and idiosyncrasies in the program. In industry, I would suggest that you put in 7,7 and find an answer of $39,000?, then put in 100,100 and get an answer of $29,000? and put in 200,200 and get an answer of $29,000?

I.e. move the initial pump location around on the roof to various locations and let him seek optimum locations from different starting points.

Please note that this entire problem is because of the “jump” in costs due to the joints, i.e. a move of only 1 inch may force him to add an $80 joint to the cost. If you will put all the joints = $0, the problem disappears, because moving the pump 1″ to the right or down no longer “jumps” the price by $80.