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

Gravity 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,000)	P*Lat	P*Lon		2) Compute each city's "Population x Latitude" (column E).
1	16.6	154.8	1655				3) Compute each city's "Population x Longitude" (column F).
2	16.7	156.8	2300				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	1385				Note: This will minimize the total travel distance from the Distribution Center to each customer!
5	17.0	152	1230
6	17.2	144.9	665
7	17.5	155.7	664
8	17.4	147.1	885
9	17.5	141.1	1116
10	17.8	155.1	636
11	17.9	153.8	1200
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

Facility Location using Multiple Solutions:
		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		Instructions
	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)
	Chicago	2,870,000	0
	Atlanta	572,000	0
	New York	8,450,000	1
	St. Louis	350,000	0
	Detroit	901,000	0
	Cincinnati	333,000	0
	Pittsburgh	306,000	0
	Charlotte	723,000	0
	Boston	610,000	0
		Total Facilities in solution	1	(After running SOVLER, this value must equal your # of allowed facilities.)
		Total Facilities Allowed	1	<<<--- You set the constraint on the number of facilities allowed.
		City served
	Y- i,j	Chicago	Atlanta	New York	St. Louis	Detroit	Cincinnati	Pittsburgh	Charlotte	Boston			Map source: batchgeo.com
Served from …	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	Note: These cells indicate an OPEN connection (1) and CLOSED connections (0).
	Boston	0	0	0	0	0	0	0	0	0	SOLVER will be changes these automatically until the lowest cost solution is found.
	Must Be Supplied	1	1	1	1	1	1	1	1	1
	Distance Matrix	Chicago	Atlanta	New York	St. Louis	Detroit	Cincinnati	Pittsburgh	Charlotte	Boston
	Chicago	- 0	720	790	297	283	296	461	769	996
	Atlanta	720	- 0	884	555	722	461	685	245	1,099
	New York	790	884	- 0	976	614	667	371	645	219
	St. Louis	297	555	976	- 0	531	359	602	715	1,217	Note: This is input data based on miles between each city.
	Detroit	283	722	614	531	- 0	263	286	629	721	Notice how the upper-right is exactly a mirror-image of the lower-left?
	Cincinnati	296	461	667	359	263	- 0	288	479	907	Example: It's the same distance from Chicago to Atlanta and from Atlanta to Chicago.
	Pittsburgh	461	685	371	602	286	288	- 0	448	589
	Charlotte	769	245	645	715	629	479	448	- 0	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.
	Boston	996	1,099	219	1,217	721	907	589	867	- 0

Iterate

Module1