Docstoc

Sales Forecasting - PowerPoint

Document Sample
Sales Forecasting - PowerPoint Powered By Docstoc
					          FORECASTING TECHNIQUES

Chapter 16
  Qualitative Approaches to Forecasting
  Quantitative Approaches to Forecasting
  The Components of a Time Series
  Using Smoothing Methods in Forecasting
  Measures of Forecast Accuracy
  Using Trend Projection in Forecasting
  Using Regression Analysis in Forecasting




                       Dr. C. Lightner           1
                 Fayetteville State University
            Forecasting Introduction

An essential aspect of managing any organization is
planning for the future.
Organizations employ forecasting techniques to
determine future inventory, costs, capacities, and
interest rate changes.
There are two basic approaches to forecasting:
     -Qualitative
     -Quantitative




                     Dr. C. Lightner                  2
               Fayetteville State University
     Qualitative Approaches to Forecasting

Delphi Approach
– A panel of experts, each of whom is physically separated from
  the others and is anonymous, is asked to respond to a
  sequential series of questionnaires.
– After each questionnaire, the responses are tabulated and the
  information and opinions of the entire group are made known to
  each of the other panel members so that they may revise their
  previous forecast response.
– The process continues until some degree of consensus is
  achieved.




                        Dr. C. Lightner                      3
                  Fayetteville State University
       Qualitative Approaches (continued)

Scenario Writing
– Scenario writing consists of developing a conceptual scenario
  of the future based on a well defined set of assumptions.
– After several different scenarios have been developed, the
  decision maker determines which is most likely to occur in the
  future and makes decisions accordingly.




                        Dr. C. Lightner                        4
                  Fayetteville State University
       Qualitative Approaches (continued)

Subjective or Interactive Approaches
– These techniques are often used by committees or panels
  seeking to develop new ideas or solve complex problems.
– They often involve "brainstorming sessions".
– It is important in such sessions that any ideas or opinions be
  permitted to be presented without regard to its relevancy and
  without fear of criticism.




                        Dr. C. Lightner                            5
                  Fayetteville State University
     Quantitative Approaches to Forecasting

Quantitative methods are based on an analysis of historical data
concerning one or more time series.
A time series is a set of observations measured at successive
points in time or over successive periods of time.
If the historical data used are restricted to past values of the series
that we are trying to forecast, the procedure is called a time series
method.
If the historical data used involve other time series that are believed
to be related to the time series that we are trying to forecast, the
procedure is called a causal method.
Quantitative approaches are generally preferred. In this chapter we
will focus on quantitative approaches to forecasting.

                          Dr. C. Lightner                           6
                    Fayetteville State University
                Time Series Data

Time Series Data is usually plotted on a graph to
determine the various characteristics or components of
the time series data.
There are 4 Major Components: Trend, Cyclical,
Seasonal, and Irregular Components.




                      Dr. C. Lightner               7
                Fayetteville State University
            Components of a Time Series

The trend component accounts for the gradual shifting
of the time series over a long period of time.
Any regular pattern of sequences of values above and
below the trend line is attributable to the cyclical
component of the series.
The seasonal component of the series accounts for
regular patterns of variability within certain time periods,
such as over a year.
The irregular component of the series is caused by
short-term, unanticipated and non-recurring factors that
affect the values of the time series. One cannot attempt
to predict its impact on the time series in advance.

                         Dr. C. Lightner                   8
                   Fayetteville State University
                Time Series Data

We will learn the following Forecasting Approaches:
Smoothing
Trend Projections




                      Dr. C. Lightner                 9
                Fayetteville State University
   Excel Instructions for Drawing a Scatter Plot

1. Enter data in the Excel spreadsheet.
2. Click on Insert on the toolbar and then click on the Chart tab. The
   Chart Wizard will appear. In step 1 on select the XY (scatter) chart
   type and then click next.
3. In step 2 specify the cells where your data is located in the data
   range box.
4. In step 3 you can give your chart a title and label your axes. In
   step 4 specify where you want the chart to be placed.




                             Dr. C. Lightner                       10
                       Fayetteville State University
                 Example: Robert’s Drugs
    During the past ten weeks, sales of cases of Comfort brand
headache medicine at Robert's Drugs have been as follows:

             Week Sales                Week           Sales
              1    110                   6             120
              2    115                   7             130
              3    125                   8             115
              4    120                   9             110
              5    125                  10             130

    Plot this data.



                            Dr. C. Lightner                      11
                      Fayetteville State University
             Plot Robert’s Drugs Example

Excel Spreadsheet Showing Input Data. Specify cells A4:B13 as the Data
Range.                  A                          B
                       1     Robert's Drugs
                       2
                      3          Week (t )            Salest
                      4                 1              110
                      5                 2              115
                      6                 3              125
                      7                 4              120
                      8                 5              125
                      9                 6              120
                     10                 7              130
                     11                 8              115
                     12                 9              110
                     13                10              130
                     14                11
                            Dr. C. Lightner                              12
                      Fayetteville State University
                             Plot Robert’s Drugs Example
                                                                           I labeled
                                                                           Robert’s Drug
                                 Robert's Drug Example
                                                                           Example as
                                                                           The Chart title
                       135
                       130
                       125
               Sales




                       120
                       115
I labeled              110
Sales as               105                                                I labeled
My Value (y)                                                              Week, t as
                             0              5                10      15
axis                                                                      My Value (x)
                                                Week, t                   axis

                                           Dr. C. Lightner                          13
                                     Fayetteville State University
                Smoothing Methods

In cases in which the time series is fairly stable and
has no significant trend, seasonal, or cyclical effects,
one can use smoothing methods to average out the
irregular components of the time series.
Three common smoothing methods are:
 – Moving average
 – Weighted moving average
 – Exponential smoothing




                       Dr. C. Lightner                     14
                 Fayetteville State University
       Smoothing Methods: Moving Average

Moving Average Method
     The moving average method consists of computing
an average of the most recent n data values for the
series and using this average for forecasting the value
of the time series for the next period.




                        Dr. C. Lightner               15
                  Fayetteville State University
   Robert Drug’s Example: Moving Average

Our scatter plot for Robert’s Drug Sales has no
significant trend, seasonal, or cyclical effects. Thus we
should employ a smoothing technique for forecasting
sales.

Forecast the sales for period 11 using a three period
moving average (MA3).




                      Dr. C. Lightner                   16
                Fayetteville State University
                   Example: Robert’s Drugs: Moving Average

             Steps to Moving Average Using Excel
              Step 1: Select the Tools pull-down menu.
              Step 2: Select the Data Analysis option.
              Step 3: When the Data Analysis Tools dialog
                        appears, choose Moving Average.
              Step 4: When the Moving Average dialog box
                        appears:
                        Enter B4:B13 in the Input Range box.
      This specifies
      the value of n    Enter 3 in the Interval box.
This is the column      Enter C5 in the Output Range box.
following our data,
and one row below where Select OK.
our data begins.
                               Dr. C. Lightner                 17
                         Fayetteville State University
                 Robert’s Drugs: Moving Average

     MA3 (Three period Moving average) for Robert’s Drug Example
                                                  Ft is the forecast for week t.
Robert's Drug
                                    n=3
     Week (t )          Yt          Ft
        1              110
        2              115             #N/A
        3              125             #N/A              F4 (forecast for week 4)=116.7
        4              120            116.6667
        5              125                 120
        6              120            123.3333
        7              130            121.6667
        8              115                 125
        9              110            121.6667
        10             130            118.3333
        11                            118.3333         F11 (forecast for week 11)=118.3
                                              Thus we would forecast the sales
                                              for Week 11 to be 118.3
                             Dr. C. Lightner                                     18
                       Fayetteville State University
Smoothing Methods: Weighted Moving Average

Weighted Moving Average Method
The weighted moving average method consists of computing a
weighted average of the most recent n data values for the series
and using this weighted average for forecasting the value of the
time series for the next period. The more recent observations are
typically given more weight than older observations. For
convenience, the weights usually sum to 1.
The regular moving average gives equal weight to past data values
when computing a forecast for the next period. The weighted
moving average allows different weights to be allocated to past
data values.
There is no Excel command for computing this so you must do this
manually. You can either manually enter the formulas into excel
and apply to all periods or compute value by hand.

                         Dr. C. Lightner                     19
                   Fayetteville State University
   Smoothing Methods: Weighted Moving Average

   Use a 3 period weighted moving average to forecast the sales for
   week 11 giving a weight of 0.6 to the most recent period, 0.3 to the
   second most recent period, and 0.1 to the third most recent period.

F11 = (0.6)*130 + (0.3)*110 + (0.1)* 115= 122.5

  Sales for the    Sales for 2nd                  Sales for 3rd
  most recent      most recent                    most recent
  period           period                         period



Thus we would forecast the sales for week 11 to be 122.5.


                             Dr. C. Lightner                        20
                       Fayetteville State University
   Smoothing Methods: Exponential Smoothing
Exponential Smoothing
– Using exponential smoothing, the forecast for the next period
  is equal to the forecast for the current period plus a
  proportion () of the forecast error in the current period.
– Using exponential smoothing, the forecast is calculated by:
           Ft+1= Yt + (1- )Ft            This is the same as
                                           Ft+1 = Ft + α (Yt – Ft)
   where:
     is the smoothing constant (a number between 0 and 1)
   Ft is the forecast for period t
   Ft +1 is the forecast for period t+1
   Yt is the actual data value for period t

                            Dr. C. Lightner                          21
                      Fayetteville State University
    Robert’s Drugs: Exponential Smoothing

Forecast the sales for period 11 using Exponential
Smoothing α= 0.1.




                      Dr. C. Lightner                22
                Fayetteville State University
             Robert’s Drugs: Exponential Smoothing

     Steps to Exponential Smoothing Using Excel
        Step 1: Select the Tools pull-down menu.
        Step 2: Select the Data Analysis option.
        Step 3: When the Data Analysis Tools dialog
            appears, choose Exponential Smoothing.
        Step 4: When the Exponential Smoothing dialog box
            appears:
                   Enter B4:B13 in the Input Range box.
Damping factor     Enter 0.9 (for  = 0.1) in Damping Factor box.
is always 1-α      Enter C4 in the Output Range box.
                   Select OK.



                                 Dr. C. Lightner                    23
                           Fayetteville State University
          Robert’s Drugs: Exponential Smoothing
   Robert's Drugs
                                     α=0.1
     Week (t )         Salest        Ft
         1              110            #N/A
         2              115                110
         3              125              110.5
         4              120             111.95
         5              125            112.755
         6              120           113.9795
         7              130           114.5816
         8              115           116.1234
         9              110           116.0111
        10              130           115.4099
        11

                         F11 = 0.1 * Y10 + .9 F10
Thus we would                = .1 *130 + .9 * 115.4099
forecast sales for          = 116.87
week 11 to be 116.87
                               Dr. C. Lightner           24
                         Fayetteville State University
          Questions That You Should Be Asking

For the Moving Average technique, how do I determine the best
value of n to use for forecasting?
For Exponential Smoothing, how do I determine the best value of α
to use?
If I realize that a smoothing technique should be employed, how do
you know which smoothing technique is best?

In order to answer the above questions, we need criteria for
judging the accuracy of a forecasting technique. Once we select a
criterion, the method (or parameter) which provides the best value
for our criterion is the best method (or parameter) to use for
forecasting our scenario.



                         Dr. C. Lightner                       25
                   Fayetteville State University
          Measures of Forecast Accuracy

Mean Squared Error (MSE)
     The average of the squared forecast errors for the historical
data is calculated. The forecasting method or parameter(s) which
minimize this mean squared error is then selected.

Mean Absolute Deviation (MAD)
     The mean of the absolute values of all forecast errors is
calculated, and the forecasting method or parameter(s) which
minimize this measure is selected. The mean absolute deviation
measure is less sensitive to individual large forecast errors than the
mean squared error measure.

You may choose either of the above criteria for evaluating the
accuracy of a method (or parameter).
                          Dr. C. Lightner                        26
                    Fayetteville State University
Selecting the best Smoothing Technique for Robert’s Drugs

  Determine the smoothing technique that is best for forecasting
  Robert’s Drug sales: A two period moving average, a three period
  moving average, exponential smoothing (α=0.1), or exponential
  smoothing (α=0.2)



  Realistically we should have experimented with more values of n
  for the moving average, and α for exponential smoothing to
  determine the absolute best parameters to use for our technique.

  On the next slide we randomly chose to use the MSE criterion to
  judge the best technique.



                           Dr. C. Lightner                      27
                     Fayetteville State University
    Robert’s Drugs :Comparing Smoothing Techniques
   Double click on the Excel sheet below to enter actual Excel spreadsheet
   that I created. Clicking on individual cells will provide the formulas that were
   entered to compute the observed values.
Robert's Drug
                   Sales              n=2            Error
                                                                        2    MSE for MA2
    Week (t )              Yt         Ft             (Yt - Ft) (Yt - Ft)
       1                 110
       2                 115               #N/A
       3                 125                 112.5          12.5    156.25
       4                 120                   120             0         0
       5                 125                 122.5           2.5      6.25
       6                 120                 122.5          -2.5      6.25
       7                 130                 122.5           7.5     56.25
       8                 115                   125           -10       100
       9                 110                 122.5         -12.5    156.25
       10                130                 112.5          17.5    306.25
       11                                      120
                                                     MSE           98.4375

                                      Dr. C. Lightner                                      28
                                Fayetteville State University
   Robert’s Drugs :Comparing Smoothing Techniques
Robert's Drug
                Sales              n=3          Error
                                                                  2
    Week (t )           Yt         Ft           (Yt - Ft) (Yt - Ft)
       1            110
       2            115              #N/A
       3            125              #N/A                             MSE for MA3
       4            120             116.6667 3.333333 11.11111
       5            125                  120        5       25
       6            120             123.3333 -3.33333 11.11111
       7            130             121.6667 8.333333 69.44444
       8            115                  125      -10      100
       9            110             121.6667 -11.6667 136.1111
       10           130             118.3333 11.66667 136.1111
       11                           118.3333
                                             MSE      69.84127




                               Dr. C. Lightner                                29
                         Fayetteville State University
Robert’s Drugs :Comparing Smoothing Techniques

            Sales              α=0.1       Error
Week (t )           Yt         Ft          (Yt - Ft) (Yt - Ft)2
   1            110             #N/A
   2            115                 110        5       25
                                                                  MSE for Exponential
   3            125               110.5     14.5   210.25
                                                                  Smoothing α=0.1
   4            120              111.95     8.05 64.8025
   5            125             112.755   12.245   149.94
   6            120            113.9795   6.0205 36.24642
   7            130            114.5816 15.41845 237.7286
   8            115            116.1234  -1.1234 1.262016
   9            110            116.0111 -6.01106 36.13279
   10           130            115.4099 14.59005 212.8696
   11
                                           MSE         108.248




                           Dr. C. Lightner                                      30
                     Fayetteville State University
   Robert’s Drugs :Comparing Smoothing Techniques
            Sales        α=0.2      Error
Week (t )           Yt   Ft         (Yt - Ft) (Yt - Ft)2
   1            110        #N/A
   2            115            110        5           25
   3            125            111       14          196
   4            120          113.8      6.2        38.44   MSE for Exponential
   5            125         115.04     9.96      99.2016   Smoothing α=0.2
   6            120        117.032    2.968     8.809024
   7            130       117.6256 12.3744      153.1258
   8            115       120.1005 -5.10048      26.0149
   9            110       119.0804 -9.08038     82.45337
   10           130       117.2643 12.73569     162.1979
   11
                                    MSE         87.91584




                               Dr. C. Lightner                                   31
                         Fayetteville State University
Robert’s Drugs :Comparing Smoothing Techniques

 Since the three period moving average technique
 (MA3) provides to lowest MSE value, this is the best
 smoothing technique to use for forecasting Robert’s
 Drug Sales.




                       Dr. C. Lightner                  32
                 Fayetteville State University
                     Trend Projection

If a time series exhibits a linear trend, the method of least
squares may be used to determine a trend line (projection) for
future forecasts.
Least squares, also used in regression analysis, determines the
unique trend line forecast which minimizes the mean square
error between the trend line forecasts and the actual observed
values for the time series.
The independent variable is the time period and the dependent
variable is the actual observed value in the time series.




                          Dr. C. Lightner                         33
                    Fayetteville State University
                           Trend Projection
      Using the method of least squares, the formula for the trend
      projection is:
      Yt = b0 + b1t.

    where:         Yt = trend forecast for time period t
                   b1 = slope of the trend line
                    b0 = trend line projection for time 0

               b1 = n tYt - t Yt               b0  Y  b1 t
                      nt 2 - (t )2
       where: Yt = observed value of the time series at time period t
t
                Y = average of the observed values for Yt
                   = average time period for the n observations
              tt
                                Dr. C. Lightner                         34
                          Fayetteville State University
           Example: Auger’s Plumbing Service
The number of plumbing repair jobs performed by Auger's Plumbing
Service in each of the last nine months are listed below.
Month Jobs         Month Jobs               Month       Jobs
March 353          June    374              September    399
April 387          July   396               October      412
May   342          August 409               November     408

Forecast the number of repair jobs Auger's will perform in
December using the least squares method.




                             Dr. C. Lightner                   35
                       Fayetteville State University
    Auger’s Plumbing Service: Trend Projection
Trend Projection
          (month) t           Yt         tYt          t2
         (Mar.)   1        353        353          1
         (Apr.)   2        387         774         4
         (May)    3        342       1026          9
         (June)   4        374       1496         16
         (July)   5        396       1980        25
         (Aug.)   6        409       2454        36
         (Sep.)   7        399       2793        49
         (Oct.)   8        412       3296        64
         (Nov.)   9        408       3672        81
           Sum    45        3480      17844       285
                            Dr. C. Lightner                36
                      Fayetteville State University
         Example: Auger’s Plumbing Service
  Trend Projection (continued)

         t t = 45/9 = 5             Y = 3480/9 = 386.667
           ntYt -  t  Yt (9)(17844) - (45)(3480)
      b1 =                   =                    = 7.4
            n  t 2 - ( t)2     (9)(285) - (45)2

   b0  Y  b1 t   = 386.667 - 7.4(5) = 349.667

     Thus our trend line is Yt = 349.667 + 7.4 t.

Y10 = 349.667 + (7.4)(10) = 423.667

   For December t=10         Dr. C. Lightner               37
                       Fayetteville State University
Auger’s Plumbing Service: Trend Line in Excel

  Excel Spreadsheet Showing Input Data
          A           B           C
  1    Auger's Plumbing Service
  2
  3     Month       Calls
  4       1          353
  5       2          387
  6       3          342
  7       4          374
  8       5          396
  9       6          409
 10       7          399
 11       8          412
 12       9          408
 13
                     Dr. C. Lightner            38
               Fayetteville State University
       Example: Auger’s Plumbing Service

Steps to Trend Projection Using Excel
 Step 1: Select an empty cell (B13) in the worksheet.
 Step 2: Select the Insert pull-down menu.
 Step 3: Choose the Function option.
 Step 4: When the Select Category dialog box appears:
            Choose Statistical in Function Category box.
            Choose Forecast in the Function Name box.
            Select OK.
 Step 5: When the Forecast dialog box appears:
         Enter 10 in the x box (for month 10).
         Enter B4:B12 in the Known y’s box.
         Enter A4:A12 in the Known x’s box.
         Select OK.
                        Dr. C. Lightner                    39
                  Fayetteville State University
     Example: Auger’s Plumbing Service

Spreadsheet Showing Trend Projection for Month 10
   Auger's Plumbing Service

            Month                    Calls
              1                       353
              2                       387
              3                       342
              4                       374
              5                       396
              6                       409
              7                       399
              8                       412
              9                       408
             10                     423.667     Projected

                      Dr. C. Lightner                       40
                Fayetteville State University
                  Roberts Drug Example
Suppose we neglected to plot Robert’s Drug example, and therefore we
do not know that a trend does not exist. Use trend analysis to forecast
the sales for month 11.
 Week (t )            Yt
       1             110
       2             115
       3             125
       4             120
       5             125
       6             120
       7             130
       8             115
       9             110
       10            130
       11            124            Forecast


                           Dr. C. Lightner                            41
                     Fayetteville State University
                   Question????

How could we use the MSE or MAD to verify that the
MA3 is a better smoothing technique than trend analysis
for Robert’s Drug Sales data?




                      Dr. C. Lightner               42
                Fayetteville State University
      Causal Method: Regression Analysis

Regression Analysis is similar to trend analysis, except
the independent variable is not restricted to time. Refer
to Robert’s Drug example. Instead of letting time
represent our independent variable, we could forecast
sales based upon the price of the product. Since
products often go on sale, we could collect data over
several months collecting the weekly price and number
of items sold for the week. For this model, we would
find the regression equation in the same manner in
which we found the trend line except we would call the
independent variable x, instead of t.


                      Dr. C. Lightner                 43
                Fayetteville State University
                        Regression Equation
      Using the method of least squares, the formula for the regression
      line is:
      Y = b0 + b1x.

    where:   Y= dependent variable which depends on the value of x
             b1 = slope of the regression line
             b0 = regression line projection for x= 0

                b1 = n XiYi - Xi Yi                    b0  y  b1 x
                      nXi2 - (Xi)2
       where: Yt = observed value of the time series at time period t
t
                 Y = average of the observed values for Yt
                    = average time period for the n observations
               tt
                                Dr. C. Lightner                           44
                          Fayetteville State University
          Regression Analysis in Excel

The dependent variable Y can predicted using the
same forecast function in Excel as used to forecast a
trend line. Follow the same steps provided on slide 39.




                      Dr. C. Lightner               45
                Fayetteville State University
                     THE END




       See your textbook for more
 examples and detailed explanations
of all topics discussed in these notes.



                    Dr. C. Lightner           46
              Fayetteville State University

				
DOCUMENT INFO
Description: Sales Forecasting document sample