Lutz Brewery brews three brands of beers: Lutz Lager, Lutz Light, Lutz Ultralight. Lager sells for $12 per barrel, Light sells for $10 and Ultralight sells for $14 per barrel. Producing a barrel of Lager requires 8 pounds of corn and 4 pounds of hops. Producing a barrel of Light requires 2 pounds of corn, 6 pounds of rice, and 3 pounds of hops. Producing  a barrel of Ultralight requires 1 pounds of corn, 8 pounds of rice, and 2 pounds of hops. The brewery has 5000 pounds of corn, 3000 pounds of rice, and 4000 pounds of hops. To supply demand from established customers, Lutz brewery should produce at least 200 barrels of each product.

 

 

a) Assuming a linear relationship, use Excel Solver to determine the optimal mix of Lager, Light and Ultralight that maximizes Lutz Brewery’s revenue.

 

 

b) Conduct sensitivity analysis and interpret results.

 

Mrs. Moody sells cookies that she bakes in her kitchen and gives the profits to charities. She sells two types of cookies – regular chocolate chip and extra chocolate chip. To bake a pound of regular chocolate chip cookies, she uses two eggs, 0.15 lbs. of chocolate chips, 0.20 lbs. of sugar, 0.40 lbs. of butter, and 0.25 lbs. of flour. To bake a pound of extra chocolate chip cookies, she uses two eggs, 0.25 lbs. of chocolate chips, 0.15 lbs. of sugar, 0.35 lbs. of butter, and 0.25 lbs. of flour. She makes a $7 profit on a pound of regular chocolate chip cookies and $9 profit on extra chocolate chip cookies. She purchased six dozen (72) eggs, eight lbs. of chocolate chips, seven lbs. of sugar, 15 lbs. of butter, and 10 lbs of flour. How much should she bake of each type of cookie to maximize her profit?

    • 10 years ago
    Excel Linear Programming Answers - $5 off for your next assignment if you don't get the full 70

    Purchase the answer to view it

    blurred-text
    • attachment
      linear_programming.xlsx