M8_2. Cullowhee Specialties manufactures parts for the furniture industry. The marketing department is predicting sales of exacth 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 amy 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 fall three combined) to Cullowhee Specialties. The cost to purchase these parts from suppliers is in the table below: 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. 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. 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: SOLUTION: 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. b) 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. M8_2. Cullowhee Specialties manufactures parts for the furniture industry. The marketing department is predicting sales of exacth 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 amy 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 fall three combined) to Cullowhee Specialties. The cost to purchase these parts from suppliers is in the table below: 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. 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. 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: SOLUTION: 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. b) 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