Facility Location Problem The X county supervisor's board has decided that a new volunteer fire station and ambulance facility needs to be built in the county. The county has 30 rural towns that need to be served. A 10 X 10 mile grid has been overlaid on the county map and each of the towns assigned a set of X,Y coordinates. The coordinates of each town along with the expected number of annual trips to each are shown in the following table. Assume that the distance between the facility and rural town is a straight line shortest distance. (The use of the GRG Nonlinear Solver will be required to solve the problem, the Min and Max for the X,Y coordinates of the facilities are between 0 and 10.) Town # Trips 35 56 2 The county is considering building one facility. ml 6 1.3 0.1 5 9.8 9.3 9.3 5.5 4.6 5.1 2 47 45 4 5 6 7 Y 3.1 8.3 1.5 9.3 0.2 8.4 2.2 3.5 1.4 0.2 0.9 3.3 A. Formulate the model as a non-linear programming model to answer the following questions. Provide the model and all of the constraints. (Snapshot of Excel Solver dialog box with data referenced is acceptable) 2 2 22 9 10 13 50 20 11 12 B. Where should the county locate the facility in order to minimize the total distance (round-trip) traveled in one year? (X,Y coordinates) Round to the nearest 1/10th, e.g. 4.1 44 5.5 4.6 5.1 2 4.1 1.5 7.9 8.4 4.3 7.6 4.2 1.4 B. Where should the county locate the facility in order to minimize the total distance (round-trip) traveled in one year? (X,Y coordinates) Round to the nearest 1/10th, e.g. (8.1, 2.7). 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 3.5 1.4 0.2 0.9 3.3 8 7.8 5.8 4.5 3 3.3 7.7 6.3 0.7 8 0.3 8.8 8.2 5.9 3.3 2 22 13 50 20 44 72 21 26 28 99 96 14 93 34 80 91 35 C. What is the total distance (round-trip) traveled in one year? Round to the nearest 1/10th. Include decimal, e.g. 27.09 = 27.1, 56.99 = 57.0. 2 9.6 1 6.3 9.7 9.9 D.What is the average round-trip distance for each trip? (Remember, average distance is total distance divided by total number of trips). Round to the nearest 1/10th. (e.g. 4.1) ho E. Draw a map with the location of each town and the location of the facility. (Use scatter plot in MS Excel to draw the map). 0.8 35 96 86 3 40 2.7 5.2 0 9.6 44 9.8 3 A. Keep for your records B. Where should the county locate the facility in order to minimize the total distance traveled in one year? (XY coordinates) Round to the nearest 1/10th, eg. (12.1.83.7) x y C. What is the total distance traveled in one year? Round to the nearest 1/10th. Include decimal, e.g. 27.09 - 27.1. 56.99 - 57.0. Total distance traveled in one year D What is the average one-way distance for each trip? (Remember, average distance is total distance divided by total number of trips), Round to the nearest 1/10th. (eg. 89.1.) Average distance traveled in one year = E. Keep for your records Facility Location Problem The X county supervisor's board has decided that a new volunteer fire station and ambulance facility needs to be built in the county. The county has 30 rural towns that need to be served. A 10 X 10 mile grid has been overlaid on the county map and each of the towns assigned a set of X,Y coordinates. The coordinates of each town along with the expected number of annual trips to each are shown in the following table. Assume that the distance between the facility and rural town is a straight line shortest distance. (The use of the GRG Nonlinear Solver will be required to solve the problem, the Min and Max for the X,Y coordinates of the facilities are between 0 and 10.) Town # Trips 35 56 2 The county is considering building one facility. ml 6 1.3 0.1 5 9.8 9.3 9.3 5.5 4.6 5.1 2 47 45 4 5 6 7 Y 3.1 8.3 1.5 9.3 0.2 8.4 2.2 3.5 1.4 0.2 0.9 3.3 A. Formulate the model as a non-linear programming model to answer the following questions. Provide the model and all of the constraints. (Snapshot of Excel Solver dialog box with data referenced is acceptable) 2 2 22 9 10 13 50 20 11 12 B. Where should the county locate the facility in order to minimize the total distance (round-trip) traveled in one year? (X,Y coordinates) Round to the nearest 1/10th, e.g. 4.1 44 5.5 4.6 5.1 2 4.1 1.5 7.9 8.4 4.3 7.6 4.2 1.4 B. Where should the county locate the facility in order to minimize the total distance (round-trip) traveled in one year? (X,Y coordinates) Round to the nearest 1/10th, e.g. (8.1, 2.7). 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 3.5 1.4 0.2 0.9 3.3 8 7.8 5.8 4.5 3 3.3 7.7 6.3 0.7 8 0.3 8.8 8.2 5.9 3.3 2 22 13 50 20 44 72 21 26 28 99 96 14 93 34 80 91 35 C. What is the total distance (round-trip) traveled in one year? Round to the nearest 1/10th. Include decimal, e.g. 27.09 = 27.1, 56.99 = 57.0. 2 9.6 1 6.3 9.7 9.9 D.What is the average round-trip distance for each trip? (Remember, average distance is total distance divided by total number of trips). Round to the nearest 1/10th. (e.g. 4.1) ho E. Draw a map with the location of each town and the location of the facility. (Use scatter plot in MS Excel to draw the map). 0.8 35 96 86 3 40 2.7 5.2 0 9.6 44 9.8 3 A. Keep for your records B. Where should the county locate the facility in order to minimize the total distance traveled in one year? (XY coordinates) Round to the nearest 1/10th, eg. (12.1.83.7) x y C. What is the total distance traveled in one year? Round to the nearest 1/10th. Include decimal, e.g. 27.09 - 27.1. 56.99 - 57.0. Total distance traveled in one year D What is the average one-way distance for each trip? (Remember, average distance is total distance divided by total number of trips), Round to the nearest 1/10th. (eg. 89.1.) Average distance traveled in one year = E. Keep for your records