The Arctic Oil Company has recently drilled two new wells in a remote area of Al

Question

The Arctic Oil Company has recently drilled two new wells in a remote area of Alaska. The company is planning to install a pipeline to carry the oil from the two new wells to a transportation and refining (T&R) center. The locations of the oil wells and the T&R center are summarized in the following table. Assume a unit change in either coordinate represents 1 mile.
X Coordinate Y Coordinate
Oil Well 1 50 150
Oil Well 2 30 40
T&R Center 230 70

Installing the pipeline is a very expensive undertaking, and the company wants to minimize the amount of pipeline required. Because the shortest distance between two points is a straight line, one of the analysts assigned to the project believes that a separate pipe should be run from each well to the T&R Center. Another alternative is to run separate pipes from each well to some intermediate substation where the two lines are joined into a single pipeline that continues on to the T&R center. Arctic Oil's Management wants to determine which alternative is best. Furthermore, if using the intermediate substation is best, management wants to determine where this station should be located.
a. Create a spreadsheet model to determine how many miles of pipeline Arctic Oil must install if it runs separate pipelines from each oil well to the T&R center. How much pipe will be needed? Use Solver.
b.If Arctic Oil wants to build a substation, where should it be built? How much pipe is needed for this solution? Use Solver.
c. Which alternaive is best?
d. Suppose the substation cannot be built within a 10-mile radius of the coordinates X=80, Y=95. (Assume the pipeline can run through this area, but the substation cannot be built in this area.) What is the optimal location of the substation now, and how much pipe will be needed? Use Solver.

Explanation / Answer

For a) Solver is not relevant. You have 2 pipelines straight to T & R.
The distance is ((230-50)^2+(70-150)^2) + ((230-30)^2+(70-40)^2) = 196.977156035922+202.237484161567=b) We have two pipes going from the oil wells to (x,y) and one pipe from (x,y) to the T&R center.

We are minimizing over x and y

((x-50)^2+(x-150)^2) + ((x-30)^2+(x-40399.214640197489)^2) + ((230-x)^2+(70-x)^2)

Using Solver, this is minimized at

This is minimized at (72.3712071004968, 93.3851510411929) , where

((x-50)^2+(x-150)^2) + ((x-30)^2+(x-40)^2) + ((230-x)^2+(70-x)^2) =

60.8745597911136+68.1563903302116+159.354013567823 = 288.384963689148

c) Alternative b is clearly better (it couldn't be worse, as the trivial solution would be locating the point at the T&R Center, which would give the same distance).

288.384963689148 < 399.214640197489, so this is the solution.

d) We add the constraint (x-80)^2 + (Y-95)^2 >= 100, so that we are not within 10 miles of (80, 95)

Note that, for the point in b,  (x-80)^2 + (Y-95)^2 =  

(72.3712071004968-80)^2 + (93.3851510411929-95)^2 = 60.8062182632709 < 100, so the constraint is active.

(60.8062182632709=7.79783420337153 is the distance, so it is within 10 miles)

I show the Excel file here

The solution is (70.3716189872678, 92.2992077906233)

The sum of the distances is 288.438480621524

So that you can create these two solutions (unconstrained and constrained) - solution to b and d)

as well, see the table below.

To orient you, the word Location is in cell A1.

Then, the equations are

D2 =SQRT((B$5-B2)^2+(C$5-C2)^2)

D3 = =SQRT((B$5-B3)^2+(C$5-C3)^2)

D4 = =SQRT((B$5-B4)^2+(C$5-C4)^2)

D5 = SUM(D2:D4)

B6 = (B5-80)^2 + (C5-95)^2

In the solver (this is in the data menu)

With no constraints, we set target cell D5 equal to min by changing cells B5:C5

With constraints, we set target cell D5 equal to min by changing cells B5:C5 subject to the constraint B6 >= 100

Location x y     Oil Well 1 50 150 61.19137 Oil Well 2 30 40 66.06871 T&R Center 230 70 161.1784 New Point 70.37162 92.29921 288.4385 Constraint 100

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.