Solve the linear programming model formulated in Question 5 of General Midterm Review doc- ument located in itslearning (the coffee blending example) by using Excel. State the optimal blending and the optimal profit. Include the excel file you generated (sensitivity report, answer report, excel model). (50 points) Question 5: 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 At least 40% Columbian, at least 30% mocha $6.50 Dark At least 60% Brazilian, no more than 10% mild 5.25 Regular No more than 60% mild, at least 30% Brazilian 3.75 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 cach blend it should prepare cach week to maximize profit. a. Formulate a linear programming model for this problem. Copyright 2013 Pearson Education, Inc. Publishing as Prentice Hall x = lbs. of coffee i used in blend j per week, where i= b (Brazilian), o (Mocha), c (Colombian), m (mild) and j=s (special), d (dark).T (regular) maximize Z-4.5x + 3.75x, +3.60x +4.&xm +3.25xd + 2.5xd +2.35 +3.55xd +1.75X +1.00xar +0.85x+2.05. subject to 6x - 4x - 4x - 4x 20 - 3x + 7xos - 3x3 - 3x. 20 4xbd - 6x6x cd 6.xm20 .1.Xod.lxed + 9.X SO -6x6xor - 6x + 4x 50 17Xfr - 3x - 3x - 3x20 s+X+X5110 Xos Xod XX580 ms 3150 570 > + x 20 Solve the linear programming model formulated in Question 5 of General Midterm Review doc- ument located in itslearning (the coffee blending example) by using Excel. State the optimal blending and the optimal profit. Include the excel file you generated (sensitivity report, answer report, excel model). (50 points) Question 5: 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 At least 40% Columbian, at least 30% mocha $6.50 Dark At least 60% Brazilian, no more than 10% mild 5.25 Regular No more than 60% mild, at least 30% Brazilian 3.75 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 cach blend it should prepare cach week to maximize profit. a. Formulate a linear programming model for this problem. Copyright 2013 Pearson Education, Inc. Publishing as Prentice Hall x = lbs. of coffee i used in blend j per week, where i= b (Brazilian), o (Mocha), c (Colombian), m (mild) and j=s (special), d (dark).T (regular) maximize Z-4.5x + 3.75x, +3.60x +4.&xm +3.25xd + 2.5xd +2.35 +3.55xd +1.75X +1.00xar +0.85x+2.05. subject to 6x - 4x - 4x - 4x 20 - 3x + 7xos - 3x3 - 3x. 20 4xbd - 6x6x cd 6.xm20 .1.Xod.lxed + 9.X SO -6x6xor - 6x + 4x 50 17Xfr - 3x - 3x - 3x20 s+X+X5110 Xos Xod XX580 ms 3150 570 > + x 20