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%.

Q12 A 1 Producing drugs 2 3 Cost/lb of chemicals 4 Chemical 1 5 Chemical 2 6 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 15 16 Required percentages of ingredients 17 A 18 7% 19 20 Production plan 21 Lbs used 14 22 Chemical 1 DR 23 Chemical 2 J 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-46 P4-48 38 Ready BIU EB C 21 X fx B $8 $10 $11 $14 A 0.04 0.03 0.09 0.02 B 5% B 8 P4-50 3% Required 5000 600 . D C 0.01 0.04 0.04 0.01 C P4-50 part B Av E P4-50 part Q12 A 1 Producing drugs 2 3 Cost/lb of chemicals 4 Chemical 1 5 Chemical 2 6 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 15 16 Required percentages of ingredients 17 A 18 7% 19 20 Production plan 21 Lbs used 14 22 Chemical 1 DR 23 Chemical 2 J 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-46 P4-48 38 Ready BIU EB C 21 X fx B $8 $10 $11 $14 A 0.04 0.03 0.09 0.02 B 5% B 8 P4-50 3% Required 5000 600 . D C 0.01 0.04 0.04 0.01 C P4-50 part B Av E P4-50 part