A coffee bean company blends four types of beans into three blends of coffee. One blend is sold to hotels, another to restaurants and a third to supermarkets. The company has four bean suppliers who independently supply the four different bean types. Below is a summary of the blend recipes, the cost and availability information for the different bean types. The percentages indicate the fraction of each bean type used in each blend.

The processor’s plant can handle no more than 100,000 pounds per week and the company would like to operate at capacity. There is no problem selling the final blends although the marketing department requires minimum production levels of 10,000, 25,000, and 30,000 pounds for the hotel, restaurant and market blends respectively.

a) Formulate a Linear Programming model for the problem to maximize the weekly profit. Define clearly all of your decision variables, objective and constraints

b) Solve the model using Excel and report the optimal amounts of each bean type to be purchased

c) If there is a chance to increase the bean availability for any of the four types by 1000 pound, which one would you select? Explain your answer based on the solution you obtained above without resolving the model

d) If the marketing department would like to increase the minimum production level by 500 pounds for one of the blends, which one would you recommend? Explain your answer based on the solution you obtained above without resolving the model

composition 

Bean type	hotel	restaurant	supermarket	cost/lb.	weekly availability
1	20%	35%	10%	0.60	40,000
2	40%	15%	35%	0.80	25,000
3	15%	20%	40%	0.55	20,000
4	25%	30%	15%	0.70	45,000

Whole sale price/Ib  1.25    1.50    1.40