5.3. Consider the Big M Co. problem presented in Section 3.5, including the spreadsheet in Figure 3.10 showing its formu- lation and optimal solution. There is some uncertainty about what the unit costs will be for shipping through the various shipping lanes. Therefore, before adopting the optimal solution in Figure 3.10, manage- ment wants additional information about the effect of inaccura- cies in estimating these unit costs. Use Solver to generate the sensitivity report preparatory to addressing the following questions. a. Which of the unit shipping costs given in Table 3.9 has the smallest margin for error without invali- dating the optimal solution given in Figure 3.10? Where should the greatest effort be placed in esti- mating the unit shipping costs? b. What is the allowable range for each of the unit shipping costs? c. How should the allowable range be interpreted to management? d. If the estimates change for more than one of the unit shipping costs, how can you use the sensitiv- ity report to determine whether the optimal solution might change? 96 Chapter Three Linear Programming: Formulation and Applications TABLE 3.9 Some Data for the Big M Company Distribution- Network Problem Shipping Cost for Each Lathe Customer 2 Customer 3 Customer 1 Output From Factory 1 Factory 2 Order size $700 800 $900 900 8 lothes $800 700 12 lathes 15 lathes 10 lathes 9 lathes 3.6 Assignment Problems 99 = problem (including this one) is guaranteed in advance to have an optimal solution that has only integer values despite the fact that fractional solutions also are allowed. In particular, as long as the data for the problem includes only integer values for all the supplies and demands (which are the outputs and order sizes in the Big M Company problem), any transportation problem with feasible solutions is guaranteed to have an optimal solution with integer values for all its decision variables. Therefore, it is not necessary to add constraints to the model that require these variables to have only integer values. To summarize, here is the algebraic form of the linear programming model that has been formulated in the spreadsheet: Minimize Cost = 700 SPL-CI + 900 SF1-c2 + 800 Sp. + 800S2.C + 900 Sr2.c2 + 70057203 subject to the following constraints: 1. Fixed-requirement constraints: SF-ci + Sp-c2+ SF1- = 12 (Factory 1) Spac + Sp2c2 + Sp2.cz = 15 (Factory 2) SFL-CI + Sp2.c (Customer 1) Sp.cz + SF2.02 = 8 (Customer 2) SF1-c3 + SF2.03 = 9 (Customer 3) = 10 2. Nonnegativity constraints: SFI-CI 20 SF-c220 SFI c 20 Sp2-ci 20 Sf2c220 Sp2.c 20 FIGURE 3.10 The spreadsheet model for the Big M Company problem, including the formulas for the objective cell TotalCost (H15) and the other output cells TotalShipped Out (FI1:F12) and TotalToCustomer (C13:E13), as well as the specifications needed to set up Solver. The changing cells Units Shipped (C11:E12) show the optimal solution obtained by Solver. B D E F H Big M Company Distribution Problem G 1 2 3 4 Shipping Cost (per Lathe) Factory 1 Factory 2 5 Customer1 $700 $800 Customer 2 $900 8900 Customer 3 S800 $700 6 ou 7 8 9 Total Shipped Out 10 Customer 1 Customer 2 Customer 3 11 Units Shipped Factory! Factory 2 Total to Customer 10 0 12 15 Output 12 15 12 13 10 9 14 Total Cost 15 Order Size 10 9 $20,500 Cells F Range Name 8 9 10 Solver Parameters Set Objective Cell: TotalCost To: Min By Changing Variable Cells: UnitsShipped Subject to the Constraints: TotalShipped Out = Output TotalToCustomer OrderSize Solver Options: Make Variables Nonnegative Solving Method: Simplex LP Order Size Output Shipping Cost TotalCost TotalShipped Out TotalToCustomer Units Shipped CIS:E15 H11:12 CS:E6 HIS FITF12 Ci3:E13 CHE12 Total Shipped Out =SUMCI:EN) =SUM(C12:E12) 12 B D E 13 Total to Customer =SUM(CH:C12) SUM(DI:DI2) SUMETT:E12) 14 Total Cost SUMPRODUCT(Shipping Cost Units Shipped) 15