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 (S) Sprocket 1 2 2 15 50 Sprocket 2 4 1 20 60 Capacity (hrs/wk) 90 50 Cost $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 s15 Max Sales Sp. 2: x2 Machine Time: 2x 1 + 4x2 Assembly Time: 2x x2 s 50 Non-negativity: X1, X2 20 s 20 S 90 + (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.c. describe verbally the results). (8 points) 15 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 Max Sales Sp. 2: 2 Machine Time: 2x1 Assembly Time: 2x 1 + x2 Non-negativity: X1. 20 s 20 + 4x2 S 90 550 X2