Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Forecasting Using Spreadsheets - Excel

VIEWS: 28 PAGES: 7

Forecasting Using Spreadsheets document sample

More Info
									Teaching Financial Forecasting Using Simulation
Supporting Spreadsheets

        Guide to Workbook:

                              Overview     (This page.) Presents an overall guide to the workbook's contents.
                               Baseline    The deterministic, baseline model for the company's income statement.
                             Traditional   Illustration of best-case/worst-case and data tables.
                        RV Generation      Basics on generating selected random variables.
                      Single RV Model      Simulation results of effect of a single RV on model.
                     Multiple RV Model     Simulation results of effects of multiple RVs on model.
       Integrated Financial Statements     Integration of Income Statement with Other Financial Statements


         Revision          Date                                            Description                              Initials
            A           21-Nov-01          Created Spreadsheet                                                        acj3
            B           3-Dec-01           Modified Input Cells' Format (Multiple RV Model)                           acj3
            C            3-Apr-02          Spreadsheet-wide revisions to incorporate reviewer's comments.          acj3 & ad
            D
            E
            F
Widgets-R-Us Simplified Income Statement:
                                       FY01       FY02       FY03     Formulas for FY03
       Net Sales              105%   $ 1,000    $ 1,050    $ 1,103 =E4*$C$4
       Operating Costs         60%   $   600    $   630    $   662 =$C$5*F4
                    EBITDA           $   400    $   420    $   441 =F4-F5
       Depreciation                  $   100    $   100    $   100 100
       Other Mktg & Admin     20%    $   200    $   210    $   221 =$C$8*F4
                       EBIT          $   100    $   110    $   121 =F6-SUM(F7:F8)
       Interest               10%    $      2   $      2   $      2 2
                       EBT           $    98    $   108    $   119 =F9-F10
       Taxes (30%)            30%    $    29    $    32    $    36 =MAX(0,$C$12*F11)
                Net Income           $    69    $    76    $    83 =F11-F12
Traditional Methods of Analysis:
       Best-case/Worst-case Analysis:
                                                                                   Worst-case             Baseline          Best-case
                                                            Actual                  Forecast              Forecast          Forecast
                                                             FY01                     FY02                  FY02               FY02
                Net Sales                                  $ 1,000       95%        $    950       105%   $ 1,050    115%    $ 1,150
                Operating Costs                    60%     $   600       64%        $    608        60%   $   630     56%    $   644
                                     EBITDA                $   400                  $    342              $   420            $   506
                Depreciation                               $   100                  $    100              $   100            $   100
                Other Mktg & Admin                 20%     $   200       24%        $    228       20%    $   210    16%     $   184
                                       EBIT                $   100                  $     14              $   110            $   222
                Interest                           10%     $      2      10%        $      2       10%    $      2   10%     $      2
                                        EBT                $    98                  $     12              $   108            $   220
                Taxes (30%)                        30%     $    29       30%        $      4       30%    $     32   30%     $    66
                                  Net Income               $    69                  $      8              $     76           $   154




       Two-factor Data Table Analysis:

                                                            Actual                  Forecast
                                                             FY01                     FY02
                Net Sales                                  $ 1,000       105%       $ 1,050
                Operating Costs                    60%     $   600        60%       $    630
                                     EBITDA                $   400                  $    420
                Depreciation                               $   100                  $    100
                Other Mktg & Admin                 20%     $   200       20%        $    210
                                       EBIT                $   100                  $    110
                Interest                           10%     $      2      10%        $      2
                                        EBT                $    98                  $    108
                Taxes (30%)                        30%     $    29       30%        $     32
                                  Net Income               $    69                  $     76


                                                                                  Sales Growth
                                               $      76       95%       100%         105%     110%        115%
                                                   56%     $     88.2   $ 96.6      $ 105.0 $ 113.4       $ 121.8
                                                   58%     $     74.9   $ 82.6      $    90.3 $ 98.0      $ 105.7
                           Operating Costs         60%     $     61.6   $ 68.6      $    75.6 $ 82.6      $ 89.6
                                                   62%     $     48.3   $ 54.6      $    60.9 $ 67.2      $ 73.5
                                                   64%     $     35.0   $ 40.6      $    46.2 $ 51.8      $ 57.4


                                                                                  Sales Growth
                                               $      76       95%       100%         105%     110%        115%
                                                   16%     $     88.2   $ 96.6      $ 105.0 $ 113.4       $ 121.8
                       Other Marketing &           18%     $     74.9   $ 82.6      $    90.3 $ 98.0      $ 105.7
                  Administrative Expenses          20%     $     61.6   $ 68.6      $    75.6 $ 82.6      $ 89.6
                                                   22%     $     48.3   $ 54.6      $    60.9 $ 67.2      $ 73.5
                                                   24%     $     35.0   $ 40.6      $    46.2 $ 51.8      $ 57.4


                                                                                 Operating Costs
                                               $      76       56%       58%          60%          62%      64%
                                                   16%         134.4     119.7         105         90.3     75.6
                       Other Marketing &           18%         119.7      105         90.3         75.6     60.9
                  Administrative Expenses          20%          105      90.3         75.6         60.9     46.2
                                                   22%         90.3      75.6         60.9         46.2     31.5
                                                   24%         75.6      60.9         46.2         31.5     16.8
RV Generation Basics:
      Uniform Distribution:

                   U(0,1): 0.619245        U(0.2,0.4): 0.213651
                           0.293454                    0.265719
                            0.83689                    0.370217
                           0.190512                    0.233854
                           0.239729                    0.275069
                           0.076588                    0.247556
                           0.774103                    0.382942

      Standard Normal (Z) Distribution:

                    1     0.472704         VBA Code for Function Called "Zscore":
                    2     0.709124                  Function Zscore()
                    3     0.697213                  '
                    4     0.564752                  ' Randomly generates a standard normal variate.
                    5     0.201429                  '
                    6     0.714654                             Sum = 0
                    7     0.213313                             For Index = 1 To 12
                    8      0.49441                               Sum = Sum + Rnd()
                    9     0.788616                             Next Index
                   10     0.595708                             Zscore = Sum
                   11     0.941953                  End Function
                   12     0.110376
                Sum =>    6.504254
                                -6         Alternate Formulation:          2.284047 =NORMSINV(RAND())
                 Z-score: 0.504254

      Normal Distribution ( m, s):
                                           Alternate Formulation:          1.004663 =NORMINV(RAND(),D33,D34)
                   Mean:      1.05
                 Std Dev:     0.05

          Normal Variate: 1.075213 =D33+D29*D34
Single RV Simulation - Sales Growth
                                             Actual               Forecast              Assumption(s): (changeable inputs are blue-highlighted .)
                                              FY01                 FY02                  1. Sales growth is normally-distributed:                                                                                                                      Relative
        Net Sales                           $ 1,000     105%     $    1,050                        Mean         105%                                                                                              Lower    Upper   Midpoint Frequency Frequency
        Operating Costs             60%     $   600      60%     $      630                        Std Dev       10%                                                                                                20       40       30       0.0         0
                     EBITDA                 $   400              $      420                                                                                                                                         40       60       50       20.0      0.2
        Depreciation                        $   100              $      100                                                                                                                                         60       80       70       38.0      0.38
        Other Mktg & Admin          20%     $   200     20%      $      210                                                                                                                                         80      100       90       38.0      0.38
                         EBIT               $   100              $      110                                                                                                                                        100      120      110       4.0       0.04
        Interest                    10%     $      2    10%      $        2                                                                                                                                        120      140      130       0.0         0
                         EBT                $    98              $      108
        Taxes (30%)                 30%     $    29     30%      $       32
                  Net Income                $    69              $       76
                                                                                                                                                                                                              1
                                                                                                                                                             Net Income
                                                                                                                                                                                                            0.9
FY02 Forecast:
                                                                 Other Mktg   Pre-tax                Net                                            Mean                 $     75.75                        0.8




                                                                                                                                                                                       Relative Frequency
  Run           Zscore              Sales   Op Costs   EBITDA     & Admin      EBIT      Taxes     Income                                           Standard Error       $   1.4938                         0.7
   1        -1.574675126        $     892.5 $ 535.5    $ 357.0   $    178.5   $  78.5   $ 23.0     $ 53.6                                           Median               $     77.35                        0.6
   2         -1.10087459        $     939.9 $ 563.9    $ 376.0   $    188.0   $  88.0   $ 25.8     $ 60.2                                           Mode                    #N/A
   3        1.373991139         $   1,187.4 $ 712.4    $ 475.0   $    237.5   $ 137.5   $ 40.6     $ 94.8                                           Standard Deviation   $ 14.9382                          0.5
   4        0.009249231         $   1,050.9 $ 630.6    $ 420.4   $    210.2   $ 110.2   $ 32.5     $ 75.7                                           Sample Variance       223.1484                          0.4
   5        0.982175696         $   1,148.2 $ 688.9    $ 459.3   $    229.6   $ 129.6   $ 38.3     $ 89.4                                           Kurtosis               -0.6495                          0.3
   6        0.133207497         $   1,063.3 $ 638.0    $ 425.3   $    212.7   $ 112.7   $ 33.2     $ 77.5                                           Skewness               -0.0617
                                                                                                                                                                                                            0.2
   7        -1.241966803        $     925.8 $ 555.5    $ 370.3   $    185.2   $  85.2   $ 24.9     $ 58.2                                           Range                $     62.32
   8        1.611579147         $   1,211.2 $ 726.7    $ 484.5   $    242.2   $ 142.2   $ 42.1     $ 98.2                                           Minimum              $      45.6                        0.1
   9        -0.605903632        $     989.4 $ 593.6    $ 395.8   $    197.9   $  97.9   $ 28.8     $ 67.1                                           Maximum              $     108.0                          0
   10       -0.744375064        $     975.6 $ 585.3    $ 390.2   $    195.1   $  95.1   $ 27.9     $ 65.2                                           Sum                  $ 7,575.1
                                                                                                                                                                                                                      30      50        70     90      110        130
   11       0.502606513         $   1,100.3 $ 660.2    $ 440.1   $    220.1   $ 120.1   $ 35.4     $ 82.6                                           Count                    100
   12       0.336602645         $   1,083.7 $ 650.2    $ 433.5   $    216.7   $ 116.7   $ 34.4     $ 80.3                                                                                                                          Net Income (Midpoint)
   13       1.424126865         $   1,192.4 $ 715.4    $ 477.0   $    238.5   $ 138.5   $ 40.9     $ 95.5
   14       -0.692628543        $     980.7 $ 588.4    $ 392.3   $    196.1   $  96.1   $ 28.2     $ 65.9
   15       0.318273862         $   1,081.8 $ 649.1    $ 432.7   $    216.4   $ 116.4   $ 34.3     $ 80.1
   16       2.141120933         $   1,264.1 $ 758.5    $ 505.6   $    252.8   $ 152.8   $ 45.2     $ 105.6
   17       -0.303845915        $   1,019.6 $ 611.8    $ 407.8   $    203.9   $ 103.9   $ 30.6     $ 71.3
   18        1.63091685         $   1,213.1 $ 727.9    $ 485.2   $    242.6   $ 142.6   $ 42.2     $ 98.4
   19       0.235232349         $   1,073.5 $ 644.1    $ 429.4   $    214.7   $ 114.7   $ 33.8     $ 78.9
   20       -1.447514794        $     905.2 $ 543.1    $ 362.1   $    181.0   $  81.0   $ 23.7     $ 55.3
Multiple RV Simulation
                                             Actual               Forecast              Assumption(s): (changeable inputs are blue-highlighted .)
                                              FY01                 FY02                  1. Sales growth is normally-distributed:                                                                                                                     Relative
        Net Sales                           $ 1,000     105%     $    1,050                        Mean          105%                                                                                            Lower    Upper   Midpoint Frequency Frequency
        Operating Costs             60%     $   600      60%     $      630                        Std Dev        10%                                                                                              20       40       30       0.0         0
                     EBITDA                 $   400              $      420              2. Operating Cost is uniformly distributed:                                                                               40       60       50       11.0      0.11
        Depreciation                        $   100              $      100                           0.56         to          0.64                                                                                60       80       70       46.0      0.46
        Other Mktg & Admin          20%     $   200     20%      $      210              3. Other Mktg & Admin Expenses is uniformly distributed:                                                                  80      100       90       37.0      0.37
                         EBIT               $   100              $      110                           0.16         to          0.24                                                                               100      120      110       6.0       0.06
        Interest                    10%     $      2    10%      $        2                                                                                                                                       120      140      130       0.0         0
                         EBT                $    98              $      108
        Taxes (30%)                 30%     $    29     30%      $       32
                  Net Income                $    69              $       76

                                                                                                                                                             Net Income                                      1
FY02 Forecast:                                                                                                                                                                                             0.9




                                                                                                                                                                                      Relative Frequency
                                                                 Other Mktg   Pre-tax                Net                                            Mean                 $ 77.57                           0.8
  Run           Zscore              Sales   Op Costs   EBITDA     & Admin      EBIT      Taxes     Income                                           Standard Error       $ 1.2960                          0.7
   1        1.281905208         $   1,178.2 $ 680.0    $ 498.2   $    241.7   $ 156.5   $ 46.4     $ 108.2                                          Median               $ 78.77                           0.6
   2        -0.879471173        $     962.1 $ 577.2    $ 384.8   $    192.4   $ 92.4    $ 27.1     $ 63.3                                           Mode                    #N/A
   3         -1.20227662        $     929.8 $ 557.9    $ 371.9   $    186.0   $ 86.0    $ 25.2     $ 58.8                                           Standard Deviation   $12.9595
                                                                                                                                                                                                           0.5
   4        -0.412500927        $   1,008.7 $ 605.2    $ 403.5   $    201.7   $ 101.7   $ 29.9     $ 69.8                                           Sample Variance      167.9497                          0.4
   5        -0.232758399        $   1,026.7 $ 616.0    $ 410.7   $    205.3   $ 105.3   $ 31.0     $ 72.3                                           Kurtosis              -0.2734                          0.3
   6        -1.144205019        $     935.6 $ 561.3    $ 374.2   $    187.1   $ 87.1    $ 25.5     $ 59.6                                           Skewness               0.1219                          0.2
   7        -0.498544165        $   1,000.1 $ 600.1    $ 400.1   $    200.0   $ 100.0   $ 29.4     $ 68.6                                           Range                $ 59.02
                                                                                                                                                                                                           0.1
   8        -1.669293657        $     883.1 $ 529.8    $ 353.2   $    176.6   $ 76.6    $ 22.4     $ 52.2                                           Minimum              $     49.5
   9         0.26254337         $   1,076.3 $ 645.8    $ 430.5   $    215.3   $ 115.3   $ 34.0     $ 79.3                                           Maximum              $ 108.5                             0
   10        -0.21823414        $   1,028.2 $ 616.9    $ 411.3   $    205.6   $ 105.6   $ 31.1     $ 72.5                                           Sum                  $ 7,757.4                                   30      50      70     90      110      130
   11       0.901040497         $   1,140.1 $ 684.1    $ 456.0   $    228.0   $ 128.0   $ 37.8     $ 88.2                                           Count                    100
                                                                                                                                                                                                                               Net Income (Midpoint)
   12       0.610054085         $   1,111.0 $ 666.6    $ 444.4   $    222.2   $ 122.2   $ 36.1     $ 84.1
   13       -0.660519625        $     983.9 $ 590.4    $ 393.6   $    196.8   $ 96.8    $ 28.4     $ 66.4
   14       0.420414649         $   1,092.0 $ 655.2    $ 436.8   $    218.4   $ 118.4   $ 34.9     $ 81.5
   15       -0.757932189        $     974.2 $ 584.5    $ 389.7   $    194.8   $ 94.8    $ 27.9     $ 65.0
   16       1.867151839         $   1,236.7 $ 742.0    $ 494.7   $    247.3   $ 147.3   $ 43.6     $ 101.7
   17       -0.655939554        $     984.4 $ 590.6    $ 393.8   $    196.9   $ 96.9    $ 28.5     $ 66.4
   18       -0.694512196        $     980.5 $ 588.3    $ 392.2   $    196.1   $ 96.1    $ 28.2     $ 65.9
   19       0.344127748         $   1,084.4 $ 650.6    $ 433.8   $    216.9   $ 116.9   $ 34.5     $ 80.4
   20       0.688599247         $   1,118.9 $ 671.3    $ 447.5   $    223.8   $ 123.8   $ 36.5     $ 85.2
   21       0.141659017         $   1,064.2 $ 638.5    $ 425.7   $    212.8   $ 112.8   $ 33.2     $ 77.6
   22       0.815326164         $   1,131.5 $ 678.9    $ 452.6   $    226.3   $ 126.3   $ 37.3     $ 87.0
Widgets-R-Us
Integrated Financial Statements

            Balance Sheet

Assets                                              FY01          FY02          FY03           Liabilities                         FY01          FY02          FY03
Cash                                            $       50    $       50    $       50    5%   A/P                             $        30   $        33   $        36
Short-term investments                          $       30    $       30    $       30         ST debt                         $      -      $      -      $      -
AR                                        10%   $      100    $      110    $      121         Accruals                        $        70   $        70   $        70
                                                                                                  Total Current Liabilities    $     100     $     103     $     106
Inventories                               10%   $       60    $       66    $       73         LT debt                         $        19   $        28   $        38
Total Current Assets                            $      240    $      256    $      274                     Total Liabilities   $     119     $     131     $     144
Net plant and equipment                         $      200    $      200    $      200         Common Stock                    $     200     $     200     $     200
                                                                                               Retained Earnings               $     121     $     125     $     129
                                                                                                    Total Common Equity        $     321     $     325     $     329
                          Total Assets          $      440    $      456    $      474           Total Liabilities & Equity    $     440     $     456     $     474
                                                                                               Additional Funds Needed                       $         9   $        10




           Income Statement                                                                    Statement of Cash Flows
                                                    FY01          FY02          FY03                                                             FY01          FY02          FY03
Net Sales                                 110% $      1,000   $     1,100   $     1,210        Free Cash Flow
Operating Costs                           60% $         600   $       660   $       726        NOPAT                                                       $       84    $      99
                              EBITDA           $        400   $       440   $       484        NOWC                                          $      110    $      123    $     137
Depreciation                                   $        100   $       100   $       100        Total Operating Capital                       $      310    $      323    $     337
Other Marketing & Administrative          20% $         200   $       220   $       242        Net investment in operating assets                          $       13    $      14
                                   EBIT        $        100   $       120   $       142        Free Cash Flow                                              $       71    $      85
Interest                                  10% $           2   $         2   $         3
                                   EBT         $         98   $       118   $       139
Taxes (30%)                               30% $          29   $        35   $        42
                           Net Income          $         69   $        83   $        97
Common dividends                          70% $          48   $        58   $        68
Change in Equity                               $         21   $        25   $        29

								
To top