exel 4-3

exel4-LocationDecision.xlsx

Home >Information Systems homework help >exel 4-3

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	154.8	16.6	1655			3) Compute each city's "Population x Longitude" (column F).
2	156.8	16.7	2300			4) Sum all the PLat and PLon that will be needed for the final equation (E27 and F27).
3	153.2	16.8	601			5) Now, follow the Gravity Method formula to compute the Latitude and Longitude.
4	154	17.0	1385			Note: This will minimize the total travel distance from the Distribution Center to each customer!
5	152	17.0	1230
6	144.9	17.2	665
7	155.7	17.5	664
8	147.1	17.4	885
9	141.1	17.5	1116
10	155.1	17.8	636
11	153.8	17.9	1200
12	144.6	18.0	148
13	142.4	18.4	854
14	156.8	18.9	1473
15	148.3	19.3	615
16	152.9	19.4	1145
17	142.8	19.4	627
18	143.7	19.9	542
19	152.5	20.3	379
20	143.7	21.2	964
21	155.6	21.6	546
22	140.1	22.6	706
23	155.8	23.4	727
24	144.4	24.0	669			Your solution for a single, low-cost location:
25	146.4	24.9	931				Latitude	Longitude
		See your solution in the chart below!

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

Solution

City	Lat	Lon	Population (1,000)	P*Lat	P*Lon	Lat-cap	Lon-cap	See solution below!
1	154.8	16.6	1655	256194	27473	150.632709703	18.8236950095
2	156.8	16.7	2300	360640	38410
3	153.2	16.8	601	92073.2	10096.8
4	154	17.0	1385	213290	23545
5	152	17.0	1230	186960	20910
6	144.9	17.2	665	96358.5	11438
7	155.7	17.5	664	103384.8	11620
8	147.1	17.4	885	130183.5	15399
9	141.1	17.5	1116	157467.6	19530
10	155.1	17.8	636	98643.6	11320.8
11	153.8	17.9	1200	184560	21480
12	144.6	18.0	148	21400.8	2664
13	142.4	18.4	854	121609.6	15713.6
14	156.8	18.9	1473	230966.4	27839.7
15	148.3	19.3	615	91204.5	11869.5
16	152.9	19.4	1145	175070.5	22213
17	142.8	19.4	627	89535.6	12163.8
18	143.7	19.9	542	77885.4	10785.8
19	152.5	20.3	379	57797.5	7693.7
20	143.7	21.2	964	138526.8	20436.8
21	155.6	21.6	546	84957.6	11793.6
22	140.1	22.6	706	98910.6	15955.6
23	155.8	23.4	727	113266.6	17011.8
24	144.4	24.0	669	96603.6	16056
25	146.4	24.9	931	136298.4	23181.9

9 City

Facility Location using Multiple Solutions:
			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) Step 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
	4,603,324,000	<<<--- Total Distance x Demand
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 Facilites	1
	Total Allowed	1	<<<--- You set the constraint on the number of facilities allowed.
Y- i,j	Chicago	Atlanta	New York	St. Louis	Detroit	Cincinnati	Pittsburgh	Charlotte	Boston		Map sourc: batchgeo.com
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	1099
New York	790	884	0	976	614	667	371	645	219
St. Louis	297	555	976	0	531	359	602	715	1217	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	1099	219	1217	721	907	589	867	0

Iterate

Module1

𝑳𝑨𝑻=

𝑷∗𝑳𝑨𝑻

𝑷

𝑳𝑶𝑵=

𝑷∗𝑳𝑶𝑵

𝑷