Scenario: Using excels analytic solver…Zahara Blends, a growing beauty brand specializing in…

Scenario: Using excels analytic solver…Zahara Blends, a growing beauty brand specializing in customizable hair oils, is planning a small-batch launch of premium blends. You’ve developed seven unique formulations, each with different profit margins, production costs, labor needs, and packaging requirements.Due to startup constraints, you have a maximum budget of $5,000,150 hours of available labor, and 250 bottles in stock. Your goal is to maximize total profit by selecting up to 4 products for launch. However, you must also respect brand strategy rules, such as avoiding product overlap, ensuring scent diversity, and aligning luxury blends with your base oils.
Decision Variables:
Yi: 1 if product i is selected for launch
Yi: 0 Otherwise
Decision Variables Table
Product
Product Name
Decision Variable
1
Argan Rosemary
Y1
2
Coconut Rosemary
Y2
3
Castor Peppermint
Y3
4
Jojoba Eucalptus
Y4
5
Argan Rose
Y5
6
Rosewater Hydration Mix
Y6
7
Luxury Gold Blend
Y7
Parameters of the Problem
Product Parameters
Product
Profit
Cost($)
Labor (hrs)
Bottles
Y1
120
750
20
40
Y 2
100
650
18
35
Y3
90
500
15
30
Y4
85
450
12
25
Y5
125
800
22
40
Y6
105
700
20
35
Y7
140
1000
30
45
Objective Function
Max: 120y1 100y2 90y3 85y4 125y5 105y6 140y7
Constraints
Budget:750y1 650y2 500y3 450y4 800y5 700y6 1000y7≤5000
Labor hours constraints:20y1 18y2 15y3 12y4 22y5 20y6 30y7≤150
Bottle supply constraint:40y1 35y2 30y3 25y4 40y5 35y6 45y7≤250
Max profit limit(only 4 products can be launched):y1 y2 y3 y4 y5 y6 y7≤4
Logical Constraints:
Avoid overlap (Argan vs Coconut Rosemary):y1 y2≤1
At least one floral scent product must be selected (Rose, Rosewater):y5 y6≥1
Luxury Gold Blend can only be launched if Argan-based product is selected:y7≤y1 y5