Please formulate a model first and solve it using Excel Solver.
Thanks!
Problem 3 (35 points)
In the year 2525, Mars is inhabited by humans who live in the Headquarters. Water is a very expensive resource. The latest drilling expeditions have discovered water in two remote locations. Water Location 1 and Water Location 2. We assume that the terrain of Mars is a 2-dimensional plane, which means that every location is defined by its x and y coordinate. The coordinates of the Headquarters and the two water locations are given in table below. A unit change in one coordinate represents a one mile distance.
X-coordinate 230 50 30
Y-coordinate
Headquarters Water Location 1 Water Location 2
70 150 40
The settlers are planning to install a pipeline to carry water from the two locations to the Headquarters. However installing the pipeline on Mars' rocky surface is a very expensive undertaking; therefore the amount of pipeline required should be minimized. The task is assigned to two analysts.
(a) (5 points) Since the shortest distance between two points is a straight line, the first analyst proposes that a separate pipe should be run from each Water Location to the Headquarters. In the year 2525, the distance between two points P,,P, with coordinates (x,y),(x2,y2) is still given by the same old formula: distance(P,P2)=V(x1-x2)2+ (y1-y2)2. Compute the number of miles of pipes needed to implement the first analyst's solution.
(b) (30 points) The second analyst has a different idea: build an Intermediate Substation and run pipes from each Water Location to the Intermediate Substation, and then from the Intermediate Substation to the Headquarters. Assume that building the Substation is costless. Come up with a nonlinear optimization problem to determine where to build the Substation.
