Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved 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

b. [1 point] Use the sheet named Q1 in the solution template and setup your model in Excel Solver. Then solve it using ExceInput Data Roasting Capacity (lbs) 100000 Component Robusta Javan Arabica Liberica Brazilian Arabica Price per lb Minimum des

In answering the following questions, use Solvers sensitivity report to give exact numerical answers whenever possible witho 

 

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.

Step by Step Solution

3.33 Rating (165 Votes )

There are 3 Steps involved in it

Step: 1

a The decision variables are the number of pounds of each blend to produce The objective function is ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Management Science The Art Of Modeling With Spreadsheets

Authors: Stephen G. Powell, Kenneth R. Baker

4th Edition

978-1118517376, 9781118800348, 1118517377, 1118800346, 978-1118582695

More Books

Students also viewed these Accounting questions

Question

Explain the process of MBO

Answered: 1 week ago