Tutorial 2: Sensitivity Analysis

Problem 1 (Exam 2007): The Classic Furniture Company is trying to determine the optimal quantities to make of six possible products: tables and chairs made of oak, cherry, and pine. The products are to be made using the following resources: labour hours and three types of wood. The availability of each resource as well as each items resource usage (technological coefficients) are shown in the Excel layout (Figure 1). Minimum production requirements are as follows: at least 3 each of oak and cherry tables, at least 10 each of oak and cherry chairs, and at least 5 pine chairs. The objective function coefficients in Figure 1 refer to the unit profit per item. The LP Sensitivity Report is shown in Figure 2.

Classic Furniture Company

Number of units Profit per unit () Constraints Labour hours Oak (pounds) Cherry (pounds) Pine (pounds) Min oak tables Min cherry tables Min oak chairs Min cherry chairs Min pine chairs

5178.33 <- Objective 517.83 <= 1500 <= 2000 <= 3000 <= 3 >= 3 >= 30 >= 35.56 >= 75 >= LHS Sign 1000 1500 2000 3000 3 3 10 10 5 RHS

Figure 1: Excel layout

Adjustable Cells Cell $D$6 $E$6 $F$6 $G$6 $H$6 $I$6 Name Number of units Oak tables Number of units Oak chairs Number of units Cherry tables Number of units Cherry chairs Number of units Pine tables Number of units Pine chairs Final Value 3 30 3 35.56 0 75 Reduced Cost 0 0 0 0 -45 0 Objective Coefficient 75 35 90 60 45 20 Allowable Increase 158.33 1E+30 310 1E+30 45 1E+30 Allowable Decrease 1E+30 23.75 1E+30 46.5 1E+30 10

Constraints Cell $J$9 $J$10 $J$11 $J$12 $J$13 $J$14 $J$15 $J$16 $J$17 Name Labour hours Oak (pounds) Cherry (pounds) Pine (pounds) Min oak tables Min cherry tables Min oak chairs Min cherry chairs Min pine chairs Final Value 517.83 1500 2000 3000 3 3 30 35.56 75 Shadow Price 0 1.17 1.67 0.5 -158.33 -310 0 0 0 Constraint R.H. Side 1000 1500 2000 3000 3 3 10 10 5 Allowable Increase 1E+30 4132.86 2893 9643.33 3 3.83 20 25.56 70 Allowable Decrease 482.17 600 920 2800 3 3 1E+30 1E+30 1E+30

Figure 2: Sensitivity Report Answer each of the following questions, each of which is independent of the others.

a) What is the optimal solution? Which constraints are binding? b) Interpret the shadow prices of the following three constraints: labour hours, the amount of cherry wood available, and the minimum production quantity for oak tables. c) Suppose Classic Furniture are forced to produce at least one table in pine. What would be the impact on profit? d) Classic Furniture can purchase an additional 1,000 pounds of oak at a price of 0.75 per pound. Should Classic Furniture buy this wood and do they need to change the current production plan? (Why?) What would be the impact on profit? e) Classic Furniture is considering the production of coffee tables. One coffee table would make a profit of 55 while its production requires 30 pounds of oak, 10 pounds of cherry and 5 hours of labour. Should the company produce coffee tables? Why (not)? f) It appears that Classic Furniture underestimated the profit of each item by 10%. Should Classic Furniture adapt the production plan? Why (not)? g) Tables and chairs usually sell as sets. For each type of wood, the number of chairs produced should not exceed 10 times the number of tables produced. What constraints should be added to the LP to reflect these conditions? Does the current solution satisfy these constraints? Problem 2: (Exam 2005) Steelco has received an order for 100 tonnes of steel. The order must contain at least 3.5 tonnes of nickel, at most 3 tonnes of carbon, and exactly 4 tonnes of manganese. Steelco receives 200/tonne for the order. To fill the order, Steelco can combine four alloys, whose chemical composition is given in Table 1. Steelco wants to maximise profit (revenues costs). Formulate Steelcos problem as an LP and solve it with Excel Solver. Table 1: Alloy composition and cost Alloy 1 6% 3% 8% 120 Alloy 2 3% 2% 3% 100 Alloy 3 2% 5% 2% 80 Alloy 4 1% 6% 1% 60

Nickel Carbon Manganese Cost/tonne

After solving Steelcos problem with excel solver, use the sensitivity report to answer the following questions: a) What is the optimal solution? Which constraints are binding? b) In what range of cost values for alloy 2 does the current solution remain optimal? Can you calculate the new profit if alloy 2 costs 90/tonne? c) Interpret the shadow prices of the different constraints? d) Suppose the cost per tonne of both alloy 2 and 4 decreases by 20. Will this change the optimal solution? Why or why not? Can you calculate the new profit?

e) Suppose the order should contain at least 3.55 tonnes of nickel. Would this change affect the profit? What about the optimal solution? f) Suppose that Steelco can use also alloy 5. Alloy 5 costs 100/tonne and contains 2% of nickel, 1% of carbon and 0.3% of manganese. Would you advise Steelco using alloy 5 in the order? g) Suppose alloys 1, 2, 3 and 4 contain respectively 2%, 3%, 1% and 4% of silicon. The 100 tonne steel order should contain at most 2.5 tonnes of silicon. Should Steelco revise its production plan?

Steelco
No. of tons Selling price () Cost () Profit () Constraints Nickel constraint Carbon constraint Manganese constraint Order size = 100 tons Alloy 1 25 200 120 80 0.06 0.03 0.08 1 Alloy 2 62.5 200 100 100 0.03 0.02 0.03 1 Alloy 3 0 200 85 115 0.02 0.05 0.02 1 Alloy 4 12.5 200 60 140 0.01 0.06 0.01 1

10000 3.5 2.75 4 100 LHS

<-- Objective >= <= = = Sign 3.5 3 4 100 RHS

Figure 3: Excel layout Steelco

Figure 4: Sensitivity report Steelco Problem 3 (4.22 & 4.23): The Good-to-Go Suitcase Company makes three kinds of suitcases: (1) Standard, (2) Deluxe and (3) Luxury styles. Each suitcase goes through four production stages: (1) cutting and colouring, (2) assembly, (3) finishing, and (4) quality and packaging. The total number of hours available in each of these departments is 630, 600, 708, and 135 respectively.

Each Standard suitcase requires 0.7 hours of cutting and colouring, 0.5 hours of assembly, 1 hour of finishing and 0.1 hours of quality and packaging. The corresponding numbers for each Deluxe suitcase are 1 hour, 0.83 hours, 0.67 hours, and 0.25 hours, respectively. Likewise, the corresponding numbers for each Luxury suitcase are: 1 hour, 0.67 hours, 0.9 hours, and 0.4 hours, respectively. The sales revenue for each type of suitcase is as follows: Standard 36.05, Deluxe 39.5 and Luxury 43.3. The material costs are Standard 6.25, Deluxe 7.5 and Luxury 8.5.The hourly cost of labour for each department is: cutting & colouring 10, assembly 6, finishing 9, and quality and packaging 8. a) Formulate an LP to determine how much suitcases Good-to-Go should make so as to maximise profit. Enter this problem into Excel and find the optimal solution. (Use the answer and sensitivity report to answer the following questions.) b) What is the optimal production plan? Which of the resources are scarce? c) Suppose Good-to-Go is considering a polishing process, the cost of which would be added directly to the price. Each Standard suitcase would require 10 minutes of time of this treatment, each Deluxe suitcase would need 15 minutes and each Luxury suitcase would need 20 minutes. Would the current production plan change as a result of this additional process if 170 hours of polishing time were available? Explain your answer. d) Suppose Good to-Go is considering the possible introduction of two new products: the Compact model and the Kiddo model (for children). Market research suggests that Good-to-Go can sell the Compact model for no more than 30, whereas the Kiddo model would go for as much as 37.5 to speciality toy stores. The amount of labour and the cost of raw materials for each possible new product are as follows: Cost Category Cutting and colouring (hr.) Assembly (hr.) Finishing (hr.) Quality and packaging (hr.) Raw materials () Compact 0.5 0.75 0.75 0.2 5 Kiddo 1.20 0.75 0.5 0.2 4.5

Would it be economically attractive to make any of these models? Explain your answer.