Mikes 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, Mikes Brewery will have the following quantities of raw materials available to them: 580 pounds of corn, 275 ounces of hops, and 1,390 pounds of malt. They also estimate that the contribution to earnings is $15 per barrel of ale and $25 per barrel of beer. Mikes 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 Mikes Brewery. Partial barrel production is allowed (e.g., A=10.25 is acceptable). Mike wants to use a linear programming 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 580 (Corn Resource)

5 A + 4 B 275 (Hops Resource)

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

A, B >= 0 (Nonnegativity)

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) 20.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 20.35. An alternative approach would be to add a new constraint in the formulation: 0.65 A-0.35 B 20 (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 20. Which approach would you use? What are the advantages, if any, of one approach over the other? Solve without implementing in Excel