Solve using Excel-Solver and provide screenshots with explanations

4. The Mill Mountain Coffee Shop blends coffee on the premises for its customers. It sells three basic blends in l-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 Special At least 30% Columbian, at least 40% mocha Dark At least 50% Brazilian, no more than 10% mild Regular No more than 70% mild, at least 30% Brazilian Selling price ($)/pound 6.20 5.50 4.00 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 a linear programming model for this problem. (b) Solve the problem by using the computer. What are optimal objective function value 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? Solution