Excel & Frontline Solver - logical functions (IF, AND, OR, etc.)

Integer Optimization

logical functions (IF, AND, OR, etc.) 

Add Constraint

problem #10, p. 313 [1]

a.

--- Excel and Frontline Solver ---

Data			Distribution Costs per Pair
	To			From		Demand
	Distribution Centers	Pontiac	Cincinnati	Dayton	Atlanta	(pair/wk.)
	Milwaukee	0.42	0.46	0.44	0.48	10000
	Dayton	0.36	0.37	0.3	0.45	15000
	Cincinnati	0.41	0.3	0.37	0.43	16000
	Buffalo	0.39	0.42	0.38	0.46	19000
	Atlanta	0.5	0.43	0.45	0.27	12000
	Capacity (pair/ week)	27000	40000	40000	40000
	Productin cost/pair	2.7	2.64	2.69	2.62
	Fixed cost/ week	7000	4000	6000	7000

Decision			Distribution Costs per Pair
	To			From		total
	Distribution Centers	Pontiac	Cincinnati	Dayton	Atlanta	(pair/wk.)
	Open or not	1	1	0	1
	Milwaukee	0	0	0	10000	10000
	Dayton	0	15000	0	0	15000
	Cincinnati	0	16000	0	0	16000
	Buffalo	0	9000	0	10000	19000
	Atlanta	0	0	0	12000	12000
	total (pair/ week)	0	40000	0	32000
	total <= yCapacity	27000	40000	0	40000

				Productin cost =		189440
				Fixed cost per week =		18000
				distribution Cost =		26770
				Optimal total cost =		234210

       The Cincinnati plant and the Atlanta plant locations will minimize the costs: production cost

($189440 ), distribution cost ( $ 26770 ), and fixed costs ( $ 18000 ).The Cincinnati plant has to send

15000 pairs to Dayton, 16000 pairs to Cincinnati, and 9000 pairs to Buffalo, and the Atlanta plant has

to send 10000 pairs to Milwaukee, 10000 pairs to Buffalo, and 12000 pairs to Atlanta. 

       The optimal total cost is 234210.

--------------------------------------------------------------------------------------------------------------------------
b.

Data			Distribution Costs per Pair
	To			From		Demand
	Distribution Centers	Pontiac	Cincinnati	Dayton	Atlanta	(pair/wk.)
	Milwaukee	0.42	0.46	0.44	0.48	10000
	Dayton	0.36	0.37	0.3	0.45	15000
	Cincinnati	0.41	0.3	0.37	0.43	16000
	Buffalo	0.39	0.42	0.38	0.46	19000
	Atlanta	0.5	0.43	0.45	0.27	12000
	Capacity (pair/ week)	27000	40000	40000	40000
	Productin cost/pair	2.7	2.64	2.69	2.62
	Fixed cost/ week	7000	4000	6000	7000

Decision			Distribution Costs per Pair
	To			From		total
	Distribution Centers	Pontiac	Cincinnati	Dayton	Atlanta	(pair/wk.)
	Open or not	0	1	0	1
	Milwaukee	0	0	0	10000	10000
	Dayton	0	15000	0	0	15000
	Cincinnati	0	16000	0	0	16000
	Buffalo	0	9000	0	10000	19000
	Atlanta	0	0	0	12000	12000
	total (pair/ week)	0	40000	0	32000
	total <= yCapacity	0	40000	0	40000

				Productin cost =		189440
				Fixed cost per week =		11000
				distribution Cost =		26770
				Optimal total cost =		227210

          The optimal locations are the Cincinnati plant and the Atlanta plant. The Cincinnati plant has to send 15000 pairs to Dayton, 16000 pairs to Cincinnati, and 9000 pairs to Buffalo, and the Atlanta plant has to send 10000 pairs to Milwaukee, 10000 pairs to Buffalo, and 12000 pairs to Atlanta. These two plant locations will minimize the costs: production cost ( $189440 ), distribution cost ( $ 26770 ), and fixed costs ( $ 11000 ).

        The optimal total cost is 227210

Reference:
[1] Powell, Stephen G. Management Science: The Art Of Modeling With Spreadsheets, 4Th Edition. 1st ed. John Wiley & Sons, 2013. Print.