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).

 

Please refer to the following Excel Solver snapshots:

The decision variables (highlighted in orange color cells) are the number of Kegs of Lite Beer (L) and Dark Beer (D) to be produced

The objective function (highlighted in yellow color cell) is to maximize the total profit

Total Profit = Number of Kegs of Lite Beer * Unit Profit for Lite Beer + Number of Kegs of Dark Beer * Unit Profit for Dark Beer

Total Profit = L * 3 + D * 2

The constraints are:

i) The total quantity of Malt extract utilized should be less than or equal to the total quantity of Malt extract available

L * 5 + D * 3 <= 675

ii) The number of hours of brewing time utilized should be less than or equal to the total brewing time available (8 hours * 60 minutes/hour = 480 minutes)

L * 2 + D * 4 <= 480

iii) All the decision variables are non-negative

L, D >= 0

The Solver Parameters are as follows:

The Optimal Solution is as follows:

Optimal Solution:

Lite Beer = 90 Kegs

Dark Beer = 75 Kegs

Maximum Daily Profit = $420

3) The Sensitivity Report is as follows:

Allowable Increase in the objective coefficient (profit margin) for Lite Beer = $0.333

Allowable Decrease in the objective coefficient (profit margin) for Lite Beer = $2

Current objective coefficient (profit margin) for Lite Beer = $3

Range of Profit Margin of Lite beer for which the optimal solution remains the same = Current value - allowable decrease, Current value + Allowable Increase

Range of Profit Margin of Lite beer for which the optimal solution remains the same = 3 - 2, 3 + 0.333

Range of Profit Margin of Lite beer for which the optimal solution remains the same = $1 to $3.333

4) Shadow price is the increase in the objective function (profit) for every unit increase in the constraint.

This is valid in the range of Allowable Decrease and Allowable Increase.

Shadow Price for Malt extract = $0.571

Increase in Malt Extract quantity = 100 gallons

(This is within the Allowable Increase limit of 525 gallons)

Increase in Profit = Shadow Price * Increase in Malt extract quantity

Increase in Profit = 0.571 * 100

Increase in Profit = $57.1

Please see the attached file for the complete solution