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 X₁ decision variable at the optimal solution?

Select one:

a. X₁ = 50

b. X₁ = 50 gallons

c. X₁ = 75

d. X₁ = 100

e. X₁ = 150

f. X₁ = 0

g. X₁ = 0 gallons

h. X₁ = 150 gallons

i. X₁ = 75 gallons

j. X₁ = 100 gallons

Please show me how to run in Excel in detail. I really want to understand how to do via Excel. Thank you. ASAP

Answer 1

Formula:

Steps:data->solver

is the output.

Maximum Profit:41.25 dollars

X1=0,X2=300,X3=75

Also,Milk=180 gallons

Sugar=150 gallons

Cream=60 gallons.

please rate my answer and comment for doubts.