Learning Activity — Facility Location by the Gravity Method
Latitude
City
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
(LAT)
16.6
16.7
16.8
17.0
17.0
17.2
17.5
17.4
17.5
17.8
17.9
18.0
18.4
18.9
19.3
19.4
19.4
19.9
20.3
21.2
21.6
22.6
23.4
24.0
25
24.9
Longitude
Population (P) (in
(LON)
1,000s)
154.8
1,655
156.8
1,300
153.2
1,601
154
2,385
152
2,230
144.9
665
155.7
1,664
147.1
885
141.1
3,116
155.1
1,636
153.8
3,200
144.6
148
142.4
854
156.8
5,473
148.3
615
152.9
1,145
142.8
627
143.7
542
152.5
379
143.7
964
155.6
1,546
140.1
2,706
155.8
1,727
144.4
1,669
146.4
(P*LAT)
(P*LON)
?????? =
2,931
See your solution in the chart below!
138
15.0
16.0
17.0
18.0
19.0
20.0
21.0
22.0
140
142
144
146
148
150
152
154
156
158
???
22.0
23.0
24.0
25.0
Instructions:
1) To start the computations, you’ll need to compute the SUM of the population shown in Column D.
2) Compute each city’s “Population x Latitude” (column E).
3) Compute each city’s “Population x Longitude” (column F).
4) Sum all the P*Lat and P*Lon that will be needed for the final equation (E30 and F30).
5) Now, follow the Gravity Method formula to compute the Latitude and Longitude for your best location.
Note: This will minimize the total travel distance over time from the Distribution Center to all customers!
6) Once you¥ finished with the Gravity Method, check out the tab called `CityŠ?????? =
?? ? ??????
??
?????? =
?? ? ??????
??
Your solution for a single, low-cost location:
Latitude Longitude
“The angular distance of a place north
celestial object north or south of the c
latwtude
degrees and minutes.”
“The angular distance of a place east o
loné5de England, or west of the standard merid
expressed in degrees and minutes.”
158
tion.
mers!
istance of a place north or south of the earth’s equator, or of a
t north or south of the celestial equator, usually expressed in
inutes.”
istance of a place east or west of the meridian at Greenwich,
st of the standard meridian of a celestial object, usually
egrees and minutes.”
Facility Location using Multiple Solutions:
DESCRIPTION: You, need ExcelàSOLVER again, but this is all
Total Allowed number of Cities for your distribution centers into the
minimum cost solution for variable transportation costs represente
green cells. Try 2, 3, 4 or more options for Distribution Centers.
make sense based on the size of each cities and the distances to
Solution
Chicago
Atlanta
New York
St. Louis
Detroit
Cincinnati
Pittsburgh
Charlotte
Boston
TOTAL
Set of Cities
Chicago
Atlanta
New York
St. Louis
Detroit
Cincinnati
Pittsburgh
Charlotte
Boston
Cost to serve
2,267,300,000
505,648,000
341,600,000
553,214,000
Instructions
222,111,000
1) Enter the desired # of facilities (in YELLOW cell)
113,526,000
2) Run SOLVER to find the optimal location(s)! (In GREY cells: 1
466,335,000
3) Save your file with the HIGHEST number of facilties that you tes
133,590,000
4,603,324,000
Purchase answer to see full
attachment