Question

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 XY coordinates of the facilities are between 0 and 10.) Town 10 . 9 3.9 5.2 Y 6 .8 9 7. 2. 7 6.8 #Trips 86 5 78 The county is considering building one facility. 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) B. Where should the county locate the facility in order to minimize the total distance (round-trip) traveled in one year? XY coordinates) Round to the nearest 1/10th, e-8, (8.1, 2.7) LLLLLLLLLLLLLLLLLL SUMMOBLOGA 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. 4,6 9.7 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. (er 4.1) cola 5. 61 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). 4.8 1.7 2.3 8.7 6.9

Answer 1

Excel formulation:

A B C D Facility location | Ε Town X 7.9 2 3 0.9 3.9 5.2 4.2 78 4 6.8 3.1 4.8 7 6.8 8 4.7 4.8 6 9.1 2.7 7.8 91.2 10 7.5 11 3.2 12 2.1 13 9.5 14 5.7 15 4.7 16 6.3 17 5.3 18 5.5 Y # trips Unit distance (one-way) 6.8 =SQRT((C4-$C$2)^2+(D4-$D$2)^2) =SQRT{(C5-$C$2)^2+(D5-$D$2)2) 2.7 =SQRT{(C6-$C$2)^2+(D6-$D$2)2) =SQRT{(C7-$C$2) 2+(D7-$D$22) =SQRT((C8-$C$2) 2+(D8-$D$22) 5.4 =SQRT((C9-$C$2) 2+(D9-$D$2) 2) =SQRT((C10-$C$2)^2+(D10-$D$2/2) 7.1 =SQRT((C11-$C$2)^2+(D11-$D$2/2) =SQRT((C12-$C$2)^2+(D12-$D$2/2) =SQRT((C13-$C$2)^2+(D13-$D$2/2) =SQRT((C14-$C$2)^2+(D14-$D$2/2) =SQRT((C15-$C$2)^2+(D15-$D$2/2) =SQRT((C16-$C$2)^2+(D16-$D$22) =SQRT{(C17-$C$2)^2+(D17-$D$22) =SQRT{(C18-$C$2)^2+(D18-$D$22) =SQRT((C19-$C$2)^2+(D19-$D$2/2) 8.8 =SQRT{(C20-$C$2)2+(D20-$D$2) 2) =SQRT{(C21-$C$2y^2+(D21-$D$2/2) =SQRT{(C22-$C$2)^2+(D22-$D$22) =SQRT(C23-$C$2)^2+(D23-$D$22) =SQRT((C24-$C$2)^2+(D24-$D$22) =SQRT{(C25-$C$2)^2+(D25-$D$22) =SQRT(C26-$C$2)^2+(D26-$D$22) =SQRT(C27-$C$2)^2+(D27-$D$22) =SQRT(C28-$C$2)^2+(D28-$D$22) =SQRT(C29-$C$2)^2+(D29-$D$2)^2) 4.8 59 =SQRT((C30-$C$2)^2+(D30-$D$2/2) 3. 4 9 =SQRT((C31-$C$2)^2+(D31-$D$2/2) 35 =SQRT{(C32-$C$2) 2+(D32-$D$2,-2) 6.9 =SQRT(C33-$C$2y^2+(D33-$D$2y^2) Total distance =SUMPRODUCT($E$4:$E$33,F4:F33) One-way Unit distance (two-way) =2*F4 =2*F5 =2*F6 =2*F7 =2*F8 =2*F9 =2*F10 =2*F11 =2*F12 =2*F13 =2*F14 2*F15 =2*F16 =2*F17 =2*F18 =2*F19 =2*F20 =2*F21 =2*F22 =2*F23 =2*F24 =2*F25 =2*F26 =2*F27 =2*F28 =2*F29 = 2*F30 = 2*F31 =2*F32 =2*F33 =SUMPRODUCT($E$4:$E$33, G4 G33) Two-way 2.2 4.3 4.6 20 21 2.7 0.5 7.3 0.6 SKAN 5.6 28 29 30 8 1.7 2.3 8.7

Solver inputs:

Set Objective: $G$34 TO: O Max Min value of: 0 By Changing Variable Cells: $C$2:$D$2 Subject to the Constraints: $C$2:$D$2 <= 10 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Solution:

	Facility location
	4.8	4.3
Town	X	Y	# trips	Unit distance (one-way)	Unit distance (two-way)
1	0.9	6.8	86	4.673	9.346
2	3.9	7.9	5	3.752	7.505
3	5.2	2.7	78	1.608	3.215
4	4.2	6.8	28	2.613	5.225
5	4.8	3.1	29	1.163	2.327
6	9.1	5.4	60	4.424	8.849
7	2.7	6.8	39	3.309	6.618
8	7.8	7.1	59	4.111	8.223
9	1.2	4.7	37	3.650	7.301
10	7.5	4.8	13	2.729	5.458
11	3.2	2.4	28	2.472	4.943
12	2.1	9.3	26	5.726	11.453
13	9.5	2.4	4	5.033	10.067
14	5.7	3.2	47	1.377	2.755
15	4.7	0.6	78	3.665	7.330
16	6.3	6.8	66	2.935	5.870
17	5.3	8.8	70	4.562	9.123
18	5.5	2.2	10	2.171	4.342
19	4.3	4.2	72	0.528	1.056
20	4.6	9.7	23	5.441	10.883
21	2.7	1.1	40	3.810	7.620
22	0.5	7.3	23	5.284	10.568
23	7.3	1.9	21	3.423	6.845
24	3.3	0.6	60	3.968	7.935
25	9	6.7	9	4.835	9.670
26	5.6	1	13	3.354	6.708
27	8	4.8	59	3.221	6.442
28	1.7	3.4	9	3.241	6.482
29	2.3	1	35	4.125	8.251
30	8.7	6.9	60	4.688	9.375
		Total distance		4063.132	8126.265
				One-way	Two-way

So,

The facility location is (4.8, 4.3)

Total distance (one-way) = 4063.1 and Total distance (two-way) = 8126.3

Total no. of trips = 1187

Average distance (one-way) = 4063.1 / 1187 = 3.4 and Average distance (two-way) = 8126.3 / 1187 = 6.9

Facility ОО ооо 1 2 3 4 5 6 7 8 9 10