M8_2. Cullowhee Specialties manufactures parts for the furniture industry. The marketing department is predicting sales of exactly 8700 of each of these parts in the next quarter. These three parts are a lumbar support, footrest bracket, and a recline mechanism. Some of these components will have to be purchased from outside suppliers due to limited in-house manufacturing capacity. The supplier can supply a maximum of 4,500 units for any of these parts. The supplier does have a limit on the total number of parts they can supply. They can supply a total of 9,700 total parts (all three combined) to Cullowhee Specialties. The cost to purchase these parts from suppliers is in the table below: PURCHASE COST FROM SUPPLIERS Lumbar Support $11.45 Footrest Bracket $7.35 Recline Mechanism $10.63 Cullowhee Specialties plant consists of three departments. Each one of these parts that is manufactured must be processed in all three departments. The time requirements in hours for each component in each department if manufactured in-house are summarized in the following table. The hours available for production of these parts are listed in the last row. COMPONENT Assembly Dept. Finishing Dept. Fabrication Dept. (hrs./unit) (hrs./unit) (hrs./unit) Lumbar Support 0.22 0.24 0.21 Footrest Bracket 0.14 0.12 0.21 Recline Mechanism 0.15 0.21 0.24 Capacity (hrs.) 3500 3500 3800 The cost of producing a part at Cullowhee Specialties is based on the number of hours a part spends in a department and the cost per hour that the department charges for its operation. Note that these costs do not apply when purchasing a part from a supplier - these costs only apply to internally manufactured parts. Assembly Dept. Finishing Dept. FABRICATION Dept. ($/hr) (($/hr) ($/hr) Operating cost 14.75 18.50 16.30 The revenue generated per part is the same whether you manufacture the part in Cullowhee Specialties manufacturing facility or buy the part from a supplier and is shown below: COMPONENT Revenue ($/part) Lumbar Support 15.21 Footrest Bracket 14.49 Recline Mechanism 16.75 3SOLUTION: The optimal solution, the value of the profit, should be $153,270.71 (+ $10 for rounding). Some of your decision variables will not be integers. The profit to make one Lumbar Support in-house and sell it to your customer is $4.10. The profit to buy one Lumbar Support from your supplier and sell it to your customer is $3.76. Formulation: Formulate this problem by defining decision variables, objective function, and constraints in standard format as specified/demonstrated in videos. Questions: a) If you could add 10 hours of capacity to any department to increase profit - adding ten hours of capacity to which department would generate the biggest increase in profit: Assembly, Finishing, or Fabrication? What would be the change in profit? Explain why you would add capacity to this department. Which of the following constraints have slack - Assembly, Finishing, or Fabrication? Explain. c) In your solution, how many Footrest Brackets should the company make? d) If the cost per hour of the Fabrication department increases to $20/hr. - how many Recline Mechanisms should the company make in their manufacturing facility with this new process cost? Did this result in them making more or less of the recline mechanisms in their manufacturing facility? Why did you think this is the case? Use the LP Template.xlsx provided to you. Solve the problem using Excel Solver, place the answers to the questions in the appropriate text boxes. I will delete your decision variables in your Excel submission and re-solve your problem. There is no credit for the problem if your Excel submission doesn't solve. See the diagram of a correct solution provided to you on the first problem. If necessary, remove problem constraints so that your Excel file solves. It is better to get partial credit than no credit. a)Will add 10 hours of capacity in the assembly department, since it is the start of the process and the profit will going to increase and the assembly department has a lower operating cost than the other department. by Fabrication department since this is the last part of production and they can meet the target if they want to gain more profit, the company should focus in the fabrication department. c) They should produce more than the produce last quarter. d) $36.30, more recline since they can produce more than the company expected from them since they are the last part in the department of processing