Docstoc

Slide 1 - Bookmark This Page

Document Sample
Slide 1 - Bookmark This Page Powered By Docstoc
					Samuel C. Weaver, PhD, CMA, CFM
     Finance Professor of Practice
                Lehigh University
My Background
  PhD – Lehigh University – Finance (1985)
  CMA (1981) and CFM (1996)
  Director Financial Planning and Analysis – Hershey
   Foods Corporation (1978 – 1998)
  Adjunct Professor, Lehigh University MBA (1985-1997)
  Professor of Finance, Lehigh University (1998-Current)
  Consulting and Professional Education
  Text Book and Professional Book Author as well as
   numerous Journal Articles and Cases

6/8/2010       Risk Analysis in Financial Decision Making   Samuel C. Weaver, PhD   2
My Books




           Risk Analysis in Financial Decision Making
6/8/2010   Samuel C. Weaver, PhD                        3
Agenda and Overview
  Financial Analysis of the New Product Decision
  Basic Discounted Cash Flow Analysis
  Sensitivity and Scenario Analysis
  Data Tables
  Monte Carlo Analysis




6/8/2010         Risk Analysis in Financial Decision Making   Samuel C. Weaver, PhD   4
Five Steps for Investment Analysis
  Estimate Initial Investment
  Determine Annual Incremental Cash Flows
  Project the Terminal Cash Flows Including the
   Expected After Tax Salvage Value
  Find the Present Value of the Future Cash Flows
  Determine the Net Present Value




6/8/2010       Risk Analysis in Financial Decision Making   Samuel C. Weaver, PhD   5
Reese Sticks Example




  My Last New Product – 1998 Introduction
  Hugely Successful
  Now a Bit “Mature”
6/8/2010      Risk Analysis in Financial Decision Making   Samuel C. Weaver, PhD   6
                                                                                                 Table 11.11

                                                                                  New Product Decision
                                                                                                     ($ 000's)


                                                           Initial         Year         Year         Year            Year         Year         Year         Year         Year         Year         Year
                                                         Investment         1            2            3               4            5            6            7            8            9            10

                                                                                          Key Assumptions
           Purchase Price of New Equipment
           Working Capital (% of Incremental Sales)
                                                         $ (50,000)
                                                              8.0%                                                                                           SOP: 10 Years
           Initial Sales                                                 $ 68,000
           Sales Growth                                                                 -10.0%         3.0%            3.0%         3.0%         3.0%         3.0%         3.0%         3.0%         3.0%
           Cost of Sales (Excluding Depreciation - % of Sales)              59.0%        59.0%        59.0%           59.0%        59.0%        59.0%        59.0%        59.0%        59.0%        59.0%
           Operating Costs (% of Sales)                                     18.0%        18.0%        18.0%           18.0%        18.0%        18.0%        18.0%        18.0%        18.0%        18.0%
           Additional Product Launch Expense                             $ 20,000 $      5,000 $        -   $           -   $        -   $        -   $        -   $        -   $        -   $        -
           Tax Rate                                                         39.0%        39.0%        39.0%           39.0%        39.0%        39.0%        39.0%        39.0%        39.0%        39.0%
           Residual Value                                                                                                                                                                      $    5,000
           Cost of Capital 11.0%
                                                                                          Capital Evaluation
           Purchase Price                                $ (50,000)
           Initial Working Capital                          (5,440)
           Incremental Operating Cash Flow
              Sales                                                      $ 68,000 $ 61,200 $          63,036 $ 64,927 $            66,875 $ 68,881 $         70,948 $ 73,076 $         75,268 $ 77,526
              Cost of Sales                                                (40,120) (36,108)         (37,191)  (38,307)           (39,456)  (40,640)        (41,859)  (43,115)        (44,408)  (45,741)
              Depreciation*
                Gross Income
                                     MACRS                                  (7,145)
                                                                            20,735
                                                                                    (12,245)
                                                                                     12,847
                                                                                                      (8,745)
                                                                                                      17,100
                                                                                                                (6,245)
                                                                                                                20,375
                                                                                                                                   (4,465)
                                                                                                                                   22,954
                                                                                                                                             (4,465)
                                                                                                                                             23,776
                                                                                                                                                             (4,465)
                                                                                                                                                             24,624
                                                                                                                                                                       (2,225)
                                                                                                                                                                       27,736
                                                                                                                                                                                          -
                                                                                                                                                                                       30,860
                                                                                                                                                                                                    -
                                                                                                                                                                                                 31,786
              Operating Expenses                                           (12,240) (11,016)         (11,346)  (11,687)           (12,037)  (12,399)        (12,771)  (13,154)        (13,548)  (13,955)
              Additional Product Launch Expense                            (20,000)  (5,000)             -         -                  -         -               -         -               -         -
                Operating Income                                           (11,505)  (3,169)           5,753     8,688             10,916    11,378          11,853    14,582          17,312    17,831
              Taxes (40%)                                                    4,487    1,236           (2,244)   (3,388)            (4,257)   (4,437)         (4,623)   (5,687)         (6,752)   (6,954)
                After-Tax Operating Income                               $ (7,018) $ (1,933) $         3,510 $ 5,300 $              6,659 $ 6,940 $           7,230 $ 8,895 $          10,560 $ 10,877

              After-Tax Operating Income                                 $ (7,018) $ (1,933) $ 3,510 $ 5,300 $ 6,659 $ 6,940 $ 7,230 $ 8,895 $ 10,560 $ 10,877
              Depreciation                                                  7,145    12,245    8,745   6,245    4,465    4,465    4,465    2,225      -        -
              Additional Working Capital (Investment)                         544      (147)    (151)   (156)    (160)    (165)    (170)    (175)    (181)     -
               Incremental Operating Cash Flow                           $    671 $ 10,165 $ 12,103 $ 11,389 $ 10,963 $ 11,240 $ 11,525 $ 10,945 $ 10,379 $ 10,877
              Residual Value - After Tax                                                                                                                                                       $    3,050
              Working Capital Recovery                                                                                                                                                              6,202
           Total Cash Flow                               $ (55,440) $         671   $ 10,165     $ 12,103        $ 11,389     $ 10,963     $ 11,240     $ 11,525     $ 10,945     $ 10,379     $ 20,129

           Present Value     11.0%                       $ (55,440) $         604   $    8,250   $    8,850      $    7,502   $    6,506   $    6,009   $    5,551   $    4,749   $    4,058   $    7,089
                                                                         $ 59,169 Gross Present Value (PV - Cash Flow Years 1-10)
                                     Net Present Value   $       3,729

           * MACRS Depreciation Rate                                       14.29%       24.49%       17.49%          12.49%        8.93%        8.93%        8.93%        4.45%        0.00%        0.00%


6/8/2010                                       Risk Analysis in Financial Decision Making                                         Samuel C. Weaver, PhD                                                     7
Milton’s Madness
  Hypothetical New Product
  Milton’s Madness is a Milk Chocolate Bar with
   Peanuts, Marshmallow, and Shaved White
   Chocolate Inclusions.
  After a Six-Month Test Market, Consumers Rated the
   Bar Very Highly on Initial Trial (and even on the
   Second and Third Trial).
  Interest Seemed to Slowly Waiver after Multiple
   Trials.
  Limited (5-year) “Window of Opportunity”.
6/8/2010        Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   8
Sales Assumptions
  First Year Sale’s Will Be $45.0 million (August to
   December).
  With the Annualized Sales, Milton’s Madness Will
   Sell 25% More in the Second Year ($56.25 million).
  Sales Growth Will Moderate in Years 3 and 4, 20%
   and 12%, respectively.
  Finally in Year 5, Sales Will Contract by 25%.




6/8/2010         Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   9
Key Assumptions
Assumptions                           Year 0          Year 1          Year 2          Year 3     Year 4    Year 5
 Year 1 Sales             $000s                     $ 45,000              n/a             n/a       n/a       n/a
 Growth Rate                                            n/a              25.0%           20.0%     12.0%    -25.0%

 Variable COGS                                           56.0%           55.0%           54.0%     54.0%     54.0%
 Fixed COGS (Ex Deprec)                                    800             800             800       800       800
 Selling, Marketing & Admin                              6,500           5,000           4,000     3,500     1,500

 Tax Rate                                                38.0%           38.0%           38.0%     38.0%     38.0%
 Investment
   Equipment                     $ 60,000
   Working Capital                   3,000
 Incremental Work Cap Inv (% of Sales)                   10.0%           10.0%           10.0%     n/a       n/a

 Depreciable Life                                          5.0 Years
 Cost of Capital                                         11.0%




6/8/2010                      Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD                      10
Capital Investment Analysis
           Financial Analysis                   Year 0        Year 1         Year 2         Year 3      Year 4         Year 5
             Sales                  $000s                 $ 45,000       $ 56,250       $ 67,500       $ 75,600    $ 56,700
             Variable Cost                                     25,200         30,938         36,450      40,824         30,618
             Fixed Cost                                           800            800            800         800            800
             Depreciation                                      12,000         12,000         12,000      12,000         12,000
               Cost of Goods Sold                              38,000         43,738         49,250      53,624         43,418
               Gross Income                                     7,000         12,513         18,250      21,976         13,282
                 Gross Margin                                   15.56%         22.24%         27.04%      29.07%         23.43%

             Marketing Expense                                  6,500          5,000          4,000       3,500          1,500
               Operating Income                                  500           7,513         14,250      18,476         11,782
             Taxes                                               190           2,855          5,415       7,021          4,477
               After-tax Operating Income                 $      310     $     4,658    $     8,835    $ 11,455    $     7,305

           Cash Flow
             After-tax Operating Income                   $       310 $ 4,658 $ 8,835 $ 11,455                     $     7,305
             Depreciation                                      12,000   12,000  12,000  12,000                          12,000
             Working Capital Investment     $ (3,000)          (2,625)  (1,125)   (810)    -                             7,560
             Equipment                       (60,000)             -        -       -       -                               -
               Cash Flow                    $ (63,000) $        9,685    $ 15,533       $ 20,025       $ 23,455    $ 26,865

               Net Present Value            $     4,368                      E55
6/8/2010                             Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD                             11
Key Findings
  Accept the Project!
  Positive $4,368,000 NPV!
  Except for the Last Year, Improving Annual Sales!
  Except for the Last Year, Improving Margins!
  Buy the Equipment, Start Production, and Introduce
      Milton’s Madness, the Wackiest, Best Tasting New
      Product in Decades!




6/8/2010           Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   12
Sensitivity and Scenario Analysis
  Recent Survey Showed That Over 50% of
   Corporations Always or Almost Always use
   Sensitivity Analysis.
  What if First Year Sales do not Materialize?
  What if Subsequent Year Growth Rates Falter?
  How Important is Attaining the Projected Margins,
   Tax Rates, Working Capital Investment, etc.?
  What about the Cost of Capital?



6/8/2010        Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   13
Growth and Margin Analysis

       Additional Sales Growth                       Additional Margin Improvement
  Increment      NPV      vs. Base                  Increment      NPV     vs. Base
     3.0%     $ 7,514.9 $ 3,147.2                      3.0%     $ 8,441.0 $ 4,073.4
     2.0%       6,447.5    2,079.8                     2.0%       7,083.2   2,715.6
     1.0%       5,398.5    1,030.8                     1.0%       5,725.4   1,357.8
     0.0%       4,367.7        -                       0.0%       4,367.7       -
    -1.0%       3,354.8 (1,012.9)                     -1.0%       3,009.9 (1,357.8)
    -2.0%       2,359.6 (2,008.0)                     -2.0%       1,652.1 (2,715.6)
    -3.0%       1,381.9 (2,985.7)                     -3.0%         294.3 (4,073.4)




6/8/2010             Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   14
  Data Table Analysis
Net Present Value                                                Additional Sales Growth
                     ($000s)      3.0%         2.0%          1.0%         0.0%         -1.0%                -2.0%         -3.0%
                    3.0%       $ 11,801.8   $ 10,661.9     $ 9,541.8 $ 8,441.0 $ 7,359.4                  $ 6,296.7     $ 5,252.6
                    2.0%         10,372.8      9,257.1       8,160.7       7,083.2      6,024.5              4,984.3       3,962.4
  Additional        1.0%          8,943.8      7,852.3       6,779.6       5,725.4      4,689.7              3,672.0       2,672.1
   Margin           0.0%         7,514.9        6,447.5        5,398.5        4,367.7        3,354.8         2,359.6       1,381.9
Improvement         -1.0%        6,085.9        5,042.6        4,017.4        3,009.9        2,019.9         1,047.3          91.7
                    -2.0%        4,657.0        3,637.8        2,636.2        1,652.1          685.0          (265.1)     (1,198.6)
                    -3.0%        3,228.0        2,233.0        1,255.1          294.3         (649.8)       (1,577.5)     (2,488.8)

Change in NPV vs. Base                                           Additional Sales Growth
                                 3.0%         2.0%           1.0%         0.0%        -1.0%                 -2.0%     -3.0%
                    3.0%       $ 7,434.1    $ 6,294.3      $ 5,174.1 $ 4,073.4 $ 2,991.8                  $ 1,929.0 $    884.9
                    2.0%         6,005.2      4,889.5        3,793.0       2,715.6     1,656.9                 616.7    (405.3)
  Additional        1.0%         4,576.2      3,484.6        2,411.9       1,357.8       322.0                (695.7) (1,695.5)
   Margin           0.0%         3,147.2        2,079.8        1,030.8             -         (1,012.9)      (2,008.0)     (2,985.7)
Improvement         -1.0%         1,718.3         675.0            (350.3)      (1,357.8)     (2,347.7)     (3,320.4)     (4,276.0)
                    -2.0%           289.3       (729.9)          (1,731.4)      (2,715.6)     (3,682.6)     (4,632.8)     (5,566.2)
  6/8/2010          -3.0%                     (2,134.7)
                                 (1,139.7) Analysis in Financial (3,112.5)
                                        Risk                                    (4,073.4) Weaver, PhD
                                                                 Decision Making Samuel C.    (5,017.5)     (5,945.1)     (6,856.4)
                                                                                                                                 15
Data Tables
  Excel Mechanical Technique
  Data Tables 1
     Single Dimension
     Capable of Many Data Items (NPV, Gross Margin, etc.)
     Input Change: Sales Growth OR Margin Improvement
  Data Table 2
     Two Dimensions
     Capable of Only 1 Data Item (NPV)
     Two Input Changes: Sales Growth and Margin
      Improvement

6/8/2010         Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   16
Steps to Use Data Tables
  Prepare Assumptions
     Add Sensitivity Cell Used for Adjustment
     Link to Assumptions
  Structure the Data Table Area and Design
  From the Excel Top Tool Bar Run Data Tables
     From Excel 2003, Click Data then Tables
     From Excel 2007, Click Data then What If
      Analysis then Tables



6/8/2010         Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   17
Prepare Key Assumptions
Assumptions                          Year 0        Year 1        Year 2         Year 3        Year 4       Year 5    Sensitivity

   Year 1 Sales                                  $ 45,000            n/a            n/a           n/a         n/a             K9
   Growth Rate                  $000s                n/a            25.0%          20.0%         12.0%      -25.0%     0.0%

   Variable COGS                                      56.0%         55.0%          54.0%         54.0%       54.0%     0.0%
   Fixed COGS (Ex Deprec)                               800           800            800           800         800        K11
   Selling, Marketing & Admin                         6,500         5,000          4,000         3,500       1,500

   Tax Rate                                           38.0%         38.0%          38.0%         38.0%       38.0%
   Investment
     Equipment                  $ 60,000
     Working Capital                3,000
   Incremental Work Cap Inv (% of Sales)              10.0%         10.0%          10.0%             n/a     n/a

   Depreciable Life                                     5.0 Years
   Cost of Capital                                    11.0%

     COGS% + Sensitivity Cell (K11)                                Growth + Sensitivity Cell (K9)



6/8/2010                          Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD                            18
Create Data Table 1 Area
       A. Create Data Table                                             B. Highlight Area

            Added Sales Growth                                 Added
                                                  Added Sales Growth Sales Growth
           Increment     NPV                                  Increment     NPV
                        +E55                                                +E55
              3.0%                                               3.0%
              2.0%                                               2.0%
              1.0%                                               1.0%
              0.0%                                               0.0%
             -1.0%                                              -1.0%
             -2.0%                                              -2.0%
             -3.0%                                              -3.0%


6/8/2010                  Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   19
Running Data Table 1
  From Excel 2003, Click Data then Tables
  From Excel 2007, Click Data then What If
   Analysis then Tables
  Input Range is Arranged as a Column
  Use “Column Input” and Enter Sensitivity Cell
  Click “OK”
                             Table
                         Row Input
                         Column Input                             K9
                                                                 OK                Cancel


6/8/2010        Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD            20
Data Table 1 Result                                          $000s




            Added Sales Growth
           Increment     NPV
              3.0%    $ 7,514.9
              2.0%      6,447.5
              1.0%      5,398.5
              0.0%      4,367.7
             -1.0%      3,354.8
             -2.0%      2,359.6
             -3.0%      1,381.9

6/8/2010   Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   21
Create Data Table 2 Area
 A. Create Data Table
      Net Present Value ($000s)                                    Additional Sales Growth
                          +E55      3.0%          2.0%         1.0%          0.0%          -1.0%     -2.0%   -3.0%
                           3.0%
                           2.0%
             Additional    1.0%
              Margin       0.0%
           Improvement    -1.0%
                          -2.0%
                          -3.0%

 B. Highlight Area
      Net Present Value ($000s)                                     Additional Sales Growth
                          +E55      3.0%          2.0%          1.0%          0.0%          -1.0%    -2.0%   -3.0%
                           3.0%
                           2.0%
             Additional    1.0%
              Margin       0.0%
           Improvement    -1.0%
                          -2.0%
                          -3.0%

6/8/2010                          Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD                   22
Running Data Table 2
  From Excel 2003, Click Data then Tables
  From Excel 2007, Click Data then What If Analysis
   then Tables
  Growth Input Range is Arranged as a Row (Input)
  Margin Input Range is Arranged as a Column (Input)
  Click “OK”
                               Table
                              Row Input                                K9
                              Column Input                             K11
                                                                      OK Cancel
6/8/2010        Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   23
  Data Table 2 Result                                                            $000s




Net Present Value                                              Additional Sales Growth
                 $ 4,367.7      3.0%         2.0%           1.0%        0.0%        -1.0%     -2.0%     -3.0%
                    3.0%     $ 11,801.8   $ 10,661.9      $ 9,541.8 $ 8,441.0 $ 7,359.4 $ 6,296.7 $ 5,252.6
                    2.0%       10,372.8      9,257.1        8,160.7      7,083.2     6,024.5   4,984.3   3,962.4
   Additional       1.0%        8,943.8      7,852.3        6,779.6      5,725.4     4,689.7   3,672.0   2,672.1
    Margin          0.0%        7,514.9      6,447.5        5,398.5      4,367.7     3,354.8   2,359.6   1,381.9
 Improvement       -1.0%        6,085.9      5,042.6        4,017.4      3,009.9     2,019.9   1,047.3      91.7
                   -2.0%        4,657.0      3,637.8        2,636.2      1,652.1       685.0    (265.1) (1,198.6)
                   -3.0%        3,228.0      2,233.0        1,255.1        294.3      (649.8) (1,577.5) (2,488.8)




  6/8/2010                       Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD           24
Other Assumption Analysis
                        First Year Sales                               Additional Annual SG&A Expense
             Sales            NPV        vs. Base        $000s       Increment       NPV      vs. Base
           $ 40,000.0      $ (2,253.0) $ (6,620.7)                   $ 1,000.0 $ 2,076.2 $ (2,291.5)
             42,500.0          1,057.3    (3,310.3)                       500.0      3,221.9    (1,145.7)
             45,000.0          4,367.7         -                             -       4,367.7         -
             47,500.0          7,678.0     3,310.3                       (500.0)     5,513.4     1,145.7
             50,000.0        10,988.3      6,620.7                     (1,000.0)     6,659.1     2,291.5

                 Investment in Equipment                                           Cost of Capital
           Investment     NPV        vs. Base                            COC            NPV        vs. Base
           $ 56,000.0 $ 7,244.1 $ 2,876.4                                10.0%      $ 6,387.7 $ 2,020.1
             58,000.0     5,805.9      1,438.2                           10.5%          5,366.7         999.0
             60,000.0     4,367.7          0.0                           11.0%          4,367.7           -
             62,000.0     2,929.4     (1,438.2)                          11.5%          3,390.1        (977.6)
             64,000.0     1,491.2     (2,876.4)                          12.0%          2,433.3     (1,934.3)

                                             Working Capital Investment
                                        Increment      NPV         vs. Base
                                            7.0%    $ 4,993.6 $         625.9
                                            8.0%       4,784.9          417.3
                                            9.0%       4,576.3          208.6
                                           10.0%       4,367.7            -
                                           11.0%       4,159.0         (208.6)
                                           12.0%       3,950.4         (417.3)
                                           13.0%       3,741.7         (625.9)
6/8/2010                              Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD           25
Quiz
                                                  Exact                         Range
                 Item                            Answer                     From      To
           1.
           2.
           3.
           4.
           5.
           6.
           7.
           8.
           9.
           10.
6/8/2010            Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD       26
Monte Carlo Analysis
     Allows Assumptions to Vary Within Some Range
     Multiple Types of Distributions
     Rerun (Spin the Wheels) the DCF Model 1000s of Times
     Average NPV with Standard Deviation and Range
     @Risk and Crystal Ball are Popular Monte Carlo Software
           First Year Sales                                                                     Margin Improvement

                 50   40
                           41
            49                                    Additional Sales Growth
                            42
            48
                            43
            47
                           44
                 46
                      45




6/8/2010                         Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD                    27
Enhanced Assumptions
  First Year’s Sales: Most Likely - $45.0 million; Worst
   Case - $40.0 million; Best Case $50.0 million.
  Sensitivity – Incremental (Additional) Growth Rate:
   Base Case with 3% Standard Deviation
  Sensitivity – Incremental (Additional) Variable
   COGS: Base Case with 2% Standard Deviation
  Fixed Costs ($000s):   Fixed Cost
                             $700
                                     Probability
                                        0.10
                                             750                   0.20
                                             800                   0.40
                                             850                   0.20
                                             900                   0.10



6/8/2010         Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   28
Enhanced Assumptions (Continued)
  Sensitivity – Incremental (Additional) Selling,
   Marketing, & Administrative: +$200,000; +$100,000;
   $0; -$100,000; -$200,000 with Equal Probability.
  Investment Amount: Most Likely - $60.0 million;
   Worst Case - $64.0 million; Best Case $56.0 million.
  Working Capital Range: 8.5% to 11.5% with Equal
   Likelihood for any Point in Between.
  Cost of Capital Range: Most Likely – 11%; Worst
   Case – 12%; Best Case 10%.

6/8/2010         Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   29
       Monte Carlo Results: Inputs
                                                                         Exhibit 6

                                                       @RISK Input Data Report
Input Data
                                          Growth       Variable COGS                                        Capital       Incremental
   Inputs                 Year 1 Sales   Sensitivity     Sensitivity     Fixed COGS    SM&A Sensitivity    Investment     Work Cap Inv   Cost of Capital
   Simulation                  1             1               1                1              1                 1               1               1
   Iteration / Cell          $F$8          $K$9            $K$11            $F$12          $K$13             $E$17          $F$19            $F$22
   1                  $      43,786.76         2.994%           2.291% $         750.00 $      (200.00) $     60,148.89        11.272%          10.956%
   2                         44,041.98         1.458%           0.278%           800.00            -          61,955.05         9.167%          11.143%
   3                         45,542.10         1.103%          -1.166%           800.00         200.00        60,311.96        10.915%          11.322%
   4                         45,062.30         2.658%          -3.440%           850.00        (100.00)       59,478.84        11.157%          10.566%
   5                         44,549.57         1.691%           0.425%           750.00            -          60,827.56        10.516%          11.332%
   6                         44,005.22         0.200%           0.104%           800.00            -          61,615.37         8.857%          11.629%
   7                         43,399.78         7.226%          -0.868%           900.00        (200.00)       62,474.70        11.070%          10.503%
   8                         44,619.98        -2.977%           1.201%           700.00         200.00        58,763.13        10.856%          11.051%
   9                         44,276.27        -5.699%           2.566%           750.00        (100.00)       59,346.22        10.040%          11.189%
   10                        44,807.44        -1.678%          -0.928%           700.00        (100.00)       62,022.41        11.315%          10.879%
   11                        44,389.20         3.558%          -3.026%           800.00            -          60,639.03         9.624%          11.479%
   12                        44,988.93        -0.336%          -1.260%           800.00        (100.00)       58,949.87         9.291%          11.105%
   13                        42,866.46        -2.189%          -2.675%           800.00            -          58,306.34         8.553%          11.542%
   14                        46,337.29        -0.378%           2.437%           850.00         100.00        58,589.42        10.755%          11.065%
   15                        46,939.90        -2.573%           1.395%           800.00         200.00        60,054.62         9.359%          11.247%
   16                        47,396.25         1.299%          -4.511%           850.00         200.00        58,511.51        10.236%          10.827%
   17                        46,007.81         4.245%          -2.091%           800.00        (100.00)       60,921.70        10.403%          11.404%
   18                        41,863.79         3.373%           0.642%           850.00         100.00        61,710.88         9.933%          10.981%
   19                        45,913.21         0.736%          -0.601%           800.00        (100.00)       63,475.48        11.488%          11.222%
   20                        45,264.74        -3.187%          -0.496%           900.00         200.00        59,169.23         9.058%          11.271%
   21                        43,573.85        -6.229%          -0.706%           850.00            -          61,410.38        11.380%          10.602%
   22                        45,150.36        -2.416%          -1.761%           800.00        (200.00)       59,072.31        10.661%          10.429%
   23                        42,407.48         2.451%           0.941%           700.00        (100.00)       59,416.41         8.893%          11.764%
   24                        46,231.96        -3.778%          -1.485%           800.00        (200.00)       61,136.59        11.012%          11.584%
   25                        43,278.04        -0.640%           1.529%           750.00         100.00        57,856.73        10.558%          10.773%
   26                        46,507.14         0.104%           3.582%           750.00         100.00        60,682.61         9.674%          10.899%
   27                        47,529.86        -1.811%           1.186%           800.00        (200.00)       59,866.94        10.740%          10.731%
   28 6/8/2010               47,076.11         4.838% Analysis -1.912%
                                                  Risk         in Financial Decision Making Samuel C. Weaver, 60,432.88
                                                                                 750.00        (200.00)       PhD               9.010%             30
                                                                                                                                                10.248%
   29                        47,812.30         0.381%           0.524%           900.00         100.00        57,434.89         9.852%          10.789%
                                                                 Exhibit 8


   Monte Carlo Results: Outputs
Output Data
                                             @RISK Output Data Report

                         Cash Flow        Cash Flow      Cash Flow           Cash Flow      Cash Flow           Cash Flow     Net Present
  Outputs                  Year 0           Year 1          Year 2             Year 3         Year 4              Year 5          Value
  Simulation                 1                1               1                  1              1                   1               1
  Iteration / Cell         $E$53            $F$53           $G$53              $H$53          $I$53               $J$53           $E$55
  1                  $       (63,149) $         8,206 $           14,518 $         19,265 $        23,541 $          28,989   $      2,911
  2                          (64,955)         10,016              15,460           20,074          23,799            26,953          2,684
  3                          (63,312)           9,418             15,979           20,827          24,797            29,126          6,322
  4                          (62,479)           9,862             16,778           22,050          26,684            31,410         13,342
  5                          (63,828)           9,229             15,369           20,097          24,052            28,361          3,662
  6                          (64,615)         10,253              15,437           19,835          23,160            25,737            981
  7                          (65,475)           9,005             15,804           21,496          27,438            33,818         10,474
  8                          (61,763)           8,807             14,587           18,501          21,032            24,415           (211)
  9                          (62,346)           9,148             14,146           17,504          19,221            21,599         (4,659)
  10                         (65,022)           9,540             15,831           20,121          23,158            26,961          2,629
  11                         (63,639)         10,533              16,711           22,009          26,709            30,481         10,140
  12                         (61,950)         10,430              16,007           20,504          23,835            26,474          6,633
  13                         (61,306)         10,807              15,594           19,652          22,319            23,796          3,278
  14                         (61,589)           8,564             14,784           19,178          22,520            26,767          2,743
  15                         (63,055)           9,935             15,443           19,531          22,196            24,716          1,539
  16                         (61,512)         10,894              17,766           23,067          27,389            30,944         16,391
  17                         (63,922)         10,128              16,957           22,496          27,633            32,492         11,978
  18                         (64,711)           8,987             14,533           19,254          23,443            27,629          1,135
  19                         (66,475)           9,425             16,237           21,029          24,923            29,681          4,134
  20                         (62,169)         10,278              15,428           19,388          21,848            23,742          1,773
  21                         (64,410)           9,298             14,895           18,324          20,004            22,700         (3,201)
  22                         (62,072)           9,997             16,026           20,244          23,065            26,119          6,571
  23                         (62,416)           9,629             14,729           19,302          23,135            26,339          1,748
  24
   6/8/2010                  (64,137)         10,110              16,325           20,381          22,857
                                             Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD        25,888          2,416 31
  25                         (60,857)           8,554             14,176           18,332          21,425            25,161          1,218
              Number of Inputs                8
              Number of Outputs               7
              Sampling Type                   Latin Hypercube
              Simulation Start Time           17:27:41
              Simulation Stop Time            17:27:42
              Simulation Duration             0:00:01
                                              61932364

Monte Carlo Results: Summary
              Random Seed
              Total Errors                    0




           Output and Input Summary Statistics
              Output Name                     Output Cell       Minimum         Maximum            Mean        Std Dev
              Cash Flow Year 0                $E$53            $ (66,475) $ (59,657) $ (63,014) $                 1,649
              Cash Flow Year 1                $F$53                8,206     11,115      9,683                      697
              Cash Flow Year 2                $G$53               13,573     17,766     15,538                    1,012
              Cash Flow Year 3                $H$53               17,350     23,281     20,049                    1,525
              Cash Flow Year 4                $I$53               19,221     28,565     23,540                    2,394
              Cash Flow Year 5                $J$53               20,789     33,818     27,020                    3,167
              Net Present Value               $E$55               (8,041)    17,760      4,531                    5,854

              Input Name                      Input Cell        Minimum         Maximum            Mean        Std Dev
              Year 1 Sales                    $F$8             $  40,719       $  48,893       $  44,991   $      2,036
              Growth Sensitivity              $K$9               -6.229%          7.226%          0.029%         3.004%
              Variable COGS Sensitivity       $K$11              -4.511%          4.110%         -0.008%         1.955%
              Fixed COGS                      $F$12            $     700       $     900       $     800   $         55
              SM&A Sensitivity                $K$13            $    (200)      $     200       $     -     $        143
              Capital Investment              $E$17            $ 56,657        $ 63,475        $ 60,014    $      1,649
              Incremental Work Cap Inv        $F$19               8.553%         11.488%         10.001%         0.876%
              Cost of Capital                 $F$22              10.218%         11.844%         11.002%         0.413%

6/8/2010                              Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD                    32
                              Milton's Madness NPV - 40 Iterations




     Monte Carlo Results: NPV Summary
            $20.0



            $15.0



            $10.0



PV ($mms)       $5.0



                $0.0
                         0%              25%                          50%                             75%   100%

                ($5.0)



            ($10.0)
                                                        Cumulative Probability
     6/8/2010                      Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD           33
Key Findings
  Average NPV is $4,531,000 Compared to the Static
   Version Results of $4,356,000
  But There is a “New” Richness of the Results
  There is a 15% - 20% Chance that the Project Will
   Have a Negative NPV
            Range of Results is ($8.0) million to $17.8 million
  Requires a Management Team that is Comfortable
      Using this Type of Information to Make a Decision



6/8/2010                 Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   34
Conclusion
  Sensitivity Analysis is Something You May Already Do as
      a Routine Part of Capital Expenditure Analysis
     Most Often, Growth and Margins Influence the Project’s
      NPV the Most
     Consider Using Data Table 2 to Document that Impact
     Software, such as @RISK and Crystal Ball, Facilitate
      Monte Carlo Analysis with Limited Training
     Capital Evaluation Evolution:

           Whims   DCF Model             Sensitivity              Data Tables Monte Carlo


6/8/2010             Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD       35
Further Information
  Please Feel Free to Contact Me:
     To Request a Copy of the Milton’s Madness Case
      Study
     To Request a Related (But Limited) Excel / Monte
      Carlo Model
  Contact Information
     scw0@lehigh.edu that’s scw[zero]@lehigh.edu
     610 – 758 - 5282




6/8/2010         Risk Analysis in Financial Decision Making Samuel C. Weaver, PhD   36

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:8/6/2011
language:English
pages:36