A manufacturer sells two types of products. Product 1 is sold at a price of $50 per unit and product 2 at a price of $60 per unit. Three units of raw material and 1.5 labor hours are needed to manufacturer one unit of product 1. Six units of raw material and 2 labor hours are need to manufacture one unit of product 2. The unit variable cost for product 1 is $30, and for product 2 is $20. A total of 15,000 units of raw material and 10,000 labor hours are available. If any product 1 is produced, a setup cost of $20,000 is incurred; if any product 2 is produced, a setup cost of $35,000 is incurred. Use Excel to setup a model to maximize the manufacturerâ€™s profit. Please include both the model worksheet and the answer report in your excel file.
- What is the effective capacity for product 1 and product 2, respectively?
- In the optimal solution, which product(s) will be manufactured? What is the optimal production quantity? What is the optimal profit?
Please answer in Excel and show calculation and functions used.