Using the template below show the steps to get the solution. (Note: the data from P04_48.xlsx has been added into the spreadsheet).

Question: NewAge Pharmaceuticals produces the drug NasaMist from four chemicals. Today, the company must pro- duce 5000 pounds of the drug. The three active ingredients in NasaMist are A, B, and C. By weight, at least 7% of NasaMist must consist of A, at least 5% must consist of B, and at least 3% must consist of C. The cost per pound of each chemical and the amount of each active ingredient in one pound of each chemical are given in the file P04_48.xlsx. At least 600 pounds of chemical 3 must be used.

a. Determine the cheapest way of producing todays batch of NasaMist.

b. Use SolverTable to see how much the percentage of requirement of B is really costing NewAge. Let the percentage required vary from 3% to 8%.

R inni S A 1 Producing drugs 2 3 Cost/lb of chemicals 4 Chemical 1 Chemical 2 Chemical 3 7 Chemical 4 8 9 Ingredient/lb of chemical 10 11 Chemical 1 0.06 12 Chemical 2 0.1 13 Chemical 3 0.12 14 Chemical 4 0.03 16 Required percentages of ingredients 17 A 18 19 20 Production plan 21 Lbs used 22 Chemical 1 23 Chemical 2 24 Chemical 3 25 Chemical 4 26 Total 27 28 29 30 Percentage constraints (expressed in lbs) 31 32 Actual 33 34 Required 35 36 Cost 37. P4-48 6 ***** esc Ready 915 fx B P4-46 Calculate $8 $10 $11 $14 A @ C 0.04 0.03 0.09 0.02 8 B P4-50 * 12 B Required C 21 D C 0.01 0.04 0.04 0.01 C P4-50 part B 80 E P4-50 part C 090 FA Formula E P4-50