Question

Consiaer tne Tollowing network representation or a transportation probiem: Des Moines 30 Jefferson City 20 10 Cit 30 St. 10 Supplies Demands The supplies, demands, and transportation costs per unit are shown on the network. The optimal (cost minimizing) distribution plan is given below Des Moines Kansas City St.Louis Supply Jefferson City Omaha Demand Total Cost: $470 Find an alternative optimal solution for the above problem If required, round your answer to nearest whole number and if your answer is zero enter "O". 10 0 10 10 20 0 30 30 30 10 Des Moines Kansas City St.Louis Jefferson City Omaha Total Cost: $

Answer 1

Form the objective function and the constraints

Let xlAmount shipped from Jefferson City to Des Moines x12Amount shipped from Jefferson City to Kansas City X23: Amount shipped from Omaha to St. Louis Min 14x11 11 x11 +8x21 +10x22 + 5x23 16x12+ 7x S 20 x12 X13 + X21 =30 = 10 + x23 = 10 x12 + 22 x13 x11, x12, x13, x21, x22, x23, 20

In excel, form two tables, in the first table put all the parameters and in the second table provide the formula as given in the picture below

Go to data tab in excel and select Solver in the Analysis panel (you need to Add-in Solver in Excel if you don't find)

Shipping Cost (Per Un 4 Supply 20 30 Des Moines 14 Kansas Cit 16 10 10 St. Louis 6 Jefferson City 7 Omaha 8 Demand 30 10 10 11 Model 12 13 St. Louis 10 0 Total Shipped SUM(B15:D15) SUM(B16:D16) Des Moines 0 30 -SUM(B15:B16) Kansas Cit 10 0 -SUM(C15:C16) SUM(D15:D16) 15 Jefferson City 16 Omaha 17 Total Shipped 18 19 20 Total Cost 21 SUMPRODUCT (B6:D7,B15:D16) 23 24

In the Solver put the required parameters as shown in the picture (Our objective is to minimize cost)

Solver Parameters Set Objective: SB$20 0 Value Of: Min 0: Max By Changing Variable Cells: SB$15:SD$16 Subject to the Constraints: $B$17:SD$17 $B$8:$D$8 SE$15:SE$16 <= $E$6:$E$7 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. Close Help Solve

The Solver will provide the below output and Optimized Cost as $470 which is already provided in the question

Shipping Cost (Per Unit) Supply Des Moine Kansas Cit St. Louis 6 Jefferson City 7 Omaha 8 Demand 16 10 10 20 30 30 10 10 11 Model 12 13 14 15 Jefferson City 16 Omaha 17 Total Shipped 18 19 20 Total Cost 21 Des Moine Kansas Cit St. Louis Total Shipped 10 0 10 10 0 10 20 30 0 30 30 470

To get an alternative solution keeping the Total cost same as $470 you'll need to form a new Objective function, new constraints and you will have to run the solver once again as shown in the below pictures

Our New objective is to maximize the sum of the variables which came out as zero when we ran solver previously and constraint is to keep the Total cost same as $470

Shipping Cost (Per Unit) Des Moines 14 Kansas City 16 10 10 Supply 20 30 St. Louis 7 Jefferson City Omaha Demand 10 30 Model Des Moines 10 20 -SUM(B15:B16) Total Shipped -SUM(B15:D15) -SUM(B16:D16) Kansas City 0 10 -SUM(C15:C16) St. Louis 10 8.88178419700125E-16 -SUM(D15:D16) Jefferson City Omaha Total Shipped Total Cost SUMPRODUCT(B6:D7,B15:D16) New Objective Function -SUM(B15,C16,D16) Sum the variables which is equal to zero and maximize it New Constaint B20 Total cost shoul remain 470 470

Solver Parameters SAS25 Set Objective: O Min To: Max O Yalue Of: By Changing Variable Cells: SB$15:SD$16 Subject to the Constraints: SA$28 $C$28 $B$17:$D$17 $B$8:$D$8 $E$15:$E$16 <- $E$6:$E$7 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

Shipping Cost (Per Unit) Des Moines Kansas Cit St. Louis 16 10 10 Supply Jefferson City Omaha Demand 14 8 30 20 30 10 Model Total Shipped Des Moines Kansas Cit St. Louis 0 Jefferson City Omaha Total Shipped 10 20 30 20 30 10 10 -8.88178E-16 10 10 Total Cost 470 New Objective Function Sum the variables which is equal to zero and maximize it 20 New Constaint Total cost shoul remain 470 470 470 =

we get the alternative solution you can neglect the value -8.88178419700125E-16 from the solution and can assume it to be zero.

for cell reference

Shipping Cost (Per Unit) 4 Supply 20 30 St. Louis Kansas City 16 10 10 Des Moines 14 6 Jefferson City 7 Omaha 8 Demand 10 30 10 11 Mode 12 13 14 15 Jefferson City 16 Omaha 17 Total Shipped 18 19 20 Total Cost 21 Total Shipped -SUM(B15:D15) -SUM(B16:D16) St. Louis 10 8.88178419700125E-16 SUM(D15:D16) Kansas City Des Moines 10 20 SUM B15:B16) 10 -SUM(C15:C16) SUMPRODUCT(B6:D7,B15:D16) 23 24 New Obje Function 25 SUM(B15,C16,D16) 26 27 New Constaint 28 B20 Sum the variables which is equal to zero and maximize it Total cost shoul remain 470 470

Let xlAmount shipped from Jefferson City to Des Moines x12Amount shipped from Jefferson City to Kansas City X23: Amount shipped from Omaha to St. Louis Min 14x11 11 x11 +8x21 +10x22 + 5x23 16x12+ 7x S 20 x12 X13 + X21 =30 = 10 + x23 = 10 x12 + 22 x13 x11, x12, x13, x21, x22, x23, 20

Shipping Cost (Per Un 4 Supply 20 30 Des Moines 14 Kansas Cit 16 10 10 St. Louis 6 Jefferson City 7 Omaha 8 Demand 30 10 10 11 Model 12 13 St. Louis 10 0 Total Shipped SUM(B15:D15) SUM(B16:D16) Des Moines 0 30 -SUM(B15:B16) Kansas Cit 10 0 -SUM(C15:C16) SUM(D15:D16) 15 Jefferson City 16 Omaha 17 Total Shipped 18 19 20 Total Cost 21 SUMPRODUCT (B6:D7,B15:D16) 23 24

Solver Parameters Set Objective: SB$20 0 Value Of: Min 0: Max By Changing Variable Cells: SB$15:SD$16 Subject to the Constraints: $B$17:SD$17 $B$8:$D$8 SE$15:SE$16 <= $E$6:$E$7 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. Close Help Solve

Shipping Cost (Per Unit) Supply Des Moine Kansas Cit St. Louis 6 Jefferson City 7 Omaha 8 Demand 16 10 10 20 30 30 10 10 11 Model 12 13 14 15 Jefferson City 16 Omaha 17 Total Shipped 18 19 20 Total Cost 21 Des Moine Kansas Cit St. Louis Total Shipped 10 0 10 10 0 10 20 30 0 30 30 470

Shipping Cost (Per Unit) Des Moines 14 Kansas City 16 10 10 Supply 20 30 St. Louis 7 Jefferson City Omaha Demand 10 30 Model Des Moines 10 20 -SUM(B15:B16) Total Shipped -SUM(B15:D15) -SUM(B16:D16) Kansas City 0 10 -SUM(C15:C16) St. Louis 10 8.88178419700125E-16 -SUM(D15:D16) Jefferson City Omaha Total Shipped Total Cost SUMPRODUCT(B6:D7,B15:D16) New Objective Function -SUM(B15,C16,D16) Sum the variables which is equal to zero and maximize it New Constaint B20 Total cost shoul remain 470 470

Solver Parameters SAS25 Set Objective: O Min To: Max O Yalue Of: By Changing Variable Cells: SB$15:SD$16 Subject to the Constraints: SA$28 $C$28 $B$17:$D$17 $B$8:$D$8 $E$15:$E$16 <- $E$6:$E$7 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

Shipping Cost (Per Unit) Des Moines Kansas Cit St. Louis 16 10 10 Supply Jefferson City Omaha Demand 14 8 30 20 30 10 Model Total Shipped Des Moines Kansas Cit St. Louis 0 Jefferson City Omaha Total Shipped 10 20 30 20 30 10 10 -8.88178E-16 10 10 Total Cost 470 New Objective Function Sum the variables which is equal to zero and maximize it 20 New Constaint Total cost shoul remain 470 470 470 =