compute the lowest cost alternative for a single facility (Gravity Method) and multiple facilities (linear programming).

Gravity

2

>

Method

Learning Activity — Facility Location by the

Gravity Method

Instructions

: Latitude Longitude 1

) To start the computations, you’ll need to compute the SUM of the population shown in Column D. City (LAT) (LON) Population (1,

0

00) P*Lat P*Lon 2) Compute each city’s “Population x Latitude” (column E). 1

1

6

.6 1

5 4

.

8 16

55 3

) Compute each city’s “Population x Longitude” (column F). 2

16.

7 15

6.8 23

00 4) Sum all the P*Lat and P*Lon that will be needed for the final equation (E27 and F27). 3

16.8 153.2 601 5) Now, follow the Gravity Method formula to compute the Latitude and Longitude.

4

17

.0 154 13

85

Note: This will minimize the total travel distance from the Distribution Center to each customer!

5 17.0

152 12

30 6

17.2 14

4.

9 665 7

17.5 155.7 664 8

17.4 147.1 885 9 17.5

141.1 11

16 10 17.8 155.1 636 11

17.9 153.8 1

20

0 12

18

.0 144.6 148 13

18.4 142.4 854 14

18.9

156.8

1473 15

19

.3 148.3 615 16

19.4 152.9 1145 17 19.4

142.8 627 18

19.9 143.7 542 19

20.3 152.5 379 20

21

.2

143.7

964 21

21.6 155.6 546 22 22.6 140.1 706 23

23.4 155.8 727 24 24.0 144.4 669 Your solution for a single, low-cost location: 25 24.9 146.4 931

Latitude Longitude
lat·i·tude “The angular distance of a place north or south of the earth’s equator, or of a celestial object north or south of the celestial equator, usually expressed in degrees and minutes.” See your solution in the chart below! lon·gi·tude “The angular distance of a place east or west of the meridian at Greenwich, England, or west of the standard meridian of a celestial object, usually expressed in degrees and minutes.”

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 154.80000000000001 156.80000000000001 153.19999999999999 154 152 144.9 155.69999999999999 147.1 141.1 155.1 153.80000000000001 144.6 142.4 156.80000000000001 148.30000000000001 152.9 142.80000000000001 143.69999999999999 152.5 143.69999999999999 155.6 140.1 155.80000000000001 144.4 146.4 16.600000000000001 16.7 16.8 17 17 17.2 17.5 17.399999999999999 17.5 17.8 17.899999999999999 18 18.399999999999999 18.899999999999999 19.3 19.399999999999999 19.399999999999999 19.899999999999999 20.3 21.2 21.6 22.6 23.4 24 24.9 Capital

9 City

s:

Solution

Instructions

Chicago

0

Atlanta

0

New York

1

St. Louis

0

Detroit

0

Cincinnati

0

Pittsburgh

0

Charlotte

0

Boston

0

1

1

Chicago Atlanta New York St. Louis Detroit Cincinnati Pittsburgh Charlotte Boston

Chicago 0 0 0 0 0 0 0 0 0

Atlanta 0 0 0 0 0 0 0 0 0
New York 1 1 1 1 1 1 1 1 1
St. Louis 0 0 0 0 0 0 0 0 0
Detroit 0 0 0 0 0 0 0 0 0
Cincinnati 0 0 0 0 0 0 0 0 0
Pittsburgh 0 0 0 0 0 0 0 0 0
Charlotte 0 0 0 0 0 0 0 0 0

Boston 0 0 0 0 0 0 0 0 0

1 1 1 1 1 1 1 1 1

Chicago Atlanta New York St. Louis Detroit Cincinnati Pittsburgh Charlotte Boston

Chicago – 0

Atlanta 720 – 0

461

New York 790 884 – 0

St. Louis 297 555 976 – 0

Detroit 283 722 614 531 – 0

Cincinnati 296 461 667 359 263 – 0

Pittsburgh 461 685 371 602 286 288 – 0

Charlotte 769 245 645 715 629 479 448 – 0

Boston 996 1,099 219 1,217 721 907 589 867 – 0

Facility Location using Multiple	Solution
DESCRIPTION: Once you’re finished with the Gravity Method, it’s optional, but you might want to check out this tab called “9 City”. You’ll need Excel’s SOLVER again, but this is all set up where you only need to put the Total Allowed number of Cities for your distribution centers into the yellow cell, and SOLVER will provide the minimum cost solution for variable transportation costs represented by the cities with ones shown in the green cells. Try 1, 2, 3 or more options for Distribution Centers. Check with the map. Does each solution make sense based on the size of each cities and the distances to get to all 9 cities?
Cost to serve
					Chicago	2,267,300,000
					Atlanta	505,648,000
					New York										– 0
					St. Louis	341,600,000
					Detroit	553,214,000
					Cincinnati	222,111,000	1) Enter the desired # of facilities (in YELLOW cell)
					Pittsburgh	113,526,000	2) Run SOLVER to find the optimal location(s)!
					Charlotte	466,335,000
					Boston	133,590,000
TOTAL	4,603,324,000	<<<--- Total Distance x Demand (this serves as a proxy for Total Transportation Costs!)
Set of Cities	Demand-j	X-i (use = 1)
2,870,000
572,000
8,450,000
350,000
901,000
333,000
306,000
723,000
610,000
Total Facilities in solution	(After running SOVLER, this value must equal your # of allowed facilities.)
Total Facilities Allowed	<<<--- You set the constraint on the number of facilities allowed.
City served
Y- i,j	Map source: batchgeo.com
Served from …
Note: These cells indicate an OPEN connection (1) and CLOSED connections (0).
SOLVER will be changes these automatically until the lowest cost solution is found.
Must Be Supplied
Distance Matrix
	720		790		297		283		296				461		769		996
	884		555		722		685		245		1,099
	976		614		667		371		645		219
	531		359		602		715		1,217	Note: This is input data based on miles between each city.
	263		286		629		721	Notice how the upper-right is exactly a mirror-image of the lower-left?
	288		479		907	Example: It’s the same distance from Chicago to Atlanta and from Atlanta to Chicago.
	448		589
	867	This could also be multiplied by the actual cost-per-mile, but if the costs are about the same all over the East Coast, then simply minimize the miles driven will minimize the cost too.

Iterate

Module1

𝑳𝑨𝑻=

σ

𝑷

∗𝑳𝑨𝑻

σ
𝑷

𝑳𝑶𝑵=

σ

𝑷∗𝑳𝑶𝑵

σ
𝑷