Betty Malloy owner of the Eagle Tavern in Pittsburgh is prep

Betty Malloy, owner of the Eagle Tavern in Pittsburgh, is preparing for Super Bowl Sunday, and she must determine how much beer to stock. Betty stocks three brands of beer- Yodel, Shotz, and Rainwater. The cost per gallon (to the tavern owner) of each brand is as follows Brand Cost/Gallon Yodel $1.50 Shotz 0.90 Rainwater 0.50 The tavern has a budget of $2,000 for beer for Super Bowl Sunday. Betty sells Yodel at a rate of $3.00 per gallon, Shotz at $2.50 per gallon, and Rainwater at $1.75 per gallon. Based on past football games, Betty has determined the maximum customer demand to be 400 gallons of Yodel, 500 gallons of Shotz, and 300 gallons of Rainwater. The tavern has the capacity to stock 1,000 gallons of beer; Betty wants to stock up completely. Betty wants to determine the number of gallons of each brand of beer to order so as to maximize profit. a. Formulate a linear programming model for this problem. THIS MUST BE ANSWERED IN EXCEL USING SOLVER PLEASE ATTACH YOUR EXCEL SPREADSHEET

Solution

We take 3 variables, for the number of gallons of each brand of beer. I\'ll call these variables Y S and R

Second, construct your objective function.

Profit = selling price - cost price

Maximize 1.50x + 1.60y + 1.25z
a budget of only $2000 to buy the beer, and capacity to stock 1000 gallons

constraint is just x+y+z<=1000.

The budget constraint comes from recognizing that you use up your resource (the $2000 budget) at different rates for the various beers:

1.50x + 0.90y + 0.50z <= 2000

x<= 400 ; y <= 500 ; z <= 300

Now solve these inequalties using simplex method :

Tableau #1
x y z s1 s2 s3 s4 s5 p   
1 1 1 1 0 0 0 0 0 1000   
1.5 0.9 0.5 0 1 0 0 0 0 2000   
1 0 0 0 0 1 0 0 0 400
0 1 0 0 0 0 1 0 0 500
0 0 1 0 0 0 0 1 0 300
-1.5 -1.6 -1.25 0 0 0 0 0 1 0

Tableau #2
x y z s1 s2 s3 s4 s5 p   
1 0 1 1 0 0 -1 0 0 500
1.5 0 0.5 0 1 0 -0.9 0 0 1550   
1 0 0 0 0 1 0 0 0 400
0 1 0 0 0 0 1 0 0 500
0 0 1 0 0 0 0 1 0 300
-1.5 0 -1.25 0 0 0 1.6 0 1 800

Tableau #3
x y z s1 s2 s3 s4 s5 p   
0 0 1 1 0 -1 -1 0 0 100
0 0 0.5 0 1 -1.5 -0.9 0 0 950
1 0 0 0 0 1 0 0 0 400
0 1 0 0 0 0 1 0 0 500
0 0 1 0 0 0 0 1 0 300
0 0 -1.25 0 0 1.5 1.6 0 1 1400   

Tableau #4
x y z s1 s2 s3 s4 s5 p   
0 0 1 1 0 -1 -1 0 0 100
0 0 0 -0.5 1 -1 -0.4 0 0 900
1 0 0 0 0 1 0 0 0 400
0 1 0 0 0 0 1 0 0 500
0 0 0 -1 0 1 1 1 0 200
0 0 0 1.25 0 0.25 0.35 0 1 1525   

Optimal Solution: p = 1525; x = 400, y = 500, z = 100

Maximum profit = $ 1525