Question:

Mike’s Brewery is a small local brewery whose ale and beer are always in demand but whose production is limited by certain raw materials that are in short supply. (Ale is a type of alcoholic beverage, similar to but heavier than beer.) The scarce materials are corn, hops, and barley malt (all of special high quality). A barrel of ale calls for different amounts of ingredients from a barrel of beer as follows:

RAW MATERIAL REQUIREMENTS

Raw material Ale (per barrel) Beer (per barrel)

Corn 6 pounds 14 pounds

Hops 5 ounces 4 ounces

Malt 35 pounds 20 pounds

For each day in the near future, Mike’s Brewery will have the following quantities of raw materials available to them: 480 pounds of corn, 175 ounces of hops, and 1,190 pounds of malt. They also estimate that the contribution to earnings is $15 per barrel of ale and $25 per barrel of beer. Mike’s Brewery believes that it can sell all the beverages it produces.

Let A and B be the daily production level of ale and beer (in barrels) at Mike’s Brewery. Partial barrel production is allowed (e.g., A=10.25 is acceptable). Mike wants to use a linear optimization model to solve this problem. The following linear optimization model will determine the best production plan:

Max 15 A + 25 B (contribution to earnings)

Subject to: 6 A + 14 B 480 (Corn Resource)

5 A + 4 B 175 (Hops Resource)

35A + 20 B 1,190 (Malt Resource)

A, B 0 (Nonnegativity)

1. [10 points] What is the optimal production plan? What is the optimal contribution to earnings? (Hint: implement in Excel)
2. [10 points] Suppose that a new marketing analysis has shown that at least 35% of the drinks Mike’s Brewery makes should be ale. One way to incorporate this change is to add a new constraint in the formulation:
   A/(A+B) 0.35 (Product Mix)
   At an unused cell location (say, B20) in the original spreadsheet file, calculate the LHS of this new constraint: B20 = C9/(C9+D9), where C9 and D9 are the cells representing variables A and B, respectively, and then add a new constraint under Solver:
   B20 0.35.
   An alternative approach would be to add a new constraint in the formulation:
   0.65 A – 0.35 B 0 (Product Mix)
   At an unused cell location (say, B20) in the original spreadsheet file, calculate the LHS of this constraint: B20 = 0.65*C9 – 0.35 * D9 and then add a new constraint under Solver:
   B20 0.
   Which approach would you use? What are the advantages, if any, of one approach over the other?
3. [10 points] Suppose that the brewing process at Mike’s Brewery requires that both types of beverages are made in batches of 1 barrel, i.e., A and B can only be assigned values 0, 1, 2, 3, …. What changes would have to be made in the formulation to reflect this requirement? How would you change the spreadsheet implementation?
4. [10 points] Instead of the situation in part (c), now suppose that the brewing process at Mike’s Brewery requires that both types of beverages are made in batches of 3 barrels, i.e., A and B can only be assigned values 0, 3, 6, 9, …. What changes would have to be made in the formulation to reflect this requirement? How would you change the spreadsheet implementation?

Price: $2.99

Solution: The downloadable solution consists of 5 pages
Deliverable: Word Document