Question

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       x₁ = the # of gallons of Chocolate ice cream made

            x₂ = the # of gallons of Vanilla ice cream made

            x₃ = the # of gallons of Strawberry ice cream made

Max Z =    $1.00x₁ + $0.90x₂ + $0.95x₃   

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. X₂ = 275 gallons

b. X₂ = 220

c. X₂ = 275

d. X₂ = 220 gallons

e. X₂ = 120

f. X₂ = 300 gallons

g. X₂ = 200

h. X₂ = 300

i. X₂ = 200 gallons

j. X₂ = 120 gallons

Answer 1

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. X₂ = 300 gallons