The operations manager for the Blue Moon Brewing Co

Subject:Operations ManagementPrice:4.86 Bought15

Share With

The operations manager for the Blue Moon Brewing Co. produces two beers: Lite (L) and Dark (D). He can only get 675 gallons of malt extract per day for brewing and his brewing hours are limited to 8 hours per day. To produce a keg of Lite beer requires 2 minutes of time and 5 gallons of malt extract. Each keg of Dark beer needs 4 minutes of time and 3 gallons of malt extract. Profits for Lite beer are $3.00 per keg and profits for Dark beer are $2.00 per keg. The brewery's goal is to maximize profits.

 

1. Show the complete linear programming model (1 point). 
2. Using Excel Solver to find the optimal solution and the daily maximum profit? (1 point - Please show your spreadsheet model, and both the answer report and the sensitivity report)
3. How much profit margin of each keg of Lite beer can change without affecting the optimal solution? (0.5 point)
4. If the operations manager can get another 100 gallons of malt extract per day, will it affect the daily  profit?  If your answer is yes, please list how much is the change of the profit; If your answer is no, then please explain why. (0.5 point)

 

Your spreadsheet model can look different from the example (Par. Inc.) in the lecture of Ch. 2 on using Excel Solver, but as long as everything lays out logically and correctly (the demo link will lead you to the video  that will walk you through the process step by step of using the Excel Solver to solve an LP problem).