Question

Location Section Project A small logistic company named LogTransit currently has 3 manufacturing facilities (suppliers) called Supplier #1, #2 and #3 to supply for goods of its 3 retail stores (customers) called Customer A, B, and C. Due to customer demands at stores are increasing, the company is considering to construct a new manufacturing facility. The new factory will have a supply capacity of 200 units per week. After screening many potential sites, Toledo and Cincinnati have been determined to be the two leading candidate locations for the new facility. Assume that overall Transportation costs from Suppliers to Customers will be the main determinant to determine which of the two locations to be selected as the new plant site. The following two tables provide information of i) capacity of old suppliers and new supplier candidates; ii) The per unit Transportation cost from existing Suppliers to customers and per unit Transportation cost from the new candidate supplier location to customers, and 3) demands from customers and available capacity of each supplier. Shipping cost/unit A B с 10 14 10 Supplier #1 #2 Capacity 200 140 150 20 12 11 17 11 #3 12 Demand 220 220 220 Shipping cost/unit A B с Capacity 200 New Supplier Toledo 18 8 14 Cincinnati 7 17 13 200 Tasks: Part I. Determine which new plant location may lead to a lower total transportation costs. You need to do the followings i) Formulate the problem into two Linear Programming models. ii) Solve each Linear Programming using Excel Solver. iii) Draw conclusion on which new site should be selected, or will have minimal transportation costs under optimal shipping schedule. Part II. Use Sensitivity Report of solver outputs for the linear programming model of “Toledo" case to answer the following questions. If the shipping cost from Supplier 3 to Demand B is increased to 12, will the overall shipping quantity from Suppliers to Customers remain the same? What will be Total Transportation Cost now? ii) If the shipping cost from Supplier 3 to Demand B is increased to 20, will the optimal shipping schedule change when Toledo is select remain the same? iii) If the new Toledo capacity is increased to 210 units, how much Total Transportation costs can be reduced? iv) If the new Toledo capacity is increased to 250 units, how much Total Transportation costs can be reduced? Note Again: a) The questions should be answered using the Answer Report and Sensitivity Report from the ORIGINAL LP formulation ONLY, not from revised solution reports with LP Updated Parameters. b) Please give explanations for your answers by pointing out the exact location in the reports to support your answers.

Answer 1

Part I

i)

Let Xij represent quantity to be shipped from supplier i to customer j

LP model using Toledo (i=4)

Min 10X1A+14X1B+10X1C+12X2A+17X2B+20X2C+11X3A+11X3B+12X3C+18X4A+8X4B+14X4C

s.t.

X1A+X1B+X1C <= 200

X2A+X2B+X2C <= 140

X3A+X3B+X3C <= 150

X4A+X4B+X4C <= 200

X1A+X2A+X3A+X4A = 220

X1B+X2B+X3B+X4B = 220

X1C+X2C+X3C+X4C = 220

Xij >= 0

-----------------

LP model using Cincinnati (i=5)

Min 10X1A+14X1B+10X1C+12X2A+17X2B+20X2C+11X3A+11X3B+12X3C+7X5A+17X5B+13X5C

s.t.

X1A+X1B+X1C <= 200

X2A+X2B+X2C <= 140

X3A+X3B+X3C <= 150

X5A+X5B+X5C <= 200

X1A+X2A+X3A+X5A = 220

X1B+X2B+X3B+X5B = 220

X1C+X2C+X3C+X5C = 220

Xij >= 0

--------------------------------------------------------------------------------------------------------------------------------

ii)

Solution using Excel Solver for Toledo

Create Excel model as follows:

B C D E А Parameter Table 1 2 A B с Capacity 3 #1 10 14 10 200 4 # 2 12 17 20 140 11 12 150 8 14 200 220 220 5 #3 11 6 #4 - Toledo 18 7 Demand 220 8 9 Optimal shipping schedule 10 A 11 #1 B с 12 #2 Row Total =SUM(B11:011) =SUM(B12:012) =SUM(B13:013) =SUM(B14:014) 13 # 3 14 =A6 15 Column Total =SUM(B11:B14) =SUM(C11:C14) -SUM(D11:014) 16 17 18 Total Cost =SUMPRODUCT(B3:D6,B11:014) 19 20 21

Enter Solver Parameters as shown below:

Solver Parameters X Set Objective: SE$18 To: Max Min Value Of: 0 By Changing Variable Cells: $B$11:$D$14 Subject to the Constraints: $E$11:$E$14 < = $E$3:$E$6 $B$15:$D$15 = $B$7:$D$7 Add Change Delete Reset All Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems. Solve Close

Click Solve to generate the solution. Values appear automatically in yellow cells (B11:D14)

С D E F G Н K L M N O B с S Solver Results X 14 10 20 17 А B 1 Parameter Table 2 A 3 #1 10 4 #2 12 5 # 3 11 6 #4 - Toledo 18 7 Demand 220 8 9 Optimal shipping schedule 10 А 11 #1 0 Capacity 200 140 150 200 11 8 12 14 Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer O Keep Solver Solution Sensitivity Limits O Restore Original Values 220 220 B C Row Total 0 Return to Solver Parameters Dialog 200 200 12 #2 110 0 0 110 OK Cancel 110 20 20 150 13 # 3 14 #4 - Toledo 15 Column Total 0 200 0 200 220 220 220 Solver found a solution. All constraints and optimality conditions are satisfied. When Simplex LP is used, this means Solver has found a global optimal solution. 16 17 18 Total Cost 6590 19 20 21

Click OK

Total cost using Toledo = 6,590

-

Solution using Excel Solver for Cincinnati

Change the cost data in B6:D6 and again run the Solver (by opening Solver Parameters and clicking Solve button on it)

Result is following:

E18 @fx =SUMPRODUCT(B3:06,B11:014) A B С D E F G Н K M N O 1 Parameter Table 2 A B с Solver Results 3 #1 10 14 10 Capacity 200 140 17 20 12 150 11 17 220 13 200 220 Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer O Keep Solver Solution Sensitivity Limits O Restore Original Values 4 #2 12 5 # 3 11 6 #5 - Cincinnati 7 7 Demand 220 8 9 Optimal shipping schedule 10 A 11 #1 0 12 #2 20 13 #3 0 14 #5 - Cincinnati 200 15 Column Total 220 16 C Row Total 200 200 Return to Solver Parameters Dialog 0 110 B 0 90 130 0 220 20 150 OK Cancel 200 0 220 Solver found a solution. All constraints and optimality conditions are satisfied. When Simplex LP is used, this means Solver has found a global optimal solution. 17 18 Total Cost 6840 19 20 21 22

Total cost using Cincinnati = 6,840

--------------------------------------------------------------------------------------------------------------------------------

iii)

In part (ii), we see that total cost using Toledo is lower (6590).

Therefore, Toledo should be selected