Docstoc

Sales Forecasting Model

Document Sample
Sales Forecasting Model Powered By Docstoc
					Forecasting
Eight Steps to Forecasting
   Determine the use of the forecast
        What objective are we trying to obtain?
   Select the items or quantities that are to be forecasted.
   Determine the time horizon of the forecast.
      Short time horizon – 1 to 30 days
      Medium time horizon – 1 to 12 months
      Long time horizon – more than 1 year
   Select the forecasting model or models
   Gather the data to make the forecast.
   Validate the forecasting model
   Make the forecast
   Implement the results
Forecasting Models
                                        Forecasting
                                        Techniques

   Qualitative                                        Time Series
    Models                                              Methods

                      Delphi
                                                                        Naive
                      Method
                                                                       Moving
                 Jury of Executive
                                                                       Average
                      Opinion
                                                                      Weighted
                   Sales Force
                                                                    Moving Average
                    Composite
                                                                     Exponential
                 Consumer Market
                                                                     Smoothing
                     Survey

                                                                    Trend Analysis
 Causal
 Methods
                                                                     Seasonality
                           Simple                                     Analysis
                          Regression
                           Analysis                                  Multiplicative
                                                                    Decomposition
                            Multiple
                           Regression
                            Analysis
Model Differences
 Qualitative – incorporates judgmental &
  subjective factors into forecast.
 Time-Series – attempts to predict the
  future by using historical data.
 Causal – incorporates factors that may
  influence the quantity being forecasted
  into the model
Qualitative Forecasting Models
   Delphi method
     Iterativegroup process allows experts to make
      forecasts
     Participants:
          decision makers: 5 -10 experts who make the forecast
          staff personnel: assist by preparing, distributing, collecting,
           and summarizing a series of questionnaires and survey
           results
          respondents: group with valued judgments who provide input
           to decision makers
Qualitative Forecasting Models (cont)
   Jury of executive opinion
       Opinions of a small group of high level managers, often in
        combination with statistical models.
        Result is a group estimate.
   Sales force composite
       Each salesperson estimates sales in his region.
       Forecasts are reviewed to ensure realistic.
       Combined at higher levels to reach an overall forecast.
   Consumer market survey.
       Solicits input from customers and potential customers regarding
        future purchases.
       Used for forecasts and product design & planning
Forecast Error                           Forecast Error  At  Ft

   Bias - The arithmetic sum of                      T

    the errors                              MSE   | forecast error | 2 /T
                                                     t 1
   Mean Square Error - Similar to                      T
    simple sample variance                           (At  Ft ) 2 / T
   Variance - Sample variance                         t 1
    (adjusted for degrees of
    freedom)
   Standard Error - Standard
    deviation of the sampling
    distribution
                                     T                         T
   MAD - Mean Absolute       MAD   | forecast error | /T   |At  Ft | / T
    Deviation                       t 1                      t 1
   MAPE – Mean Absolute
    Percentage Error                                     T
                                          MAPE  100 [|At  Ft | / At ] / T
                                                        t 1
    Quantitative Forecasting Models
   Time Series Method
     Naïve
        Whatever happened
         recently will happen
         again this time (same
                                       Ft  Yt 1
         time period)
        The model is simple

                                   Ft  Yt 4 : Quarterly data
         and flexible
        Provides a baseline to
         measure other models
        Attempts to capture
                                   Ft  Yt 12 : Monthly data
         seasonal factors at the
         expense of ignoring
         trend
Naïve Forecast
Wallace Garden Supply
Forecasting
                         Storage Shed Sales




              Actual   Naïve               Absolute   Percent   Squared
    Period    Value   Forecast   Error      Error      Error     Error
January            10   N/A
February           12       10         2         2     16.67%        4.0
March              16       12         4         4     25.00%       16.0
April              13       16        -3         3     23.08%        9.0
May                17       13         4         4     23.53%       16.0
June               19       17         2         2     10.53%        4.0
July               15       19        -4         4     26.67%       16.0
August             20       15         5         5     25.00%       25.0
September          22       20         2         2      9.09%        4.0
October            19       22        -3         3     15.79%        9.0
November           21       19         2         2      9.52%        4.0
December           19       21        -2         2     10.53%        4.0
                                   0.818         3     17.76%     10.091
                                   BIAS        MAD      MAPE        MSE

                     Standard Error (Square Root of MSE) =      3.176619
Naïve Forecast Graph
                                                      Wallace Garden - Naive Forecast


         25




         20




         15
 Sheds




                                                                                                                                Actual Value
                                                                                                                                Naïve Forecast
         10




         5




         0
              February   March   April   May   June          July        August     September   October   November   December
                                                           Period
    Quantitative Forecasting Models
   Time Series Method
     Moving     Averages
          Assumes item
           forecasted will stay
           steady over time.
          Technique will smooth
           out short-term
           irregularities in the time
           series.
                                        k
      k - period moving average   (Actual value in previous k periods) /k
                                    k 1
Moving Averages
 Wallace Garden Supply
 Forecasting
                         Storage Shed Sales



               Actual
    Period     Value                Three-Month Moving Averages
 January            10
 February           12
 March              16
 April              13               10       +   12   +   16   /   3   =   12.67
 May                17               12       +   16   +   13   /   3   =   13.67
 June               19               16       +   13   +   17   /   3   =   15.33
 July               15               13       +   17   +   19   /   3   =   16.33
 August             20               17       +   19   +   15   /   3   =   17.00
 September          22               19       +   15   +   20   /   3   =   18.00
 October            19               15       +   20   +   22   /   3   =   19.00
 November           21               20       +   22   +   19   /   3   =   20.33
 December           19               22       +   19   +   21   /   3   =   20.67
Moving Averages Forecast
Wallace Garden Supply
Forecasting                  3 period moving average
                                                                     Actual Value - Forecast


Input Data                                Forecast Error Analysis
                                                                       Absolute         Squared         Absolute
  Period      Actual Value                  Forecast      Error          error           error           % error
Month 1                 10
Month 2                 12
Month 3                 16
Month 4                 13                      12.667       0.333           0.333              0.111     2.56%
Month 5                 17                      13.667       3.333           3.333             11.111    19.61%
Month 6                 19                      15.333       3.667           3.667             13.444    19.30%
Month 7                 15                      16.333      -1.333           1.333              1.778     8.89%
Month 8                 20                      17.000       3.000           3.000              9.000    15.00%
Month 9                 22                      18.000       4.000           4.000             16.000    18.18%
Month 10                19                      19.000       0.000           0.000              0.000     0.00%
Month 11                21                      20.333       0.667           0.667              0.444     3.17%
Month 12                19                      20.667      -1.667           1.667              2.778     8.77%
                                              Average       12.000           2.000              6.074    10.61%
Next period        19.667                                    BIAS            MAD                 MSE      MAPE
Moving Averages Graph
                                  Three Period Moving Average


         25




         20




         15
 Value




                                                                                   Actual Value
                                                                                   Forecast

         10




         5




         0
              1   2   3   4   5       6          7         8    9   10   11   12
                                          Time
    Quantitative Forecasting Models
   Time Series Method
      Weighted        Moving Averages
              Assumes data from some periods are more
               important than data from other periods (e.g.
               earlier periods).
              Use weights to place more emphasis on some
               periods and less on others.


    k - period weighted moving average 
     k                                                                 k

     (Weight for each period i)(Actual value in previous k periods) /  (weights)
    i 1                                                              i 1
Weighted Moving Average
 Wallace Garden Supply
 Forecasting
  Storage Shed Sales



                Actual
    Period      Value   Weights     Three-Month Weighted Moving Averages
 January             10     0.222
 February            12     0.593
 March               16     0.185
 April               13             2.2   +   7.1   +    3    /   1   =   12.298
 May                 17             2.7   +   9.5   +   2.4   /   1   =   14.556
 June                19             3.5   +   7.7   +   3.2   /   1   =   14.407
 July                15             2.9   +   10    +   3.5   /   1   =   16.484
 August              20             3.8   +   11    +   2.8   /   1   =   17.814
 September           22             4.2   +   8.9   +   3.7   /   1   =   16.815
 October             19             3.3   +   12    +   4.1   /   1   =   19.262
 November            21             4.4   +   13    +   3.5   /   1   =   21.000
 December            19             4.9   +   11    +   3.9   /   1   =   20.036

 Next period      20.185

 Sum of weights =           1.000
Weighted Moving Average
Wallace Garden Supply
Forecasting                  3 period weighted moving average


Input Data                                   Forecast Error Analysis
                                                                           Absolute     Squared    Absolute
  Period      Actual value    Weights          Forecast         Error       error        error     % error
Month 1                 10        0.222
Month 2                 12        0.593
Month 3                 16        0.185
Month 4                 13                         12.298          0.702        0.702      0.492     5.40%
Month 5                 17                         14.556          2.444        2.444      5.971    14.37%
Month 6                 19                         14.407          4.593        4.593     21.093    24.17%
Month 7                 15                         16.484         -1.484        1.484      2.202     9.89%
Month 8                 20                         17.814          2.186        2.186      4.776    10.93%
Month 9                 22                         16.815          5.185        5.185     26.889    23.57%
Month 10                19                         19.262         -0.262        0.262      0.069     1.38%
Month 11                21                         21.000          0.000        0.000      0.000     0.00%
Month 12                19                         20.036         -1.036        1.036      1.074     5.45%
                                                  Average          1.988        6.952      6.952    10.57%
Next period        20.185                                          BIAS         MAD         MSE      MAPE

Sum of weights =                   1.000
Quantitative Forecasting Models
   Time Series Method
     Exponential           Smoothing
         Moving average technique that requires little
          record keeping of past data.
         Uses a smoothing constant α with a value between
          0 and 1. (Usual range 0.1 to 0.3)

Forecast for period t 
forecast for period t - 1   (actual value in period t - 1 - forecast for period t - 1)
Exponential Smoothing Data
Wallace Garden Supply
Forecasting
                        Storage Shed Sales


                               Exponential Smoothing
              Actual
  Period      Value             Ft       α          At       Ft           Ft+1
January            10           10       0.1
February           12           10   +   0.1   *(   10   -   10   )   =   10.000
March              16           10   +   0.1   *(   12   -   10   )   =   10.200
April              13           10   +   0.1   *(   16   -   10   )   =   10.780
May                17           11   +   0.1   *(   13   -   11   )   =   11.002
June               19           11   +   0.1   *(   17   -   11   )   =   11.602
July               15           12   +   0.1   *(   19   -   12   )   =   12.342
August             20           12   +   0.1   *(   15   -   12   )   =   12.607
September          22           13   +   0.1   *(   20   -   13   )   =   13.347
October            19           13   +   0.1   *(   22   -   13   )   =   14.212
November           21           14   +   0.1   *(   19   -   14   )   =   14.691
December           19           15   +   0.1   *(   21   -   15   )   =   15.322
Exponential Smoothing
Wallace Garden Supply
Forecasting                  Exponential smoothing


Input Data                       Forecast Error Analysis
                                                                Absolute     Squared     Absolute
  Period      Actual value         Forecast          Error       error        error      % error
Month 1                 10             10.000
Month 2                 12             10.000           2.000        2.000       4.000    16.67%
Month 3                 16             10.838           5.162        5.162      26.649    32.26%
Month 4                 13             13.000           0.000        0.000       0.000     0.00%
Month 5                 17             13.000           4.000        4.000      16.000    23.53%
Month 6                 19             14.675           4.325        4.325      18.702    22.76%
Month 7                 15             16.487          -1.487        1.487       2.211     9.91%
Month 8                 20             15.864           4.136        4.136      17.106    20.68%
Month 9                 22             17.596           4.404        4.404      19.391    20.02%
Month 10                19             19.441          -0.441        0.441       0.194     2.32%
Month 11                21             19.256           1.744        1.744       3.041     8.30%
Month 12                19             19.987          -0.987        0.987       0.973     5.19%
                                     Average                         2.608       9.842    14.70%
   Alpha            0.419                                            MAD          MSE      MAPE

Next period        19.573
                               Sheds




                          10
                                                         15
                                                              20
                                                                   25




                  0
                      5
    Ja
      nu
        ar
           y

 Fe
   br
      ua
        ry



     M
      ar
         c   h



         Ap
           ri l



         M
          ay



         Ju
           ne



          Ju
            ly


     Au
       gu
         st
                                                                        Exponential Smoothing




Se
  pt
    em
      be
        r

    O
        ct
          ob
            er
                                                                                                Exponential Smoothing




N
    ov
       em
         be
           r

D
    ec
       em
         be
           r
                               Forecast
                                          Actual value
Trend & Seasonality
   Trend analysis
        technique that fits a trend equation (or curve) to a series of
        historical data points.
        projects the curve into the future for medium and long term
        forecasts.
   Seasonality analysis
       adjustment to time series data due to variations at certain
        periods.
       adjust with seasonal index – ratio of average value of the item in
        a season to the overall annual average value.
       example: demand for coal & fuel oil in winter months.
Linear Trend Analysis
              Midwestern Manufacturing Sales

                                       Sales(in units) vs. Time

Scatter Diagram        160

 Actual      Period    140
value (or)   number
    Y         (or) X   120
    74        1995
    79        1996     100
    80        1997
    90        1998      80                                          Period number (or) X
   105        1999
   142        2000      60
   122        2001
                        40
                        20
                         0
                         1994   1996     1998      2000      2002
Least Squares for Linear Regression
                                  Midwestern Manufacturing
                                            Least Squares Method
  Values of Dependent Variables




                                                     Time
Least Squares Method
 ^
 Y  a  bX
Where

 ^
 Y   = predicted value of the dependent variable (demand)

 X = value of the independent
 variable (time)

                                                            _ _
 a = Y-axis intercept
                                              [ XY - n X Y ]
                                        b=
 b = slope of the regression line                       _
                                                           2
                                                X - n X 
                                                   2

                                                           
Linear Trend Data & Error Analysis
Midwestern Manufacturing Company
Forecasting            Linear trend analysis


Input Data                                     Forecast Error Analysis
              Actual value Period number                                    Absolute   Squared Absolute
   Period        (or) Y        (or) X            Forecast       Error        error      error   % error
Year 1                   74      1                   67.250         6.750        6.750   45.563   9.12%
Year 2                   79      2                   77.786         1.214        1.214    1.474   1.54%
Year 3                   80      3                   88.321        -8.321        8.321   69.246 10.40%
Year 4                   90      4                   98.857        -8.857        8.857   78.449   9.84%
Year 5                  105      5                  109.393        -4.393        4.393   19.297   4.18%
Year 6                  142      6                  119.929       22.071       22.071 487.148 15.54%
Year 7                  122      7                  130.464        -8.464        8.464   71.644   6.94%
                                                   Average                       8.582 110.403    8.22%
Intercept          56.714                                                        MAD       MSE    MAPE
Slope              10.536

Next period       141.000       8
Least Squares Graph
                             Trend Analysis

        160



        140

                             y = 10.536x + 56.714
        120



        100
Value




        80



        60



        40



        20



         0
              1   2   3                    4                            5   6   7
                                          Time

                          Actual values        Linear (Actual values)
Seasonality Analysis
                                     Ratio = demand / average demand
Eichler Supplies
                          Average           Seasonal
Year     Month     Demand Demand    Ratio    Index
 1      January      80     94      0.851    0.957
        February     75     94      0.798    0.851
                                                       Seasonal Index – ratio of the
         March       80     94      0.851    0.904     average value of the item in a
          April      90     94      0.957    1.064     season to the overall average
          May        115    94      1.223    1.309
          June       110    94      1.170    1.223
                                                       annual value.
          July       100    94      1.064    1.117
         August      90     94      0.957    1.064     Example: average of year 1
       September     85     94      0.904    0.957
        October      75     94      0.798    0.851     January ratio to year 2 January
       November      75     94      0.798    0.851     ratio.
       December      80     94      0.851    0.851
                                                        (0.851 + 1.064)/2 = 0.957
 2      January      100    94      1.064
        February     85     94      0.904
         March       90     94      0.957
          April      110    94      1.170              If Year 3 average monthly demand is
          May        131    94      1.394
          June       120    94      1.277              expected to be 100 units.
          July       110    94      1.170              Forecast demand Year 3 January:
         August      110    94      1.170                 100 X 0.957 = 96 units
       September     95     94      1.011              Forecast demand Year 3 May:
        October      85     94      0.904
                                                          100 X 1.309 = 131 units
       November      85     94      0.904
       December      80     94      0.851
Deseasonalized Data
   Going back to the conceptual model, solve
    for trend:
         Trend = Y / Season
        (96 units/ 0.957 = 100.31)
 This eliminates seasonal variation and
  isolates the trend
 Now use the Least Squares method to
  compute the Trend
Forecast
   Now that we have the Seasonal Indices
    and Trend, we can reseasonalize the data
    and generate the forecast
       Y = Trend x Seasonal Index

				
DOCUMENT INFO
Description: Sales Forecasting Model document sample