Seminar 3:
MS Excel Solver, duality of LP and sensitivity analysis
Problem 1: Solve Problem 1 of Seminar 1 using computer.
Problem 2: Use Excel Solver to check the solution of the problem 1a) of the
seminar 2
z = 36x1 + 12x2 + 60x3 → max.
subject to
2x1 + x2 + 3x3 ≤ 9
x1 + 3x3 ≤ 3
x1, x2, x3 ≥ 0
Supplementary Tasks:
• Find intervals of stability for prices and capacities in Solver Sensitivity
report.
• Formulate the dual problem.
• Find the solution of the dual in the Solver Reports.
• Find the solution of the dual in the simplex table.
• Solve the dual problem and compare the optimal values of primal and dual
problems.
Problem 3: (Hillier and Lieberman, 6.7-18.)
Ken and Larry, Inc., supplies its ice cream parlors with three flavors of ice cream:
chocolate, vanilla, and banana. Because of extremely hot weather and a high
demand for its products, the company has run short of its supply of ingredients:
milk, sugar, and cream. Hence, they will not be able to fill all the orders received
from their retail outlets, the ice cream parlors. Owing to these circumstances, the
company has decided to choose the amount of each flavor to produce that will
maximize total profit, given the constraints on supply of the basic ingredients.
The chocolate, vanilla, and banana flavors generate, respectively, $1.00, $0.90,
and $0.95 of profit per gallon sold. The company has only 200 gallons of milk,
150 pounds of sugar, and 60 gallons of cream left in its inventory. The technology
of the production is described by the table
chocolate vanilla banana
milk 0.45 0.5 0.4
sugar 0.5 0.4 0.4
cream 0.1 0.15 0.2
For each of the following parts, answer the question using sensitivity report in
the Excel Solver. Note: Each part is independent (i.e., any change made to the
model in one part does not apply to any other parts).
a) What is the optimal solution and total profit?
b) Suppose the profit per gallon of banana changes to $1.00. Will the optimal
solution change, and what can be said about the effect on total profit?
c) Suppose the profit per gallon of banana changes to 92 cents. Will the optimal
solution change, and what can be said about the effect on total profit?
d) Suppose the company discovers that 3 gallons of cream have gone sour and
so must be thrown out. Will the optimal solution change, and what can be
said about the effect on total profit?
e) Suppose the company has the opportunity to buy an additional 15 pounds
of sugar at a total cost of $15. Should they? Explain.