Question

The distribution system for the Herman Company consists of three plants, two ware- houses, and four customers. Plant capacities and shipping costs per unit (in $) from each plant to each warehouse are as follows:

		Warehouse
Plant		1	2		Capacity
1		4	7		450
2		8	5		600
3		5	6		380

Customer demand and shipping costs per unit (in $) from each warehouse to each customer are as follows:

		Customer
Warehouse		1	2	3	4
1		6	4	8	4
2		3	6	7	7
Demand		300	300	300	400

1. Develop a network representation of this problem.
2. Formulate a linear programming model of the problem.
3. How do you Solve the linear program to determine the optimal shipping plan using Solver in excel? How do you formulate the information in excel to enter into solver? Please provide a step by step on how Solver would be used to solve this problem.

Answer 1

a)

yEo Plant al 2 7

b)

FROM/TO Warchawouhauae Plant i Plant2 Planta 지 72 -

bl CI di aI ba 2

Qlrective ippina tost dit ya 느 600 bitb2ニ300 7r o

c)

1From To Warehouse 1 Warehouse 2 Capacity Plant 1 Plant 2 plant 3 450 600 5 From To Customer 1 Customer 2 Customer 3 Customer 4 6 Warehouse1 7 Warehouse 2 Demand 300 300 300 400 Decision Variables 10 FromTo Warehouse 1 Warehouse 2 Plant 1 Plant 2 plant 3 12 13 14 FromTo Customer 1 Customer 2 Customer 3 Customer 4 15 Warehouse 1 16 Warehouse 2

Obiective function 17 18-SUMPRODUCT(B11:C13,B2:C4)+SUMPRODUCT(B15:E16,B6:E7) 19 20 SUM(B11:C11) 21 SUM(B12:C12) 22 SUM(B13:C13 23 SUM(B11:B13 24 SUM(C11:C13 25 -B15+B16 26 C15+C16 27 D15+D16 28 E15+E16 Constraints -D2 -D3 -SUM(B15:E15) -SUM(B16:E16 -B8 -D8 30

Objective function 18 19 20 21 0 Constraints 24 25 26 27 28 23 20 C) C) C) C) C) C) 450 600 380 C) C) 300 300 300 400 C)

Solver Parameters SAS18 Set Objective To: OMax By Changing aiable Cells: Value Of: SBS11:SCS13,SBS15:SES16 Subject to the Constraints: SAS20:SAS22 -SCS20:SCS22 SAS23:SAS28 SCS23:SCS28 SBS11:SCS130 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Decision Variables Warehouse 1 Warehouse 2 From To plant 1 plant 2 plant 3 From To Warehouse 1 Warehouse 2 450 0 250 0 600 0 Customer 1 Customer 2 Customer 3 Customer 4 4 0 300 400 300 0 300 Obiective function 11850