SOLVER 2

Problems: What if Analysis

Version 1 Lutz Brewery brews three brands of beers: Lutz Lager,Lutz Light, Lutz Ultralight. Lager sells for $12 per barrel, Lightsells for $10 and Ultralight sells for $14 per barrel. Producing abarrel of Lager requires 8 pounds of corn and 4 pounds of hops.Producing a barrel of Light requires 2 pounds of corn, 6 pounds ofrice, and 3 pounds of hops. Producing a barrel of Ultralightrequires 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 4000pounds of hops. To supply demand from established customers, Lutzbrewery should produce at least 200 barrels of each product. a)Assuming a linear relationship, use Excel Solver to determine theoptimal mix of Lager, Light and Ultralight that maximizes LutzBrewery’s revenue. b) Conduct sensitivity analysis and interpretresults.