Using Excel Solver Question

data1.xlsx

Question 1

Allied Freight data
Input data
Unit shipping costs
		To
		Customer 1	Customer 2	Customer 3
From	Warehouse 1	$15	$35	$25
	Warehouse 2	$10	$30	$40
Shipping plan, and constrain on demand and supply
Unit shipping costs		To
		Region 1	Region 2	Region 3	Total shipped	Capacity
From	Warehouse 1	10	0	30	40	40
	Warehouse 2	20	10	0	30	30
	Total received	30	10	30
	Demand	30	30	30
Penalty for unmet demand
		Customer 1	Customer 2	Customer 3
	For each unit	$90	$80	$110
	Unmet demand	0	20	0
	Total Penalty	$ 1,600.00
Objective to maximize
Total cost		$ 3,000.00

Distances between cities
	Boston	Chicago	Dallas	Denver	Los Angeles	Miami	New York	Phoenix	Pittsburgh	San Francisco	Seattle
Boston	0	983	1815	1991	3036	1539	213	2664	792	2385	2612
Chicago	983	0	1205	1050	2112	1390	840	1729	457	2212	2052
Dallas	1815	1205	0	801	1425	1332	1604	1027	1237	1765	2404
Denver	1991	1050	801	0	1174	2041	1780	836	1411	1765	1373
Los Angeles	3036	2112	1425	1174	0	2757	2825	398	2456	403	1909
Miami	1539	1390	1332	2041	2757	0	1258	2359	1250	3097	3389
New York	213	840	1604	1780	2825	1258	0	2442	386	3036	2900
Phoenix	2664	1729	1027	836	398	2359	2442	0	2073	800	1482
Pittsburgh	792	457	1237	1411	2456	1250	386	2073	0	2653	2517
San Francisco	2385	2212	1765	1765	403	3097	3036	800	2653	0	817
Seattle	2612	2052	2404	1373	1909	3389	2900	1482	2517	817	0
Locations of service centers
	Boston	Chicago	Dallas	Denver	Los Angeles	Miami	New York	Phoenix	Pittsburgh	San Francisco	Seattle	Service centers		Required centers
Include service center	0	0	1	0	0	0	1	0	0	1	0	3	=	3