In: Math
Helena Lorimer runs a set of ice cream cafes that sell mainly three flavors of ice cream: vanilla, chocolate, and strawberry. Hot weather and high demand have caused her to run short of the main ingredients: milk, sugar, and cream. She has decided to make the best assortment of ice cream quantities in these three flavors and ration out the deliveries to the cafes.
She has collected data on the profitability of the various flavors, availability of supplies, and the amounts of ingredients required for each flavor.
Flavor |
Profit per Gallon |
Usage/Gallon |
||
Milk (gal.) |
Sugar (lbs.) |
Cream (gal.) |
||
Chocolate |
$1.00 |
0.45 |
0.50 |
0.10 |
Vanilla |
$0.90 |
0.50 |
0.40 |
0.15 |
Strawberry |
$0.95 |
0.40 |
0.40 |
0.20 |
Max available |
200 |
150 |
60 |
She wants to determine the optimal product mix for the Lorimer ice cream.
Let x1 = the # of gallons of Chocolate ice cream made
x2 = the # of gallons of Vanilla ice cream made
x3 = the # of gallons of Strawberry ice cream made
Max Z = $1.00x1 + $0.90x2 + $0.95x3
Subject To:
0.45x1 + 0.50x2 + 0.40x3 |
≤ 200 gal |
Milk Supply Constraint |
0.50x1 + 0.40x2 + 0.40x3 |
≤ 150 lbs |
Sugar Supply Constraint |
0.10x1 + 0.15x2 + 0.20x3 |
≤ 60 gal |
Cream Supply Constraint |
x1 |
≥ 0 gallons |
x1 Non-negativity Constraint |
x2 |
≥ 0 gallons |
x2 Non-negativity Constraint |
x3 |
≥ 0 gallons |
x3 Non-negativity Constraint |
Use your Excel spreadsheet model to answer the following question. Select the answer that best fits what you got. The answer options are not in any particular order.
What is the value of the X2 decision variable at the optimal solution?
Select one:
a. X2 = 275 gallons
b. X2 = 220
c. X2 = 275
d. X2 = 220 gallons
e. X2 = 120
f. X2 = 300 gallons
g. X2 = 200
h. X2 = 300
i. X2 = 200 gallons
j. X2 = 120 gallons
Prepare the following sheet in Excel
get this
set up the solver using data--->solver
get this
the value of the X2 decision variable at the optimal solution is
ans: f. X2 = 300 gallons