QUESTION 3: Linear Programming Formulation (33 points) Acme sells two sprocket assemblies for escalators and elevators. Each type of sprocket must first be machined and then assembled. Unit machining and assembly times, capacity limitations, demand restrictions and revenue-cost data are as follows: Required Hrs/unit Max Unit Machining Assembly Sales Price $) Sprocket 1 2 2 15 50 Sprocket 2 4 1 20 60 Capacity (hrs/wk) 90 50 $5/hr $ 4/hr Let xl and x2 represent the number of units of sprockets 1 and 2 to produce, respectively. MAX Z= 32x1 + 36x2 Subject to Max Sales Sp. 1: X1 0 (a) In November, Acme introduces a third type of sprocket (Sprocket 3). To produce one sprocket 3 requires 3 hours of machining and three hours of assembly. The maximum sales of sprocket 3 is 10 units. The selling price of a sprocket 3 is $61. The above formulation can be modified to take account of the new product. The new formulation is: Let x1, x2 and x3 represent the number of units of sprockets 1, 2 and 3 to produce, respectively. MAX Z= 32x1 + 36x2 Subject to Max Sales Sp. 1: X1 0 Complete the formulation above by filling in the four blanks (8 points). (b) In December, the problem turns out to be the same as the November problem given in part (a), except that a new packaging process has resulted in an additional requirement that at least 2 sprocket l's must be produced for every sprocket 2 that is produced. Either include below the LP formulation of this new problem, or clearly state how the formulation in part (a) could be changed to deal with the December situation. (2 points) (c) In January, the problem turns out to be the same as the November problem given in part (a), except that the selling price per unit for sprockets 1 and 2 have each decreased by 10%. Either included below the LP formulation of this new problem, or clearly state how the formulation in part (a) could be changed to deal with the January situation. (3 points) (d) In February, the problem turns out to be the same as the November problem given in part (a), except that it is now in February) possible to purchase up to 10 additional hours of machine time at a price of $6 per hour. The $6 per hour cost applies to only the additional 10 hours. Either include below the LP formulation of this new problem, or clearly state how the formulation in part (a) could be changed to deal with the February situation. (6 points) Hint: Add a new decision variable (Y: the number of extra machine hours to produce). (e) Formulate the original linear programming problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding Excel Spreadsheet and the Sensitivity Report). Include managerial statements that communicate the results of the analysis. (i.e. describe verbally the results). (8 points) Let xl and x2 represent the number of units of sprockets 1 and 2 to produce, respectively. MAX Z= 32x1 + 36x2 Subject to Max Sales Sp. 1: X1 0 (f) Would it be worthwhile to increase the maximum sales level on Sprocket 2 OR to increase the maximum Machine capacity? Justify. (2 points). (g) ACME is offered $7 per hour for use of their assembly time by an outside contractor. How much assembly time is it worthwhile to sell? (2 points). (h) If a machine breakdown reduced machining capacity by 20 hours, how would profits be affected? (2 points)