From chapter 30 1. Jason makes diamond bracelets, necklaces, and earrings. He wa

Question

From chapter 30 1. Jason makes diamond bracelets, necklaces, and earrings. He wants to work a maximum of 160 hours per month. He has 800 ounces of diamonds. The profit, labor time, and ounces of diamonds required to produce each product are as follows. If demand for each product is unlimited, how can Jason maximize his profit? Unit profit Labor hours per Ounces of diamonds per unit Product Bracelet $300 .35 Necklace $200 15 Earrings $100 05 1.2 75 .5 In your product mix example, suppose that whenever you sell more than 400 pounds of any product, you must give a $1 per pound discount on each pound above 400 sold. How does this change the answer to the problem? 1. If demand is unlimited and negative production is allowed, find a feasible solution that earns a billion dollars. 1. Assuming unlimited demand and unlimited resources, find a feasible solution that earn $1 billion in profit

Explanation / Answer

1. I have used EXCEL solver to solve this product mix problem

A key to solving the product mix problem is to compute the resource usage and profit associated with any given product mix efficiently. An important tool that you can use to make this computation is the SUMPRODUCT function. It multiplies corresponding values in cell ranges and returns the sum of those values.

Profit Cell: =SUMPRODUCT(B7:B9,D7:D9)-IF(B7>400,(B7-400),0)-IF(B8>400,(B8-400),0)-IF(B9>400,(B9-400),0)

If conditions are used to calculate profit after subtracting discount. Suppose that whenever you sell more than 400 pounds of any product, you must give a $1 per pound discount on each pound above 400 sold.

Labor Cell:=SUMPRODUCT(B7:B9,E7:E9)

Diamond Cell: =SUMPRODUCT(B7:B9,F7:F9)

Target cell: The goal is to get the value of profit as 1 billion

Changing cells: Units of bracelets, necklaces, and earrings produced to meet the constraints and get the target cell

Constraints: Labor <= 160hours and Diamond <=800 pounds

To begin, click the Data tab and, in the Analysis group, click Solver.

Click the Set Objective box and then select the profit cell (cell B13). To value of 1E+09

Click the By Changing Variable Cells box and then point to the B7: B9 range, which contains the units of bracelets, necklaces, and earrings produced.

You’re now ready to add constraints to the model. Click the Add button. To add the resource usage constraints, click the Cell Reference box and then select the B16 range. After confirming the selection of <= from the middle list, click the Constraint box and then select the D16 cell range.

You have now ensured that when Solver tries different values for the changing cells, only combinations that satisfy both C16<=E16 (labor used is less than or equal to 160 hours). Click Ok.

Click Add to enter the diamond constraints. To add the diamond usage constraints, click the Cell Reference box and then select the B19 range. After confirming the selection of <= from the middle list, click the Constraint box and then select the D19 cell range.

You have now ensured that when Solver tries different values for the changing cells, only combinations that satisfy both C17<=E17 (diamond used is less than or equal to 800 ounces). Click Ok.

Unselecting the Make Unconstrained Variables Non-Negative check box ensures that all the changing cells can be less than 0. (negative production)

Selecting a GRG Nonlinear as Simplex LP was not able to get the feasible solution for the problem.

Click solve we get

To get the problem of 1 billion, since demand is unlimited and negative production is allowed

Units of bracelet produced = -11075930.58

Units of necklace produced = 33967253.77

Units of earrings produced = -24367047.27

2. Since constraints are removed in this problem as unlimited resources are available, and we have solved this with simple GRG Nonlinear, Selecting the Make Unconstrained Variables Non-Negative check box ensures that all the changing cells should be greater or equal to 0.

the feasible solution to get the profit of 1 billion is

Units of bracelet produced = 2152077.762

Units of necklace produced = 1434718.508

Units of earrings produced = 717359.2539

Units produced Unit profit labor hours per unit ounces of diamonds per unit -11075930.58 Bracelet $    300.00 0.35 1.2 33967253.77 Necklace $    200.00 0.15 0.75 -24367047.27 Earrings $    100.00 0.05 0.5 Profit 1000000000 Labor 160 <= 160 Diamond 800 <= 800

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.