Influence_Diagram

Document Sample
Influence_Diagram Powered By Docstoc
					                                   Influence Diagram


Price/Unit     Aluminum                                 Price/Unit               Price/Unit   inspection
                              Price/Unit   Shearer                   drill
Aluminum       lbs                                      Drill                    Inspection   minutes
                              shearer      minues                    minutes


                                                                                                imagine all
                                                                                                on top point to all
                                                                                                profits. All the arro
                                                                                                make things ugly...

                             Profit/LR               Profit/SR         Proft/M




         Units produced of
         each



                                               Total Profit
inspection   Price/Unit   assembly
minutes      Assembly     minutes



  imagine all 10 things
  on top point to all 3
  profits. All the arrows
  make things ugly...
Inputs
Al $/unit                 $      25.00
Shearer $/minute          $       2.08
Drill & Deburr $/minute   $       1.54
Assembly $/minute         $       1.67
Inspection $/minute       $       1.96

Product Type
                             LR             SR               M
Al per product                19            17              15
Shearer minutes               35            38              23
Drill minutes                 40            30              20
Assembly minutes              21            24              15
Inspection minutes            35            35              25
Revenue per unit          $1,000.00       $900.00         $750.00
Profit per unit             286.875        241.042          222.292

Production Plan               LR            SR              M
Units Produced                 5            10              23             Changing cells (aka 'decision
                              >=            >=              >=
Minimum Production             5            10               5            I created this sheet us
                                                                          'textbook' method bec
Resource Constraints      Used                        Available           more intuitive to look a
Al per product                     610      <=             800            follow. I prefer more c
Shearer minutes                   1084      <=           1200             the different ranges, b
Drill minutes                      960      <=           1600             enables immediate rec
Assembly minutes                   690      <=           1300             ranges.
Inspection minutes                1100      <=           1100

Product
                              LR             SR            M
Revenue                   $ 5,000.00     $ 9,000.00   $ 17,250.00
Input Costs
  Al                      $ 2,375.00     $ 4,250.00   $   8,625.00
  Shearer                 $ 364.58       $ 791.67     $   1,102.08
  Drill                   $ 308.33       $ 462.50     $     709.17
  Assembly                $ 175.00       $ 400.00     $     575.00
  Inspection              $ 342.71       $ 685.42     $   1,126.04
Profit                    $ 1,434.38     $ 2,410.42   $   5,112.71    $ 8,957.50

Total profit              $ 1,434.38     $ 2,410.42   $ 5,112.71
                         LEGEND
                     units_produced              Changing cells
                     profit1                     Objective to maximize
                     min_production              Constraint on production
                     resources_used1
                     resources_avail             Constraint on resources


                                                 All areas with blue borders are 'input variables'


                                                  These purple cells just represent an alternative to doing the 'full blown' reven
                                        It's entirely possible to just use 'profit per unit' and then multiply this times the units_p

                                                                   One way data table with price of Aluminum on vert
                                                                                    LR         SR
                                                                   $ 15.00            5        10
                                                                   $ 17.50            5        10
                                                                   $ 20.00            5        10
                                                                   $ 22.50            5        10
 Changing cells (aka 'decision variables')                         $ 25.00            5        10
                                                                   $ 27.50            5        10
I created this sheet using the                                     $ 30.00            5        10
'textbook' method because I find it                                $ 32.50            5        10
more intuitive to look at, and easier to                           $ 35.00            5        10
follow. I prefer more colorcoding of                               $ 37.50 17.85714            10
the different ranges, because it                                   $ 40.00 17.85714            10
enables immediate recognition of the                               $ 42.50            5        10
ranges.                                                            $ 45.00            5        10
                                                                   $ 47.50            5        10
                                                                   $ 50.00            5        10

                                                                                     Goalseek breakeven price = $39.79/lb


                                                                                                         Summary of Profit to

                                                                                   $20,000.00


                                                                                   $15,000.00
                Objective to maximize (aka 'target cell')
                                                                                   $10,000.00
                                                                          Profit
Profit
         $5,000.00


                $-


         $(5,000.00)
ative to doing the 'full blown' revenue breakdown that I am doing.
 then multiply this times the units_produced

 th price of Aluminum on vertical
                    M       Profit                         Increase
                   23 $ 15,057.50         $   15.00    $   1,525.00
                   23 $ 13,532.50         $   17.50    $   1,525.00
                   23 $ 12,007.50         $   20.00    $   1,525.00
                   23 $ 10,482.50         $   22.50    $   1,525.00
                   23 $ 8,957.50          $   25.00    $   1,525.00
                   23 $ 7,432.50          $   27.50    $   1,525.00
                   23 $ 5,907.50          $   30.00    $   1,525.00
                   23 $ 4,382.50          $   32.50    $   1,525.00
                   23 $ 2,857.50          $   35.00    $   1,516.43
                    5 $ 1,341.07          $   37.50    $   1,460.71
                    5 $ (119.64)          $   40.00    $     874.11
                    5 $ (993.75)          $   42.50    $     850.00
                    5 $ (1,843.75)        $   45.00    $     850.00
                    5 $ (2,693.75)        $   47.50    $     850.00
                    5 $ (3,543.75)        $   50.00

reakeven price = $39.79/lb


          Summary of Profit to Aluminum Price
Aluminum $/lb
Microsoft Excel 9.0 Answer Report
Worksheet: [optimization.xls]TranscoNew
Report Created: 4/15/2008 1:40:32 AM


Target Cell (Max)
    Cell            Name          Original Value   Final Value
  $E$39 profit1                   $     8,614.17 $     8,957.50


Adjustable Cells
    Cell         Name             Original Value     Final Value
  $B$19 Units Produced LR                      12               5
  $C$19 Units Produced SR                      15              10
  $D$19 Units Produced M                        7              23


Constraints
   Cell            Name             Cell Value         Formula        Status    Slack
  $B$24 Al per product Used                  610    $B$24<=$D$24    Not Binding   190
  $B$25 Shearer minutes Used                1084    $B$25<=$D$25    Not Binding   116
  $B$26 Drill minutes Used                   960    $B$26<=$D$26    Not Binding   640
  $B$27 Assembly minutes Used                690    $B$27<=$D$27    Not Binding   610
  $B$28 Inspection minutes Used             1100    $B$28<=$D$28    Binding         0
  $B$19 Units Produced LR                       5   $B$19>=$B$21    Binding         0
  $C$19 Units Produced SR                      10   $C$19>=$C$21    Binding         0
  $D$19 Units Produced M                       23   $D$19>=$D$21    Not Binding    18
Microsoft Excel 9.0 Sensitivity Report
Worksheet: [optimization.xls]TranscoNew
Report Created: 4/15/2008 1:40:32 AM                         I'm using 9999 as short hand for 'infinity



Adjustable Cells
                                 Final   Reduced     Objective    Allowable
   Cell          Name            Value     Cost     Coefficient   Increase
  $B$19 Units Produced LR            5 -24.33333334      286.875 24.33333334
  $C$19 Units Produced SR           10 -70.16666667 241.0416667 70.16666667
  $D$19 Units Produced M            23            0 222.2916667        1E+30

Constraints
                                  Final    Shadow       Constraint   Allowable
   Cell              Name         Value     Price       R.H. Side    Increase
  $B$24   Al per product Used       610             0           800       1E+30
  $B$25   Shearer minutes Used     1084             0          1200       1E+30
  $B$26   Drill minutes Used        960             0          1600       1E+30
  $B$27   Assembly minutes Used     690             0          1300       1E+30
  $B$28   Inspection minutes Used  1100   8.891666667          1100 126.0869565
as short hand for 'infinity'




            Allowable   Allowable Range
            Decrease
                 1E+30   -99999 311.2083
                 1E+30   -99999 311.2083
           17.38095238 204.9107     99999


            Allowable
            Decrease
                   190
                   116
                   640
                   610
                   450
Microsoft Excel 9.0 Limits Report
Worksheet: [optimization.xls]TranscoNew
Report Created: 4/15/2008 1:40:32 AM


                  Target
   Cell           Name         Value
  $E$39 profit1             $ 8,957.50


            Adjustable                     Lower Target     Upper    Target
   Cell        Name           Value        Limit Result     Limit    Result
  $B$19 Units Produced LR              5        5 8957.5         5   8957.5
  $C$19 Units Produced SR             10      10 8957.5        10    8957.5
  $D$19 Units Produced M              23        5 4956.25      23    8957.5
Product mix model

Input data
Hourly wage rate               $8.00
Cost per oz of metal           $0.50
Cost per oz of glass           $0.75


Muffler Type              Family          Sports
Labor hours per muffler               1            0.8
brackets per muffler                  2              6
alloy per muffler                     1            1.5
Unit selling price            $28.50          $12.50


Production plan
Muffler type              Family          Sports
Mufflers produced              26.00           16.00
                                     <=             >=
Sales Constraints                    35              5


Resource constraints               Used                  Available
Labor hours                          39       <=               40
Brackets                            148       <=              150
Alloy                                50       <=               50



Revenue, cost summary
Frame type            Family              Sports
Profit                              $10            $18       548     Objective to maximize
                                   260             288
Range names used:
Mufflers_produced     =Model!$B$16:$C$16
Maximum_sales         =Model!$B$18:$C$18
Profit                =Model!$D$28
Resources_available   =Model!$D$21:$D$23
Resources_used        =Model!$B$21:$B$23
Microsoft Excel 9.0 Answer Report
Worksheet: [optimization.xls]BobNew
Report Created: 4/14/2008 10:51:00 PM


Target Cell (Max)
    Cell            Name           Original Value    Final Value
  $D$28 Profit                                550              548


Adjustable Cells
    Cell          Name           Original Value      Final Value
  $B$16 Mufflers produced Family           25.00             26.00
  $C$16 Mufflers produced Sports           16.67             16.00


Constraints
   Cell           Name              Cell Value         Formula         Status    Slack
  $B$21 Labor hours Used                      39    $B$21<=$D$21     Not Binding    1.2
  $B$22 Brackets Used                        148    $B$22<=$D$22     Not Binding      2
  $B$23 Alloy Used                            50    $B$23<=$D$23     Binding          0
  $B$16 Mufflers produced Family           26.00    $B$16<=$B$18     Not Binding      9
  $C$16 Mufflers produced Sports           16.00    $C$16>=$C$18     Not Binding 11.00
  $B$16 Mufflers produced Family           26.00    $B$16=integer    Binding       0.00
  $C$16 Mufflers produced Sports           16.00    $C$16=integer    Binding       0.00
Microsoft Excel 9.0 Answer Report
Worksheet: [optimization.xls]BobNew
Report Created: 4/14/2008 10:49:51 PM


Target Cell (Max)
    Cell            Name           Original Value    Final Value
  $D$28 Profit                       549.9997214               550


Adjustable Cells
    Cell          Name           Original Value      Final Value
  $B$16 Mufflers produced Family           25.00             25.00
  $C$16 Mufflers produced Sports           16.67             16.67


Constraints
   Cell           Name              Cell Value         Formula         Status
  $B$21 Labor hours Used                      38    $B$21<=$D$21     Not Binding
  $B$22 Brackets Used                        150    $B$22<=$D$22     Binding
  $B$23 Alloy Used                            50    $B$23<=$D$23     Binding
  $B$16 Mufflers produced Family           25.00    $B$16<=$B$18     Not Binding
  $C$16 Mufflers produced Sports           16.67    $C$16>=$C$18     Not Binding
   Slack
1.666666667
           0
           0
          10
       11.67
Microsoft Excel 9.0 Sensitivity Report
Worksheet: [optimization.xls]BobNew
Report Created: 4/14/2008 10:49:51 PM


Adjustable Cells
                                 Final Reduced Objective      Allowable
   Cell           Name           Value   Cost   Coefficient   Increase
  $B$16 Mufflers produced Family 25.00     0.00          10            2
  $C$16 Mufflers produced Sports 16.67     0.00          18           12

Constraints
                                  Final Shadow Constraint    Allowable
   Cell          Name             Value  Price   R.H. Side    Increase
  $B$21 Labor hours Used             38        0         40        1E+30
  $B$22 Brackets Used               150        1        150           50
  $B$23 Alloy Used                   50        8         50 1.136363636
Allowable       Allowable Range
Decrease
            4     21.00    27.00
            3     13.67    28.67


 Allowable
 Decrease
1.666666667
7.142857143
        12.5
                   857317b4-435f-436b-86c8-524be27687c3.xls


         A             B          C           D           E          F      G
1 T R A N S C O
2    TRANSCO                    Raw Materials               Units         Cost/
3                   Long Range Short Range Microwave     Available        Unit
4 Aluminum Stock         19           17          15         800         $25.00
5
6                           Production Time (minutes)       Total          Cost/
7                   Long Range Short Range Microwave      Minutes          Hour
8          Shearer       35           38          23        1200         $125.00
9   Drill & Deburr       40           30          20        1600          $92.50
10       Assembly        21           24          15        1300         $100.00
11      Inspection       35           35          25        1100         $117.50
12    Min Orders          5           10           5
13           Costs
14   Raw Material $475.000        $425.000 $375.000
15     Production $238.125        $233.958 $152.708
16           Total: $713.125      $658.958 $527.708
17
18        Revenue $1,000.00        $900.00     $750.00  $31,250.00
19
20 Maximize Profit    $286.88      $241.04     $222.29   $8,957.50   <=OPTIMIZE
21                  Long Range Short Range Microwave
22 DECISIONS =>         5.00         10.00      23.00  Amount Used   Need/Available
23      Subject to: Long Range Short Range Microwave CONSTRAINTS       Resources
24       Aluminum        19           17          15       610.00    <    800
25         Shearer       35           38          23      1084.00    < 1200
26  Drill & Deburr       40           30          20       960.00    < 1600
27       Assembly        21           24          15       690.00    < 1300
28      Inspection       35           35          25      1100.00    < 1100
29        Min Long        1                                  5.00    >     5
30       Min Short                     1                    10.00    >     10
31     Min Micros                                  1        23.00    >     5
32            Ratio       2            2          -1         7.00    >




                                    Page 20
                     857317b4-435f-436b-86c8-524be27687c3.xls


                 H
        1
        2
        3
        4
        5
        6
        7
        8
        9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
<=OPTIMIZE
       21
       22
Need/Available
       23
  Resources
       24
       25
       26
       27
       28
       29
       30
       31
       32




                                     Page 21