Problem 3 (35 points) The Moon Coffee Shop blends coffee on the premises for its customers....

Transcribed Image Text:

Problem 3 (35 points) The Moon Coffee Shop blends coffee on the premises for its customers. It sells three basic blends in 1-pound bags, Special, Moon Dark, and Moon Regular. It uses four different types of coffee to produce the blends - Brazilian, Mocha, Columbian, and Mild. The shop used the following blend recipe requirements. Mix Requirements Blend Selling Price/pound ($) Special Dark Regular At least 40% Columbian, at least 30% Mocha At least 60% Brazilian, no more than 10% Mild No more than 60% Mild, at least 30% Brazilian 16.50 15.25 13.75 The cost of Brazilian coffee is $3.00 per pound, the cost of Mocha is $3.75 per pound, the cost of Columbian is $3.90 per pound, and the cost of Mild is $2.70 per pound. The shop has 110 pounds of Brazilian coffee, 70 pounds of Mocha, 80 pounds of Columbian, 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 the profit (i.e., revenue - cost). (a) Formulate algebraically the Linear Programming (LP) model for this problem. Define the decision variables, objective function, and constraints. (19 points) (b) Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding "Excel Spreadsheet" and the "Answer Report"). Include "managerial statements" that communicate the results of the analyses (i.e. describe verbally the results). (16 points) Problem 3 (35 points) The Moon Coffee Shop blends coffee on the premises for its customers. It sells three basic blends in 1-pound bags, Special, Moon Dark, and Moon Regular. It uses four different types of coffee to produce the blends - Brazilian, Mocha, Columbian, and Mild. The shop used the following blend recipe requirements. Mix Requirements Blend Selling Price/pound ($) Special Dark Regular At least 40% Columbian, at least 30% Mocha At least 60% Brazilian, no more than 10% Mild No more than 60% Mild, at least 30% Brazilian 16.50 15.25 13.75 The cost of Brazilian coffee is $3.00 per pound, the cost of Mocha is $3.75 per pound, the cost of Columbian is $3.90 per pound, and the cost of Mild is $2.70 per pound. The shop has 110 pounds of Brazilian coffee, 70 pounds of Mocha, 80 pounds of Columbian, 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 the profit (i.e., revenue - cost). (a) Formulate algebraically the Linear Programming (LP) model for this problem. Define the decision variables, objective function, and constraints. (19 points) (b) Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding "Excel Spreadsheet" and the "Answer Report"). Include "managerial statements" that communicate the results of the analyses (i.e. describe verbally the results). (16 points)