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, 3, and 5 are high pressure lines, and cost $13/foot. Medium pressure lines are required at drop points 2 and 4, and cost $8/foot. Drop point 6 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, he will sell you that exact length.
Determine the optimum location for the pump, which will minimize the cost of the lines and joints leading from the pump to the 6 drop points.
Solve this problem 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 |
|
1 |
0 |
0 |
High |
$13 |
20 | $80 |
|
2 |
800 |
0 |
Medium |
$8 |
40 | $50 |
|
3 |
0 |
300 |
High |
$13 |
20 | $80 |
|
4 |
400 |
350 |
Medium |
$8 |
40 | $50 |
|
5 |
800 |
550 |
High |
$13 |
20 | $80 |
|
6 |
150 |
650 |
Low |
$2 |
50 | $40 |
Note to students: Same note as in Problem 4.2. Also, if you set this problem up in a general form, this change in parameters will be very simple. If you hard-coded everything into the equations, it will not be as simple.