I need help specifically with part (b), (c), (d). I think the answers can be found in the answer report. I think Maximum Z is 1125.5, but I don't know what the optimal point is.

Excel:

Answer Report:

Sensitivity Report:

4. The Mill Mountain Coffee Shop blends coffee on the premises for its customers. It sells three basic blends in 1-pound bags, Special, Mountain Dark, and Mill Regular. It uses four different types of coffee to produce the blends - Brazilian, mocha, Colombian, and mild. The shop has the following blend recipe requirements: Blend Mix requirements Selling price ($)/pound Special 6.20 Dark At least 30% Columbian, at least 40% mocha At least 50% Brazilian, no more than 10% mild No more than 70% mild, at least 30% Brazilian 5.50 4.00 Regular The cost of Brazilian coffee is $2.00 per pound, the cost of mocha is $2.75 per pound, the cost of Colombian is $2.90 per pound, and the cost of mild is $1.70 per pound. The shop has 110 pounds of Brazilian coffee, 70 pounds of mocha, 80 pounds of Colombian, and 150 pounds of mild coffee available per week. The shop wants to know the amount of each blend it should prepare each week to maximize profit. (a) Formulate this problem in a linear programming model. (b) Solve the problem by using the computer. What are the maximum 2 and the optimal point? (c) Based on the optimal solution (b), how much cost can be caused by the use of Colombian coffee? (d) Based on the optimal solution (b), how much sales can be generated by selling Mountain Dark blend? Profit ($) Constraint Resources/ Achievement Constraints Requirements Slack/Surplus 110 110 70 70 80 =$J$10 Binding 0 $H$11>=$J$11 Binding 0 $H$12=$J$14 Not Binding 40 110 $H$5=$J$9 Binding Microsoft Excel 16.16 Sensitivity Report Worksheet: [bl.xlsx]Sheet1 Report Created: 2/22/20 5:53:17 PM Variable Cells Final Reduced Value Cost 0 -3.55271E-15 700 52.5 52.5 0 0 Cell Name $B$6 X15 values (bbls) $B$7 X2S values (bbls) $B$8 X3S values (bbls) $B$9 X4S values (bbls) $B$10 X1D values (bbls) $B$11 X2D values (bbls) $B$12 X3D values (bbls) $B$13 X4D values (bbls) $B$14 X1R values (bbls) $B$15 X2R values (bbls) $B$16 X3R values (bbls) $B$17 X4R values (bbls) Objective Allowable Coefficient Increase 4.2 3.55271E-15 3.451E+30 3.3 7.99361E-15 5 4. 1 E+30 3.5 3.55271E-15 2.75 5.5 2.6 3.8 1E+30 2 0 1.25 5.5 1.1 7.333333333 2.3 3.55271E-15 Allowable Decrease 1E+30 5.5 7.333333333 3.55271E-15 0 1E+30 1E+30 3.55271E-15 3.55271E-15 1E+30 -5.5 o 0 0 110 0 27.5 97.5 0 0 -5.5 0 2.3 Constraints Shadow Price -5.5 -3 3.55271E-15 0 Cell Name $H$10 blend - special Achievement $H$11 blend - dark Achievement $H$12 blend - dark Achievement $H$13 blend - regualr Achievement SH$14 blend - regualr Achievement $H$5 brazilian availability Achievement $H$6 mocha availability Achievement $H$7 columbian availability Achievement $H$8 mild availability Achievement $H$9 blend - special Achievement Final Value 0 O 0 -67 39.5 110 70 80 150 0 Constraint Allowable Allowable R.H. Side Increase 30 36.66666667 0 32.91666667 0 0 1E+30 67 0 39.5 1E+30 110 1E+30 56.42857143 70 36.66666667 70 80 131.6666667 27.5 150 131.6666667 97.5 0 27.5 52.5 0 2 6.75 1.1 2.3 -7.99361E-15