Question

Red Box Manufacturing Company must decide how many plastic coolers to produce in the next month. Red Box currently produces three kinds of coolers: 12 qt, 20 qt, and 48 qt. Details for each product are given in the table below.

Model	Profit	Labor	Plastic	Demand
12 qt	$4	1	1.81	2501
20 qt	$9	3	3.06	1088
48 qt	$14	3.3	4.88	1261
Amount on hand		9430	14150

Use Solver to find the best product mix. Your answer should not consume more resources than the amount on hand, and production amounts should not exceed forecasted demand.

Write an email to your supervisor, Sebastian, in the box below. Your email should consist of one or two paragraphs containing your recommendations. Your recommendatons should include the optimal product mix and the expected profit from your recommendations. In addition, consider the aspects of the problem that are limiting profit. Suggest strategies for mitigating these limitations and increasing profit. If you are unable to complete the problem, write "Unable to solve" in the box. I will then assess your work for partial credit.

At the conclusion of the exam, submit your spreadsheet in Final Exam Dropbox as directed. Remember that you must post your spreadsheet within 15 minutes of completing your exam, and your answers must agree with your spreadsheet.

Answer 1

Solution using Solver is following

Formulas:

E2 =SUMPRODUCT(B2:D2,$B$7:$D$7) copy to E2:E4

Optimal product mix:

12 qt = 2501

20 qt = 922

48 qt = 1261

Total profit = 35956