CASE #7 - Sensitivity Analysis - Interpretation Name(s): Frandec Company manufactures, assembles, and rebuilds material handling equipment used in warehouses and distribution centers. One product, called a Liftmaster, is assembled from four components: a frame, a motor, two supports, and a metal strap. Frandec's production schedule calls for 5000 Liftmasters to be made next month. Frandec purchases the motors from an outside supplier, but the frames, supports, and straps may either be manufactured by the company or purchased from an outside supplier. Manufacturing and purchase costs per unit are shown. Component Manufacturing Cost Purchase Cost Frame $38.00 $51.00 Support $11.50 $15.00 Strap $6.50 $7.50 Three departments are involved in the production of these components. The time (in minutes per unit) required to process each component in each department is given, along with the available capacity (in hours) for the three departments. Component Frame Support Strap Capacity (hours) Department Cutting Milling Shaping 3.5 2.2 3.1 1.3 1.7 2.6 0.8 1.7 350 420 680 (a) Formulate a linear programming model for this make-or-buy application. (b) Solve the linear program obtained in (a) in Excel and submit the file in Canvas. (C) Write a report to explain the solution obtained. (d) Where is the bottleneck of this system? What is the implied utilization of each of the departments? When possible answer the following questions based only in the sensitivity report. If you need to run the solver again, explain why it isn't possible to answer the question with the sensitivity report provided by Excel. (e) How much should Frandec be willing to pay for an additional hour of time in the cutting department? (f) Unfortunately Frandec doesn't have right now a financial situation that allows them to invest in new machinery but an additional cutting machine can be rented for $15,000 per month. The machine can increase the monthly cutting capacity by 160 hours per month. Should Frandec rent the new machine? Explain. (g) Another manufacturer has offered to sell frames to Frandec for $45.00 each. Can Frandec improve its position by pursuing this opportunity? Why or why not? (h) A new order for 500 additional Liftmasters has been received. Frandec is willing to fulfill this order as long as they can get a 30% profit from it. Is it possible to predict the increase in the overall cost of an increase of demand of 500 units? How much should Frandec charge for these 500 units in order to achieve the required 30% profit