ABC Company is planning for the production of two products: Product X and Product Y. Based on the assumption that all items can be sold, the manager wants to make decision on how many items to produce for each type of products in order to maximize the profit given the following constraints:

• The total labor available is 20,000 hours.
• The total material M1 available is 2,000 pounds.
• The total material M2 available is 2,000 pounds.
• The company will NOT produce a fraction of any product.

We have following information about each product:

Product X

• The profit margin is $3,600 per unit.
• The production of each unit requires 10 hours of labor.
• The production of each unit requires 2 pounds of M1.
• The production of each unit requires 3 pounds of M2.

Product Y

• The profit margin is $4,200 per unit.
• The production of each unit requires 15 hours of labor.
• The production of each unit requires 3 pounds of M1.
• The production of each unit requires 1.5 pounds of M2.

Using Solver in Excel with the table in the Production worksheet, answer the following questions (2 points each):

Question 1: Which cell reference should be the objective for Solver?

Question 2: Which cell or cell range should be changed by the Solver to meet the objective?

Question 3: How many constraints should be added into the Solver (not including the Integer constraints)?

Question 4: As part of the constraints, what should be the formula in the cell D5?

Question 5: What should be the value in the cell F5?

Transcribed Image Text:

1 2 3 Number of Items Produced 4 Profit Margin 5 Labor 6 M1 7 M2 8 9 Max Profit 10 11 12 Product X 10 2 3 $3,600 Product Y 15 3 1.5 $4,200 D Constraints 1 2 3 Number of Items Produced 4 Profit Margin 5 Labor 6 M1 7 M2 8 9 Max Profit 10 11 12 Product X 10 2 3 $3,600 Product Y 15 3 1.5 $4,200 D Constraints

Answer rating: 100% (QA)

Product X P... View the full answer