# Sales Forecasting Model

Document Sample

```					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
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

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
   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%

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%
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
Shared By:
Categories:
Stats:
 views: 132 posted: 7/7/2010 language: English pages: 30
Description: Sales Forecasting Model document sample