Using Spreadsheets for Linear Programming with The Simplex Method

1
Using Spreadsheets forLinear Programming
withThe Simplex Method
By Jeffrey Bivin Lake Zurich High School
A sample problem
Last Updated October 11, 2005
2

• A landscaper can buy three types of 100-pound
  bags of fertilizer, type A, type B, and type C.
  Each 100-pound bag of type A fertilizer costs 20
  and contains 40 pounds of nitrogen, 30 pounds of
  phosphoric acid, and 10 pounds of potash. Each
  100-pound bag of type B fertilizer costs 30 and
  contains 20 pounds of nitrogen, 20 pounds of
  phosphoric acid, and 55 pounds of potash. Each
  100-pound bag of type C fertilizer costs 20 and
  contains no nitrogen, 30 pounds of phosphoric
  acid, and 40 pounds of potash. The landscaper
  requires 4000 pounds of nitrogen. 2000 pounds of
  phosphoric acid, and 2000 pounds of potash. How
  many bags of each type of fertilizer should the
  landscaper buy in order to minimize the cost?
  Also, find the minimum cost.

Jeff Bivin -- LZHS
3
Analyzing the problem
Nitrogen Phos. Acid potash
Type A x1 20 40 30 10
Type B x2 30 20 20 55
Type C x3 20 0 30 40
needs 4000 2000 2000

Jeff Bivin -- LZHS
4
Define the Constraints

• 40x1 20x2 gt 4000
• 30x1 20x2 30x3 gt 2000
• 10x1 55x2 40x3 gt 2000
• x1 gt 0
• x2 gt 0
• x3 gt 0

Jeff Bivin -- LZHS
5
Function to minimize

• z 20x1 30x2 20x3

Jeff Bivin -- LZHS
6
Initial Tableau
x1 x2 x3 s1 s2 s3 z Values
40 20 0 -1 0 0 0 4000
30 20 30 0 -1 0 0 2000
10 55 40 0 0 -1 0 2000
20 30 20 0 0 0 1 0
Jeff Bivin -- LZHS
7
Initial Tableau Analysis
Jeff Bivin -- LZHS
8
A negative basic variable in s1leads us to use
column x1 as the pivot column.
x1 x2 x3 s1 s2 s3 z Values
40 20 0 -1 0 0 0 4000
30 20 30 0 -1 0 0 2000
10 55 40 0 0 -1 0 2000
20 30 20 0 0 0 1 0
Jeff Bivin -- LZHS
9
A negative basic variable in s1leads us to use
column x1 as the pivot column.
x1 x2 x3 s1 s2 s3 z Values
40 20 0 -1 0 0 0 4000
30 20 30 0 -1 0 0 2000
10 55 40 0 0 -1 0 2000
20 30 20 0 0 0 1 0
s1 is a basic variable because it has only one
non-zero element in the column
Jeff Bivin -- LZHS
10
A negative basic variable in s1leads us to use
column x1 as the pivot column.
x1 x2 x3 s1 s2 s3 z Values
40 20 0 -1 0 0 0 4000
30 20 30 0 -1 0 0 2000
10 55 40 0 0 -1 0 2000
20 30 20 0 0 0 1 0
We use Column x1 as the pivot column because the
40 in column x1 is the left most positive number
in the row with the -1 value (negative basic
variable).
Jeff Bivin -- LZHS
11
Divide the value column by the pivot column.This
adds an additional column at the right.
x1 x2 x3 s1 s2 s3 z Values
40 20 0 -1 0 0 0 4000 100
30 20 30 0 -1 0 0 2000 66.667
10 55 40 0 0 -1 0 2000 200
20 30 20 0 0 0 1 0
Jeff Bivin -- LZHS
12
66.667 is the lowest non-negative quotient.
x1 x2 x3 s1 s2 s3 z Values
40 20 0 -1 0 0 0 4000 100
30 20 30 0 -1 0 0 2000 66.667
10 55 40 0 0 -1 0 2000 200
20 30 20 0 0 0 1 0
Jeff Bivin -- LZHS
13
66.667 is the lowest non-negative
quotient.Therefore, 30 is determined to be the
pivot point.
x1 x2 x3 s1 s2 s3 z Values
40 20 0 -1 0 0 0 4000 100
30 20 30 0 -1 0 0 2000 66.667
10 55 40 0 0 -1 0 2000 200
20 30 20 0 0 0 1 0
Jeff Bivin -- LZHS
14
Determine the row operationsrow1 3 x row1
4 x row2row3 3 x row3 row2row4 3 x row4
2 x row2
x1 x2 x3 s1 s2 s3 z Values
40 20 0 -1 0 0 0 4000 100
30 20 30 0 -1 0 0 2000 66.667
10 55 40 0 0 -1 0 2000 200
20 30 20 0 0 0 1 0
Jeff Bivin -- LZHS
15
Perform the row operations to determine Tableau 2
x1 x2 x3 s1 s2 s3 z Values
r13r1-4r2 0 -20 -120 -3 4 0 0 4000
r2 30 20 30 0 -1 0 0 2000
r33r3-r2 0 145 90 0 1 -3 0 4000
r43r4-2r2 0 50 0 0 2 0 3 -4000
Jeff Bivin -- LZHS
16
2nd Tableau Analysis
Jeff Bivin -- LZHS
17
A negative basic variable in s1leads us to use
column s2 as the pivot column.
x1 x2 x3 s1 s2 s3 z Values
0 -20 -120 -3 4 0 0 4000
30 20 30 0 -1 0 0 2000
0 145 90 0 1 -3 0 4000
0 50 0 0 2 0 3 -4000
Jeff Bivin -- LZHS
18
Divide the value column by the pivot
column.This adds an additional column at the
right.
x1 x2 x3 s1 s2 s3 z Values
0 -20 -120 -3 4 0 0 4000 1000
30 20 30 0 -1 0 0 2000 -2000
0 145 90 0 1 -3 0 4000 4000
0 50 0 0 2 0 3 -4000
Jeff Bivin -- LZHS
19
1000 is the lowest non-negative quotient.
x1 x2 x3 s1 s2 s3 z Values
0 -20 -120 -3 4 0 0 4000 1000
30 20 30 0 -1 0 0 2000 -2000
0 145 90 0 1 -3 0 4000 4000
0 50 0 0 2 0 3 -4000
Jeff Bivin -- LZHS
20
1000 is the lowest non-negative
quotient.Therefore, 4 is determined to be the
pivot point.
x1 x2 x3 s1 s2 s3 z Values
0 -20 -120 -3 4 0 0 4000 1000
30 20 30 0 -1 0 0 2000 -2000
0 145 90 0 1 -3 0 4000 4000
0 50 0 0 2 0 3 -4000
Jeff Bivin -- LZHS
21
Determine the row operationsrow2 4 x row2
row1row3 4 x row3 row1row4 2 x row4
row1
x1 x2 x3 s1 s2 s3 z Values
0 -20 -120 -3 4 0 0 4000
30 20 30 0 -1 0 0 2000
0 145 90 0 1 -3 0 4000
0 50 0 0 2 0 3 -4000
Jeff Bivin -- LZHS
22
Perform the row operations to determine Tableau 3
x1 x2 x3 s1 s2 s3 z Values
r1 0 -20 -120 -3 4 0 0 4000
r24r2r1 120 60 0 -3 0 0 0 12000
r34r3-r1 0 600 480 3 0 -12 0 12000
r42r4-r1 0 120 120 3 0 0 6 -12000
Jeff Bivin -- LZHS
23
3rd Tableau Analysis
Jeff Bivin -- LZHS
24
A negative basic variable in s3leads us to use
column x2 as the pivot column.
x1 x2 x3 s1 s2 s3 z Values
r1 0 -20 -120 -3 4 0 0 4000
r24r2r1 120 60 0 -3 0 0 0 12000
r34r3-r1 0 600 480 3 0 -12 0 12000
r42r4-r1 0 120 120 3 0 0 6 -12000
Jeff Bivin -- LZHS
25
Divide the value column by the pivot
column.This adds an additional column at the
right.
x1 x2 x3 s1 s2 s3 z Values
r1 0 -20 -120 -3 4 0 0 4000 -200
r24r2r1 120 60 0 -3 0 0 0 12000 200
r34r3-r1 0 600 480 3 0 -12 0 12000 20
r42r4-r1 0 120 120 3 0 0 6 -12000
Jeff Bivin -- LZHS
26
66.667 is the lowest non-negative quotient.
x1 x2 x3 s1 s2 s3 z Values
r1 0 -20 -120 -3 4 0 0 4000 -200
r24r2r1 120 60 0 -3 0 0 0 12000 200
r34r3-r1 0 600 480 3 0 -12 0 12000 20
r42r4-r1 0 120 120 3 0 0 6 -12000
Jeff Bivin -- LZHS
27
66.667 is the lowest non-negative
quotient.Therefore, 30 is determined to be the
pivot point.
x1 x2 x3 s1 s2 s3 z Values
r1 0 -20 -120 -3 4 0 0 4000 -200
r24r2r1 120 60 0 -3 0 0 0 12000 200
r34r3-r1 0 600 480 3 0 -12 0 12000 20
r42r4-r1 0 120 120 3 0 0 6 -12000
Jeff Bivin -- LZHS
28
Determine the row operationsrow1 30 x row1
row3row2 10 x row2 row3row4 5 x row4
row3
x1 x2 x3 s1 s2 s3 z Values
0 -20 -120 -3 4 0 0 4000
120 60 0 -3 0 0 0 12000
0 600 480 3 0 -12 0 12000
0 120 120 3 0 0 6 -12000
Jeff Bivin -- LZHS
29
Perform the row operations to determine Tableau 4
x1 x2 x3 s1 s2 s3 z Values
r130r1r3 0 0 -3120 -87 120 -12 0 132000
r210r2-r3 1200 0 -480 -33 0 12 0 108000
r3 0 600 480 3 0 -12 0 12000
r452r4-r3 0 0 120 12 0 12 30 -72000
Jeff Bivin -- LZHS
30
4th Tableau Analysis
Jeff Bivin -- LZHS
31
Now we can determine the solutions
x1 x2 x3 s1 s2 s3 z Values
0 0 -3120 -87 120 -12 0 132000
1200 0 -480 -33 0 12 0 108000
0 600 480 3 0 -12 0 12000
0 0 120 12 0 12 30 -72000
Basic Variables x1, x2, and s2 are all positive
and the bottom row has all positive variable
coefficients
Jeff Bivin -- LZHS
32
Determining the solutionsfrom row 1 s2
132000 / 120 1100from row 2 x1 108000 /
1200 90from row 3 x2 12000 / 600 20from
row 4 z -(-72000 / 30) 2400also x3 s1
s3 0
x1 x2 x3 s1 s2 s3 z Values
r130r1r3 0 0 -3120 -87 120 -12 0 132000
r210r2-r3 1200 0 -480 -33 0 12 0 108000
r3 0 600 480 3 0 -12 0 12000
r452r4-r3 0 0 120 12 0 12 30 -72000
Jeff Bivin -- LZHS
33
Results

• x1 90
• x2 20
• x3 0
• s1 0
• s2 1100
• s3 0
• z 2400

Jeff Bivin -- LZHS
34
Process CompleteAnswer the Question

• The minimum cost is 2400 when you order
• 90 bags of type A fertilizer
• 20 bags of type B fertilizer
• 0 bags of type C fertilizer

Jeff Bivin -- LZHS
35
A Quick Recap
Jeff Bivin -- LZHS
36
Initial Tableau
x1 x2 x3 s1 s2 s3 z Values
40 20 0 -1 0 0 0 4000 100
30 20 30 0 -1 0 0 2000 66.667
10 55 40 0 0 -1 0 2000 200
20 30 20 0 0 0 1 0
Jeff Bivin -- LZHS
37
Perform the row operations to determine Tableau 2
x1 x2 x3 s1 s2 s3 z Values
r13r1-4r2 0 -20 -120 -3 4 0 0 4000 1000
r2 30 20 30 0 -1 0 0 2000 -2000
r33r3-r2 0 145 90 0 1 -3 0 4000 4000
r43r4-2r2 0 50 0 0 2 0 3 -4000
Jeff Bivin -- LZHS
38
Perform the row operations to determine Tableau 3
x1 x2 x3 s1 s2 s3 z Values
r1 0 -20 -120 -3 4 0 0 4000 -200
r24r2r1 120 60 0 -3 0 0 0 12000 200
r34r3-r1 0 600 480 3 0 -12 0 12000 20
r42r4-r1 0 120 120 3 0 0 6 -12000
Jeff Bivin -- LZHS
39
Perform the row operations to determine Tableau 4
x1 x2 x3 s1 s2 s3 z Values
r130r1r3 0 0 -3120 -87 120 -12 0 132000
r210r2-r3 1200 0 -480 -33 0 12 0 108000
r3 0 600 480 3 0 -12 0 12000
r452r4-r3 0 0 120 12 0 12 30 -72000
Jeff Bivin -- LZHS
40
from row 1 s2 132000 / 120 1100from row 2
x1 108000 / 1200 90from row 3 x2 12000 /
600 20from row 4 z -(-72000 / 30)
2400also x3 s1 s3 0
Determine the solutions from the 4th Tableau
x1 x2 x3 s1 s2 s3 z Values
r130r1r3 0 0 -3120 -87 120 -12 0 132000
r210r2-r3 1200 0 -480 -33 0 12 0 108000
r3 0 600 480 3 0 -12 0 12000
r452r4-r3 0 0 120 12 0 12 30 -72000
Jeff Bivin -- LZHS
41
Process CompleteAnswer the Question

• The minimum cost is 2400 when you order
• 90 bags of type A fertilizer
• 20 bags of type B fertilizer
• 0 bags of type C fertilizer

Jeff Bivin -- LZHS