Graffeg Coffee Roasting Company is one of North Americas oldest artisan coffee roasters and sells the...

 

 

Transcribed Image Text:

Graffeg Coffee Roasting Company is one of North Americas oldest artisan coffee roasters and sells the best coffee in San Francisco. Graffeg blends four beans into three final blends of coffee: one is sold to luxury hotels, another to restaurants, and the third to supermarkets for store-label brands. The company has four reliable bean supplies: Robusta, Javan Arabica. Liberica, and Brazilian Arabica. The table below summarizes the precise recipes for the final coffee blends, the costs and the availability information for the four components, and the wholesale price per pound of the final blends. The percentages indicate the fraction of each bean to be used in each blend. The roasting plant of the company can handle no more than 100,000 pounds per week. The minimum production levels for the hotel, restaurant, and supermarket blends Cost per lb. Maximum Weekly Restaurants Supermarkets Luxury Hotels Availability (lbs) Beans Robusta Javan Arabica Liberica Brazilian Arabica Wholesale Price (per lb) 20% 40% 15% 25% $1.25 35% 15% 20% 30% $1.50 10% 35% 40% 15% $1.40 $0.65 $0.80 $0.60 $0.70 22,500 25,000 20,000 45,000 are 15,000, 25,000 and 30,000 pounds. Please assume that there is plenty of demand for each blend, i.e. there are no demand constraints. a. Develop a linear optimization model to help Graffeo Coffee decide how many pounds of each blend to produce to maximize its total profit. In doing so, assume that fractional values are acceptable. Write down your decision variables and formulas for objective function and constraints below. b. [1 point] Use the sheet named "Q1" in the solution template and setup your model in Excel Solver. Then solve it using Excel Solver to find the optimal production plan. Write down your optimal solution below. Optimal value for each of your decision variables: Input Data Roasting Capacity (lbs) Component Robusta Javan Arabica Liberica Brazilian Arabica Price per lb Minimum desired (lbs) 100000 Hotels 0.2 0.4 0.15 0.25 1.25 15000 Restaurants 0.35 0.15 0.2 0.3 1.5 25000 Supermarkets 0.1 0.35 0.4 0.15 1.4 30000 Cost per lb 0.65 0.8 0.6 0.7 Max Avail (lbs) 22500 25000 20000 45000 In answering the following questions, use Solver's sensitivity report to give exact numerical answers whenever possible without rerunning the Solver. Include the sensitivity report sheet in your submission. If it is not possible to answer the question without rerunning Solver, please do so and report the answer and provide the excel model you ran in a separate sheet. [0.5 point] How much will the profit of Graffeo increase if the capacity of the roasting plant increases by an additional pound? Justify your answer. C. d. [0.5 point] Suppose that Graffeo can purchase an additional 3000 lbs. of Liberica for $4.00/lb. Should Graffeo make this purchase? What is the impact on the profit? Justify your answer. e. [0.5 point] Suppose that Graffeo has the opportunity to decrease the minimum production level for one of the three blends by 5000 lbs per week. What blend should Graffeo consider to decrease the production? What will be the new total profit? Justify your answer. f. [0.5 point] How much can Graffeo increase the wholesale price for the supermarket blend without changing the current optimal solution? What will be the new total profit? Justify your answer. Graffeg Coffee Roasting Company is one of North Americas oldest artisan coffee roasters and sells the best coffee in San Francisco. Graffeg blends four beans into three final blends of coffee: one is sold to luxury hotels, another to restaurants, and the third to supermarkets for store-label brands. The company has four reliable bean supplies: Robusta, Javan Arabica. Liberica, and Brazilian Arabica. The table below summarizes the precise recipes for the final coffee blends, the costs and the availability information for the four components, and the wholesale price per pound of the final blends. The percentages indicate the fraction of each bean to be used in each blend. The roasting plant of the company can handle no more than 100,000 pounds per week. The minimum production levels for the hotel, restaurant, and supermarket blends Cost per lb. Maximum Weekly Restaurants Supermarkets Luxury Hotels Availability (lbs) Beans Robusta Javan Arabica Liberica Brazilian Arabica Wholesale Price (per lb) 20% 40% 15% 25% $1.25 35% 15% 20% 30% $1.50 10% 35% 40% 15% $1.40 $0.65 $0.80 $0.60 $0.70 22,500 25,000 20,000 45,000 are 15,000, 25,000 and 30,000 pounds. Please assume that there is plenty of demand for each blend, i.e. there are no demand constraints. a. Develop a linear optimization model to help Graffeo Coffee decide how many pounds of each blend to produce to maximize its total profit. In doing so, assume that fractional values are acceptable. Write down your decision variables and formulas for objective function and constraints below. b. [1 point] Use the sheet named "Q1" in the solution template and setup your model in Excel Solver. Then solve it using Excel Solver to find the optimal production plan. Write down your optimal solution below. Optimal value for each of your decision variables: Input Data Roasting Capacity (lbs) Component Robusta Javan Arabica Liberica Brazilian Arabica Price per lb Minimum desired (lbs) 100000 Hotels 0.2 0.4 0.15 0.25 1.25 15000 Restaurants 0.35 0.15 0.2 0.3 1.5 25000 Supermarkets 0.1 0.35 0.4 0.15 1.4 30000 Cost per lb 0.65 0.8 0.6 0.7 Max Avail (lbs) 22500 25000 20000 45000 In answering the following questions, use Solver's sensitivity report to give exact numerical answers whenever possible without rerunning the Solver. Include the sensitivity report sheet in your submission. If it is not possible to answer the question without rerunning Solver, please do so and report the answer and provide the excel model you ran in a separate sheet. [0.5 point] How much will the profit of Graffeo increase if the capacity of the roasting plant increases by an additional pound? Justify your answer. C. d. [0.5 point] Suppose that Graffeo can purchase an additional 3000 lbs. of Liberica for $4.00/lb. Should Graffeo make this purchase? What is the impact on the profit? Justify your answer. e. [0.5 point] Suppose that Graffeo has the opportunity to decrease the minimum production level for one of the three blends by 5000 lbs per week. What blend should Graffeo consider to decrease the production? What will be the new total profit? Justify your answer. f. [0.5 point] How much can Graffeo increase the wholesale price for the supermarket blend without changing the current optimal solution? What will be the new total profit? Justify your answer.

Answer rating: 100% (QA)

a The decision variables are the number of pounds of each blend to produce The objective function is ... View the full answer