Using MS Excel for LP

1
Using MS Excel for LP

• Collect homework
• Roll Call
• Review homework
• Lecture Using MS Excels Solver
• Small Group Exercise
• Lecture More Excel
• Assign homework

2
9 The Brewery

• The brewery produces beer and ale. Beer sells for
  5 per barrel, and ale for 2/barrel. Producing
  a barrel of beer requires 5 pounds of corn and 2
  pounds of hops. Producing a barrel of ale
  requires 2 pounds of corn and 1 pound of hops.
  The brewery has 60 pounds of corn and 25 pounds
  of hops.

3
9 The Brewery
4
9 The Brewery
Max Z ( revenue ) 5 x1 2 x2 S. T. 5 x1 2 x2
lt 60 2 x1 1 x2 lt 25 x1, x2 gt 0
5
9 The Brewery
30
Max Z ( revenue ) 5 x1 2 x2 S. T. 5 x1 2 x2
lt 60 2 x1 1 x2 lt 25 x1, x2 gt 0
25
0, 25 50
10, 5 60
3
12, 0 60
3
12
,12.5
6
10 The Baker

• A gourmet cook bakes two types of cake, chocolate
  and vanilla, to supplement her income. Each
  chocolate cake can be sold for 12, and each
  vanilla cake can be sold for 9. Each chocolate
  cake requires 20 minutes of baking time and uses
  4 eggs. Each vanilla cake requires 40 minutes of
  baking time and uses 1 egg. The baker has 8 hours
  of baking time and 30 eggs.

7
10 The Baker
8
10 The Baker
Max Z ( profit ) 12 x1 9 x2 S. T. 20 x1 40
x2 lt 480 4 x1 1 x2 lt 30 x1, x2 gt
0, integers
9
10 The Baker
30
To find a points coordinates that arent
obvious, solve simultaneous equations.
0,12
??, ??
12
7.5,0
3
3
7.5
24
10
10 The Baker
30
20 x1 40 x2 lt 480 -5(4) x1 -5 (1)
x2 lt -5 (30)
0,12
??, ??
12
7.5,0
3
3
7.5
24
11
10 The Baker
30
20 x1 40 x2 480 -20 x1 - 5 x2
-150 0 35 x2 330 x2
330/35 or 66/7
0,12
??, ??
12
7.5,0
3
3
7.5
24
12
10 The Baker
30
Isoprofit lines Solve the optimization equation
for x2 to find the slope of the profit line.
0,12
36/7, 66/7
12
7.5,0
3
3
7.5
24
13
10 The Baker
Z 12 x1 9 x2 Z - 12 x1 9 x2 Z/9 - 12/9 x1
x2
5, 9 141
14
11. Woodco

• ...tables and chairs. Each table and chair must
  be made entirely out of oak or entirely out of
  pine. A total of 150 board feet of oak and 210
  board feet of pine are available. A table
  requires either 17 board feet of oak or 30 board
  feet of pine. A chair requires either 5 board
  feet of oak or 13 board feet of pine. Each table
  sells for 40, and each chair for 15.

15
11 Woodco
16
11 Woodco
Max Z ( profit ) 40 x1 15 x2 S. T. 17 x1
5 x2 lt 150 30x1 13 x2 lt 210 x1,
x2 gt 0, integers
17
11 Woodco
Max Z ( profit ) 40 x1 15 x2 S. T. 17 x1
5 x2 lt 150 30x1 13 x2 lt 210 x1,
x2 gt 0, integers
WRONG - FOLLOW ME
0, 210/13 240
7, 0 280
18
Using MS Excels Solver

• EX using the Woodco example.
• Create the table of information.
• Set up formulas to relate the information to be
  optimized.
• Set up solver to list constraints.
• Run solver.

19
Go to MS Excel w/ 9-3cexblank
20
EX Chicken Feed

• A farmer feeds chickens, maintaining a particular
  level of nutrition. Minimize the cost, using
  this information