Question: Please show excel formula and solver analysis. Please also highlight the answers for each question to be easier to follow A manufacturer sells two types

Please show excel formula and solver analysis.

Please show excel formula and solver analysis. Please also highlight the answers for each question to be easier to follow

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. a) What is the effective capacity for product 1 and product 2, respectively? b) In the optimal solution, which product(s) will be manufactured? What is the optimal production quantity? What is the optimal profit

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!