Question

M4_A2. Jackson Paper Mills, Inc., has plants in Boone, NC and Augusta, Georgia. Warehouses are located in Tampa, FL and Winchester, VA. Distributors are located in Detroit, MI; Knoxville, TN; and New Orleans, LA. Products are shipped from the plants to the warehouses and from the warehouses to the distributors. There is no storage at the warehouses so any product entering the warehouses must be shipped to distributors. The plant capacities and distributor demands are shown in the tables below: DEMAND (units) CAPACITY (units) PLANT DISTRIBUTOR Detroit Boone 400 200 Knoxville New Orleans Augusta 250 300 200 The unit transportation costs ($) for the shipments from the two plants to the two warehouses and from the two warehouses to the three distributors are shown below: TO WAREHOUSE Winchester Tampa FROM Boone 12 11 PLANT Augusta 9 14 TO DISTRIBUTOR Detroit Knoxville New Orleans Tampa WAREHOUSE Winchester FROM 18 14 13 16 10 12 Formulate this problem in Excel and solve to determine the minimum shipping costs required to meet demands. (Hint: you will not be able to satisfy all demands) a) How many decision variables does this problem have? b) Not counting the non-negativity constraint -how many constraints does this problem have? c) What is the total cost to minimize shipping costs from the plants to the distributors? d) Which routes had zero units shipped? e) Which distributor was unable to meet its demand? f How many units should be shipped from Boone to Winchester? g) How many units should be from Augusta to Tampa? h) How many units should be from Tampa to Detroit? i) How many units should be from Tampa to New Orleans? j) How many units should be from Winchester to Detroit? k) How many units should be from Winchester to Knoxville? How many units should be from Winchester to New Orleans?

Answer 1

ANSWER:

Given that,

The flow diagram for the problem is shown below: De Bo Ta Kn Au Wi NO Formulate this problem in Excel spreadsheet as shown below:

C F A B D K Units shipped 1 2 To Warehouse Tampa To Distributor Knoxville New Orleans Total Total Total Winchester capacity capacity From Warehouse Detroit From plant Boone 0 400 Tampa Winchester 0 6 Augusta Total 0 250 0 Total 7 0 0 0 0 8 Demand 9 200 300 200 10 Cost calculation: 11 To Distributor Knoxville New Orleans $13 To Warehouse 12 13 From plant From Warehouse Detroit $18 $16 Tampa $12 $9 Winchester $11 $14 $14 14 Boone Tampa Winchester $10 $12 15 Augusta 16 Objective function $0 17 18 Formulas used in the cells are shown below

A C D E F Units shipped 1 2 To Warehouse Tampa 3 Winchester Total capacity |=SUM(B5:C5) |=SUM(B6:C6) 4 From plant Total capacity 5 Boone 400 6 Augusta 250 7 Total -SUM(B5:B6) SUM(C5:C6) 8 10 Cost calculation: 11 To Warehouse 12 13 From plant Tampa Winchester 14 Boone 12 11 Augusta 9 15 14 16 Objective function =SUMPRODUCT(B5:C6,B14:C15)+SUMPRODUCT(H5:J6,H14:J15) 17 18

G J K 1 2 To Distributor Knoxville New Orleans 3 Total 4 From Warehouse Detroit |=SUM(H5:J5) |=SUM(H6:J6) Tampa Winchester 6 Total -SUM(H5:H6) SUM(I5:16) =SUM(J5:J6) 7 Demand 200 300 200 10 11 To Distributor 12 13 From Warehouse Detroit Knoxville New Orleans 18 13 12 14 14 Tampa 16 Winchester 15 10 Now, solve the problem using the excel solver. Put the following values in the solver window T

рата осIS UITine ForecaST Solver Parameters X SF$17 Set Objective: To: Min 0 Value Of Мax By Changing Variable Cells: $B$5:$C$6,$H$5:$J$6 Subject to the Constraints: SF$5:SF$6 SD$5:SD$6 Add $H$7:$J$7 SH$9:SJ$9 $K$5 $B$7 $K$6 = SCS7 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 The final output is shown below

&SUMPRODUCT(C4:G7,C13:G16) C20 |Solver Parameters A C D E G H Unit transportation costs ($) 1 eC$20 Set Objective: Warehouse Distributor 2 To: OValue Of Tampa Winchester Detroit O Max O Min Knoxville New Orleans Capacity 3 400 Boone 12.0 11.0 4 By Changing Variable Cels: 14,0 250 5 Augusta 9.00 $C$13:$D$14,SE$15: $G$16 18.0 13.0 6 Tampa 14.0 Winchester 16,0 10.0 12.0 7 Subject to the Constraints: 300 Demand 200 200 8 16 SES17:SGS17<-SE$8: SG$8 SH$13:SH$14 SH$4:SH$5 Add q Optimal Distribution Plan Change 10 Warehouse 11 Distributor Delete Tampa Wincheste Detroit Total Knoxville New Orleans 12 0 400 400 13 Boone Reset All 250 250 14 Augusta 0 0 200 250 Tampa 50 15 Load/Save 300 400 Winchester 100 16 Make Unconstrained Variables Non-Negative Total 400 150 300 200 250 17 Select a Solving Method: Simplex LP Options 18 19 Solving Method 14,750 Total Cost 20 Select GRG Noninear engine for Solver Problems that are smooth nonlnear. Select the LP Simplex Evolutionary engine linear Solver Problems, and select problems that are 21 non-smooth. 22 23 24 Help Solve Close 25

Formula used in above Excel model: C20 =SUMPRODUCT(C4:G7,C13:G16)

In TOTAL rows and column , SUM formula is used.

a) How many decision variables does this problem have?   

ANSWER:10

b) Not counting the non-negativity constraint - how many constraints does this problem have?

ANSWER:7

c) What is the total cost to minimize shipping costs from the plants to the distributors?

ANSWER: $ 14750

d) Which routes had zero units shipped?

ANSWER: Boone-Tampa, Augusta-Winchester, Tampa-Knoxville, Winchester-New Orleans

e) Which distributor was unable to meet its demand?

ANSWER: Detroit

f) How many units should be shipped from Boone to Winchester?

ANSWER: 400

g) How many units should be from Augusta to Tampa?

ANSWER: 250

h) How many units should be from Tampa to Detroit?

ANSWER: 50

i) How many units should be from Tampa to New Orleans?

ANSWER: 200

j) How many units should be from Winchester to Detroit?

ANSWER: 100

k) How many units should be from Winchester to Knoxville?

ANSWER: 300

l) How many units should be from Winchester to New Orleans?

ANSWER: 0