Transcript MS Excel: Farming

Using MS Excel for LP
•
•
•
•
•
•
•
Collect homework
Roll Call
Review homework
Lecture: Using MS Excel’s Solver
Small Group Exercise
Lecture: More Excel
Assign homework
#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.
#9: The Brewery
Beer
Revenue 5
Corn
5
Hops
2
Ale
2
2
1
available
60
25
#9: The Brewery
Max Z ( revenue ) = 5 x1 + 2 x2
S. T. 5 x1 + 2 x2 <= 60
2 x1 + 1 x2 <= 25
x1, x2 >= 0
30
#9: The Brewery
25
Max Z ( revenue ) = 5 x1 + 2 x2
0, 25 = $50
S. T. 5 x1 + 2 x2 <= 60
2 x1 + 1 x2 <= 25
10, 5 = $60
3
12, 0 = $60
3
12,12.5
x1, x2 >= 0
#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.
#10: The Baker
Chocolate Vanilla
Profit 12
9
Baking 20
40
8 hrs/
time
480 min
eggs
4
1
30
#10: The Baker
Max Z ( profit ) = 12 x1 + 9 x2
S. T. 20 x1 + 40 x2 <= 480
4 x1 + 1 x2 <= 30
x1, x2 >= 0, integers
30
#10: The Baker
To find a point’s coordinates
that aren’t obvious, solve
simultaneous equations.
0,12
??, ??
12
7.5,0
3
3
7.5
24
30
#10: The Baker
20 x1 +
-5(4) x1 + -5 (1) x2 <= -5 (30)
0,12
??, ??
12
7.5,0
3
3
7.5
40 x2 <= 480
24
30
#10: The Baker
20 x1 + 40 x2 = 480
-20 x1 - 5 x2 = -150
0,12
??, ??
12
0
35 x2 = 330
x2 = 330/35 or
7.5,0
= 66/7
3
3
7.5
24
30
#10: The Baker
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
#10: The Baker
Z = 12 x1 + 9 x2
Z - 12 x1 = 9 x2
Z/9 - 12/9 x1 = x2
5, 9 = $141
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.
#11: Woodco
Profit
Oak
Pine
Tables
Chairs
Avail.
40
17
30
15
5
13
150
210
#11: Woodco
Max Z ( profit ) = 40 x1 + 15 x2
S. T. 17 x1 + 5 x2 <= 150
30x1 + 13 x2 <= 210
x1, x2 >= 0, integers
#11: Woodco
Max Z ( profit ) = 40 x1 + 15 x2
S. T. 17 x1 + 5 x2 <= 150
0, 210/13 =
~$240
30x1 + 13 x2 <= 210
7, 0 = $280
x1, x2 >= 0, integers
Using MS Excel’s 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.
Go to MS Excel w/ 9-3cexblank
EX: Chicken Feed
A farmer feeds chickens, maintaining a
particular level of nutrition. Minimize the
cost, using this information:
Corn
Cost/ lb. $4
Protein 3
Starches 3
Fiber
2
Beans
$6
7
6
4
Alfalfa
$3
2
4
7
Req.
40
60
20