Question

Starbright Coffee Shop at the Galleria Mall serves two coffee blends it brews on a daily basis, Pomona and Coastal. Each is a blend of three high-quality coffees from Colombia, Kenya, and Indonesia. The coffee shop has 4.5 pounds of Colombian, 3 pounds of Kenyan, and 5 pounds of Indonesian coffees available each day. Each pound of coffee will produce sixteen 16-ounce cups of coffee. The shop has enough brewing capacity to brew 20 gallons of these two coffee blends each day. Pomona is a blend of 15% Colombian, 25% Kenyan, and 60% Indonesian, while Coastal is a blend of 60% Colombian, 20% Kenyan, and 20% Indonesian. Pomona sells for $2.50 per cup, and Coastal sells for $1.50 per cup. The manager wants to know how many cups of each blend to sell each day in order to maximize revenue.

A. Formulate a linear programming model for this problem - I need the excel spreadsheet and the solver parameters. Please use/show Excel Solver. Thank you!

Answer 1

Assume that P and C pounds of Pomona and Coastal coffee are brewed everyday

The ingredient constraint imply that

0.15P + 0.6C ≤ 4.5 (1)

0.25P + 0.2C ≤ 3 (2)

0.6P + 0.2C ≤ 5 (3)

Total brewing capacity implies that

P + C ≤ 167 (4) (1 gallon = 8.35 pounds)

The revenue function is

R = 16 × (2.5P + 1.5 C) = 40P + 24C

Solution

s