Help with linear programming, statistics homework help

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.

b. Solve the model by using the computer.

SOLUTION

Decisions variables

a)  x₁ = no. of gallons of Yodel

x₂ = no. of gallons of Shotz

x₃ = no. of gallons of Rainwater

maximize Z = 1.50x₁ + 1.60x₂ + 1.25x₃

subject to

x₁ + x₂ + x₃ = 1,000

1.50x₁ + .90x₂ + .50x₃ ≤ 2,000

x₁ ≤ 400

x₂ ≤ 500

x₃ ≤ 300

x₁, x₂, x₃ ≥ 0

I have formulated the problem, but I am not sure how to solve using excel QM. I need to get a sensitivity report and the LP max using QM. I’m not sure how to input the data in QM solver with three variables and three constraints to generate the report correctly.  In addition, I need to make sure I formulated the problem correctly.

Solve and generate using excelQM on the computer.