Question

Manhattan Brewery produces two types of beer: stout and India pale ale (IPA). The stout sells for $70 per barrel and IPA sells for $80 per barrel. Producing a barrel of stout requires 30 pounds of malt and 10 pounds of hops. Producing a barrel of IPA requires 20 pounds of malt and 20 pounds of hops. The brewery has 700 pounds of malt and 500 pounds of hops available. How can Manhattan Brewery earn the most revenue?

a) Formulate an LP model for this problem. Write out the linear optimization problem being solved.

b) Develop a spreadsheet model in EXCEL to solve the problem and solve it.

c) What is the optimal solution?

Answer 1

a) Let X₁ be the quantity of stout produced and X₂ be the quantity of IPA produced.

The objective is to maximize the profit subject to the constraints

Maximize Z = $ 70X₁ + $ 80X₂

Subject to the following constraints

30X₁ + 20X₂ 700

10X₁ + 20X₂ 500

____________________________________________________________________

b) The output of solver is below

	Stout	India Pale Ale	Total	Available capacity
Decision variables	10	20
Contribution	70	80	2300
Pounds of Malt	30	20	700	700
Pounds of Hops	10	20	500	500

______________________________________________________

c)  As per solver, the firm should make 10 units of stout and 20 units of India Pale Ale.