# Examples

Document Sample

```					                                                                  Example I

A          B          C           D      E         F         G      H      I        J          K           L          M
1   Econ 172A
2   Joel Sobel                                 Simplex Example
3
4   Objective Function Coefficients               c1       c2         c3     c4
5                                                  2        4         3       1
6
7   Variables                                     x1       x2         x3     x4
8                                                  0      10.4        0      0.4         Put guesses for variables in E8 through H8
9
10
11
12   Resource Constraints                          ai1      ai2        ai3    ai4   LHS   compare        bi         si
13                                     #1           3        1          1      4     12                  12          0
14                                     #2           1       -3          2      3    -30                   7         37
15                                     #3           2        1          3     -1     10                  10          0
16
17
18   Value          42
19
20               Formula for B18 (value) = SUMPRODUCT(E5:H5,E8:H8)
21               Formula for I13 (LHS) = SUMPRODUCT(E\$8:H\$8,E13:H13)                                 Copy Down to get I14 and I15
22               Formula for L13 (slack) = ABS(I13-K13)                                              Copy Down to get L14 and L15

Page 1
Worksheet: [sens.xls]Example I
Report Created: 2/14/01 5:28:23 PM

Target Cell (Max)
Cell Name       Original Value     Final Value
\$B\$18 Value                    42              42

Cell Name       Original Value     Final Value
\$E\$8 x1                         0               0
\$F\$8 x2                      10.4            10.4
\$G\$8 x3                         0               0
\$H\$8 x4                       0.4             0.4

Constraints
Cell Name          Cell Value         Formula         Status   Slack
\$I\$13 #1 LHS                  12    \$I\$13<=\$K\$13    Binding          0
\$I\$14 #2 LHS                 -30    \$I\$14<=\$K\$14    Not Binding     37
\$I\$15 #3 LHS                  10    \$I\$15<=\$K\$15    Binding          0
\$E\$8 x1                        0    \$E\$8>=0         Binding          0
\$F\$8 x2                     10.4    \$F\$8>=0         Not Binding  10.4
\$G\$8 x3                        0    \$G\$8>=0         Binding          0
\$H\$8 x4                      0.4    \$H\$8>=0         Not Binding    0.4
Microsoft Excel 8.0e Sensitivity Report
Worksheet: [sens.xls]Example I
Report Created: 2/5/01 3:42:57 PM

Final Reduced Objective       Allowable     Allowable
Cell    Name    Value   Cost    Coefficient   Increase      Decrease
\$E\$8    x1            0       -7           2            7         1E+30
\$F\$8    x2        10.4         0           4        1E+30   2.692307692
\$G\$8    x3            0       -7           3            7         1E+30
\$H\$8    x4          0.4        0           1           15              5

Constraints
Cell    Name Value   Price   R.H. Side    Increase    Decrease
\$I\$13   #1 LHS     12       1         12 2.12377E+12             2
\$I\$14   #2 LHS    -30       0          7        1E+30          37
\$I\$15   #3 LHS     10       3         10            2 12.33333333
Lumber Example

Econ 172A
Joel Sobel                             LUMBER EXAMPLE

Objective Function Coefficients        c1          c2         c3           c4
30         80           30         100

Variables                                         Chair      Unfinished Tables Tables
Unfinished Finished Chair        Finished
0 1333.333            0          0          Put guesses for variables in E8 through H8

Resource Constraints                   ai1         ai2        ai3          ai4      LHS      compare   bi         si
#1          30         30           40         40    40000                40000        0
#2           2          4            2          5 5333.333                 6000 666.6667

Value        106666.7

Page 4
Worksheet: [sens.xls]Lumber Example
Report Created: 2/5/01 2:37:50 PM

Target Cell (Max)
Cell Name       Original Value     Final Value
\$B\$17 Value                     0    106666.6667

Cell Name Original Value           Final Value
\$E\$8 UN.CH.               0                    0
\$F\$8 F. CH.               0          1333.333333
\$G\$8 UN. TBL              0                    0
\$H\$8 F. Table             0                    0

Constraints
Cell Name          Cell Value         Formula        Status       Slack
\$I\$13 Lumber               40000    \$I\$13<=\$K\$13   Binding                 0
\$I\$14 Labor         5333.333333     \$I\$14<=\$K\$14   Not Binding   666.6666667
\$E\$8 x1                        0    \$E\$8>=0        Binding                 0
\$F\$8 x2             1333.333333     \$F\$8>=0        Not Binding   1333.333333
\$G\$8 x3                        0    \$G\$8>=0        Binding                 0
\$H\$8 x4                        0    \$H\$8>=0        Binding                 0
Microsoft Excel 8.0e Sensitivity Report
Worksheet: [sens.xls]Lumber Example
Report Created: 2/5/01 2:37:50 PM

Final         Reduced    Objective    Allowable     Allowable
Cell    Name      Value           Cost     Coefficient   Increase     Decrease
\$E\$8    x1                 0            -50          30           50        1E+30
\$F\$8    x2       1333.333333              0          80        1E+30             5
\$G\$8    x3                 0   -76.66666667          30 76.66666667         1E+30
\$H\$8    x4                 0   -6.666666667         100 6.666666667         1E+30

Constraints
Cell Name          Value         Price    R.H. Side     Increase    Decrease
\$I\$13 #1 LHS           40000   2.666666667      40000          5000       40000
\$I\$14 #2 LHS     5333.333333             0       6000         1E+30 666.6666667
Lumber Example - 2

Econ 172A
Joel Sobel                             LUMBER EXAMPLE

Objective Function Coefficients        c1          c2          c3           c4
30         70            30         100

Variables                              x1          x2          x3           x4
0           0            0         1000            Put guesses for variables in E8 through H8

Resource Constraints                   ai1         ai2         ai3          ai4          LHS      compare    bi           si
#1          30         30            40          40       40000                 40000     1.46E-07
#2           2          4             2           5        5000                  6000         1000

Value         100000
This spreadsheet lowers the price of Finished Chairs to \$100.
This leads to a change in the production plan.

Page 7
Worksheet: [sens.xls]Lumber Example - 2
Report Created: 2/5/01 3:14:34 PM

Target Cell (Max)
Cell        Name      Original Value    Final Value
\$B\$17 Value               93333.33333           100000

Cell         Name   Original Value      Final Value
\$E\$8 Unfinished_Chair               0               0
\$F\$8 Finished_Chair     1333.333333                 0
\$G\$8 Unfinished_Table               0               0
\$H\$8 Finished_Table                 0            1000

Constraints
Cell       Name          Cell Value        Formula         Status   Slack
\$I\$13 Lumber                     40000   \$I\$13<=\$K\$13    Binding         0
\$I\$14 Labor                       5000   \$I\$14<=\$K\$14    Not Binding 1000
\$E\$8 Unfinished_Chair                0   \$E\$8>=0         Binding         0
\$F\$8 Finished_Chair                  0   \$F\$8>=0         Binding         0
\$G\$8 Unfinished_Table                0   \$G\$8>=0         Binding         0
\$H\$8 Finished_Table               1000   \$H\$8>=0         Not Binding 1000
Lumber Example - 3

Econ 172A
Joel Sobel                             LUMBER EXAMPLE

Objective Function Coefficients        c1          c2          c3           c4
30          80           30         100

Variables                              x1          x2          x3           x4
0           0            0         1000            Put guesses for variables in E8 through H8

Resource Constraints                   ai1         ai2         ai3          ai4          LHS      compare    bi           si
#1          30          30           40          40       40000                 40000          0
#2           2           5            2           6        6000                  6000          0

Value         100000
This spreadsheet raises the labor input for finished products by one hour.

Page 9
Worksheet: [sens.xls]Lumber Example - 3
Report Created: 2/5/01 3:19:25 PM

Target Cell (Max)
Cell        Name      Original Value    Final Value
\$B\$17 Value                     100000          100000

Cell         Name   Original Value      Final Value
\$E\$8 Unfinished_Chair               0               0
\$F\$8 Finished_Chair                 0               0
\$G\$8 Unfinished_Table               0               0
\$H\$8 Finished_Table             1000             1000

Constraints
Cell       Name          Cell Value        Formula         Status   Slack
\$I\$13 Lumber                     40000   \$I\$13<=\$K\$13    Binding         0
\$I\$14 Labor                       6000   \$I\$14<=\$K\$14    Binding         0
\$E\$8 Unfinished_Chair                0   \$E\$8>=0         Binding         0
\$F\$8 Finished_Chair                  0   \$F\$8>=0         Binding         0
\$G\$8 Unfinished_Table                0   \$G\$8>=0         Binding         0
\$H\$8 Finished_Table               1000   \$H\$8>=0         Not Binding 1000
Lumber Example - 4

Econ 172A
Joel Sobel                             Lumber Example with Cabinets

Objective Function Coefficients        c1         c2         c3            c4
30         80            30         100      150

Variables                              x1      x2        x3                x4
0 1319.728               0          0 8.163265              Put guesses for variables in E8 through H8

Resource Constraints                   ai1        ai2        ai3           ai4                  LHS      compare     bi           si
#1         30         30            40         40        50      40000                  40000     2.13E-07
#2          2          3             2          4       250       6000                   6000     7.46E-09

This sheet adds a fifth output - cabinets - and
Value        106802.7                                   modifies the constraints accordingly.

Page 11
Lumber Example - 4

bles in E8 through H8

Page 12
Worksheet: [sens.xls]Lumber Example - 4
Report Created: 2/5/01 3:47:49 PM

Target Cell (Max)
Cell          Name      Original Value     Final Value
\$B\$17 Value                 106666.6667      106802.7211

Cell         Name       Original Value     Final Value
\$E\$8 C_U                                0              0
\$F\$8 C_F                    1333.333333      1319.727891
\$G\$8 T_U                                0              0
\$H\$8 T_F                                0              0
\$I\$8    Cabinet                         0    8.163265306

Constraints
Cell         Name          Cell Value         Formula        Status       Slack
\$J\$13 Lumber_Constraint            40000    \$J\$13<=\$L\$13   Binding                 0
\$J\$14 Labor_Constraint              6000    \$J\$14<=\$L\$14   Binding                 0
\$E\$8 C_U                               0    \$E\$8>=0        Binding                 0
\$F\$8 C_F                    1319.727891     \$F\$8>=0        Not Binding   1319.727891
\$G\$8 T_U                               0    \$G\$8>=0        Binding                 0
\$H\$8 T_F                               0    \$H\$8>=0        Binding                 0
\$I\$8    Cabinet             8.163265306     \$I\$8>=0        Not Binding   8.163265306
Lumber Example - 4 (2)

Econ 172A
Joel Sobel                             Lumber Example with Cabinets

Objective Function Coefficients        c1         c2         c3            c4
30         80            30         100       100

Variables                              x1      x2        x3                x4
0 1333.333               0          0          0                 Put guesses for variables in E8 through H8

Resource Constraints                   ai1        ai2        ai3           ai4                   LHS      compare         bi           si
#1         30         30            40         40         50      40000                      40000     1.04E-08
#2          2          3             2          4        250       4000                       6000         2000

This sheet lowers the profit from cabinets (I5 falls
Value        106666.7                                   to 100) and shows that cabinet production is no
longer profitable.

Page 14
Lumber Example - 4 (2)

bles in E8 through H8

Page 15
Worksheet: [sens.xls]Lumber Example - 4 (2)
Report Created: 2/5/01 3:52:37 PM

Target Cell (Max)
Cell          Name      Original Value     Final Value
\$B\$17 Value                 106394.5578      106666.6667

Cell         Name       Original Value     Final Value
\$E\$8 C_U                                0              0
\$F\$8 C_F                    1319.727891      1333.333333
\$G\$8 T_U                                0              0
\$H\$8 T_F                                0              0
\$I\$8    Cabinet             8.163265306                0

Constraints
Cell         Name          Cell Value         Formula        Status       Slack
\$J\$13 Lumber_Constraint            40000    \$J\$13<=\$L\$13   Binding                 0
\$J\$14 Labor_Constraint              4000    \$J\$14<=\$L\$14   Not Binding          2000
\$E\$8 C_U                               0    \$E\$8>=0        Binding                 0
\$F\$8 C_F                    1333.333333     \$F\$8>=0        Not Binding   1333.333333
\$G\$8 T_U                               0    \$G\$8>=0        Binding                 0
\$H\$8 T_F                               0    \$H\$8>=0        Binding                 0
\$I\$8    Cabinet                        0    \$I\$8>=0        Binding                 0

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 10 posted: 12/10/2011 language: Latin pages: 16