Problem 1: Product Mix Optimization (Linear Programming)
This problem presents Product Mix and Waste Management .
e) System of Equations and LP Models
Define Decision Variables :
x₁ = Number of Regular Coffee cups sold per day
x₂ = Number of Cappuccino cups sold per day
x₃ = Number of Latte cups sold per day
x₄ = Number of Pastries pieces sold per day
x₅ = Number of Sandwiches pieces sold per day
SCENARIO A: MAXIMIZE DAILY PROFIT
Maximize Z = 2.30x₁ + 2.70x₂ + 2.50x₃ + 2.50x₄ + 3.50x₅
Subject to Constraints:
0.25x₂ + 0.30x₃ + 0.05x₄ ≤ 35 (Milk constraint, in liters)
18x₁ + 22x₂ + 20x₃ ≤ 12,000 (Coffee beans constraint, in grams)
80x₄ + 120x₅ ≤ 8,000 ( Flour constraint, in grams)
5x₁ + 8x₂ + 10x₃ + 20x₄ + 5x₅ ≤ 5,000 (Sugar constraint, in grams)
3x₁ + 7x₂ + 6x₃ + 12x₄ + 15x₅ ≤ 900 (Labor constraint, in minutes)
1.20x₁ + 2.80x₂ + 2.50x₃ + 2.00x₄ + 3.50x₅ ≤ 1,000 (Budget constraint, in dollars)
x₁ + x₂ + x₃ + x₄ + x₅ ≤ 500 (Storage constraint, in units)
x₁, x₂, x₃, x₄, x₅ ≥ 0 (Non-negativity constraint)
SCENARIO B: MINIMIZE TOTAL INGREDIENT USAGE (Waste Reduction)
Objective Function:
Minimize W = 0.05x₁ + 0.10x₂ + 0.09x₃ + 0.08x₄ + 0.12x₅
Subject to all constraints from Scenario A, PLUS:
2.30x₁ + 2.70x₂ + 2.50x₃ + 2.50x₄ + 3.50x₅ ≥ 300 (Minimum profit constraint)
Now, Excel Solver Setup
f) Excel Solver Result:
Scenario A Maximize Profit :
Product Optimal Units/Day Profit Contribution
Regular Coffee(x1) 80 cups $184.00
Cappuccino (x2) 30 cups $81.00
Latte (x3) 20 cups $50.00
Pastries (x4) 15 pieces $37.50
Sandwiches(x5) 10 pieces $35.00
Total Daily Profit $387.50
Scenario B Minimize Waste:
Product Optimal Units/Day Profit Contribution
Regular Coffee(x1) 50 cups $115.00
Cappuccino (x2) 30 cups $81.00
Latte (x3) 20 cups $50.00
Pastries (x4) 15 pieces $37.50
Sandwiches(x5) 10 pieces $35.00
Total Daily Profit $318.50