Question

Small motors for garden equipment are produced at four manufacturing facilities and need to be shipped to three plants that produce different garden items (lawn mowers, rototillers, leaf blowers). The company wants to minimize the cost of transporting items between the facilities, taking into account the demand at the three different plants, and the supply at each manufacturing site. The table below shows the cost to ship one unit between each manufacturing facility and each plant (transportation costs), as well as the demand at each plant and the supply at each manufacturing facility. From Manufacturing Facility To Plant Supply 350 NO ABC 3 3.5 2.8 3.5 3 4 4.5 3 3.8 210 160 110 150 wa 200 Demand After you have formulated and solved this problem, please indicate the number of items that should be shipped from the manufacturing facility #2 in order to minimize the company's cost of transportation: 00 OOOO 210

Answer 1

First design the two matrices as shown below:-

13 Supply 2 Cost Matrix 350 150 200 3.5 2.8 3.5 4.5 210 3.8 180 110 6 Demand Actual Supply Max Supply 9 Transportation Matrix A 10 350 150 200 0 12 13 Actual Demand 14 Demand 15 16 Cost 210 180 110 0

The formula sheet for the above excel is:-

2 Cost Matrix Supply 350 150 200 3.5 2.8 3.5 4.5 210 3.8 110 6 Demand 180 9 Transportation Matrix 10 1 11 2 12 3 13 Actual Demand 14 Demand 15 16 Cost 17 Actual Supply SUM(B10:010 SUM(B11:D11) SUM B12:D12 Max Supply 350 150 200 -SUM(B10:B12) 210 SUM(C10:C12) 180 -SUM(D10:D12) 110 SUMPRODUCT(B3:D5,B10:D12)

Add the Constraints and changing cells on Solver as shown and press Solve.

Solver Parameters 2 Cost Matrix Set Objective To: O Max O Min By Changing Variable Cells SB$16 3.5 2.8 Value Of 3.5 4.5 210 3.8 110 180 6 Demand SBS10:SDS12 Subject to the Constraints Add Change Delete SB$13:SD$13 $B$14:$D$14 SES 10:SES 12 <= $F$ 10:$F$12 Actua 9 Transportation Matrix A 10 0 12 13 Actual Demand 14 Demand 15 16 Cost 17 18 19 20 21 Reset AlI 210 180 110 Load/Save 0. Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP 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. 23 24 25 26 Close Solve

The final output is as shown Hence, the number of items that should be shipped from the manufacturing facility 2 is 0 units as shown above.

Supply 350 150 200 2 Cost Matrix 3.5 2.8 3.5 4.5 210 3.8 110 180 6 Demand Actual Supply Max Supply 320 9 Transportation Matrix A 10 350 150 200 110 210 0 180 180 180 180 110 110 12 13 Actual Demand 14 Demand 15 16 Cost 17 210 210 1478