Docstoc

Examples

Document Sample
Examples Powered By Docstoc
					                                                                  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
Microsoft Excel 8.0e Answer Report
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


Adjustable Cells
    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


Adjustable Cells
                   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
                 Final Shadow Constraint    Allowable    Allowable
   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
Microsoft Excel 8.0e Answer Report
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


Adjustable Cells
    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


Adjustable Cells
                     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
                      Final       Shadow     Constraint    Allowable   Allowable
   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
Microsoft Excel 8.0e Answer Report
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


Adjustable Cells
    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
Microsoft Excel 8.0e Answer Report
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


Adjustable Cells
    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
Microsoft Excel 8.0e Answer Report
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


Adjustable Cells
    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
Microsoft Excel 8.0e Answer Report
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


Adjustable Cells
    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