Docstoc

Seasonality

Document Sample
Seasonality Powered By Docstoc
					 Below is a fabricated data set of retail sales. Only growth and seasonality have been applied to the seed
 value of 100
 . The growth rate is declining as seen by Colum C, and the seasonality in Colum F shows heavy sales for the
 Christmas holiday, an increase in April for Spring, and another increase in August for the school year


      Growth Rate                  X             Seasonality                =              (Sales)

 Annual Growth Rates                        Seasonality Factors                     MMM-YY
 Year 1 Growth = 10%                          Jan        1.0                        May-08
 Year 2 Growth = 8%                           Feb        0.9                         Jun-08
 Year 3 Growth = 5%                           Mar        0.9                         Jul-08
 Year 4 Growth = 3%                           Apr        1.1                         Aug-08
                                              May        0.8                         Sep-08
        CAGR =        6.9%                    Jun        0.7                         Oct-08
Annual Growth =        6.9%                    Jul       1.0                         Nov-08
Monthly Growth =       0.6%                   Aug        1.3                         Dec-08
                                              Sep        1.1                         Jan-09
                                              Oct        1.2                         Feb-09
                                              Nov        1.5                         Mar-09
                                              Dec        1.3                         Apr-09
                                                                                    May-09
                                                                                     Jun-09
                                                                                     Jul-09
                                                                                     Aug-09
                                                                                     Sep-09
                                                                                     Oct-09
                                                                                     Nov-09
                                                                                     Dec-09
                                                                                     Jan-10
                                                                                     Feb-10
                                                                                     Mar-10
                                                                                     Apr-10
                                                                                    May-10
                                                                                     Jun-10
                                                                                     Jul-10
                                                                                     Aug-10
                                                                                     Sep-10
                                                                                     Oct-10
                                                                                     Nov-10
                                                                                     Dec-10
                                                                                     Jan-11
                                                                                     Feb-11
                                                                                     Mar-11
                                                                                     Apr-11
 applied to the seed

ows heavy sales for the
 the school year
                                  Base:
       (Sales)                   $100

              Period      Cycle   Sales
                 1          1     $80
                 2          2     $71
                 3          3     $102
                 4          4     $133
                 5          5     $113
                 6          6     $124
                 7          7     $156
                 8          8     $136
                 9          9     $105
                10         10     $95
                11         11     $96
                12         12     $118
                13          1     $86
                14          2     $76
                15          3     $109
                16          4     $142
                17          5     $121
                18          6     $132
                19          7     $166
                20          8     $145
                21          9     $112
                22         10     $101
                23         11     $102
                24         12     $125
                25          1     $91
                26          2     $80
                27          3     $116
                28          4     $151
                29          5     $128
                30          6     $141
                31          7     $177
                32          8     $154
                33          9     $119
                34         10     $108
                35         11     $108
                36         12     $109
How to model cyclicality (seasonality) of data:

 Step 1 -
                      Plot data and visually look for cyclicality. This can be seasonally driven, process driven (so
                      on an off cycle, such as 9 or 15 months), or driven by industry dynamics, thus spanning 2 -
                      15 years. Whatever the cycle, the same techniques outlined here can be applied.


                              $190

                              $170

                              $150

                              $130
                      Sales




                              $110

                              $90

                              $70

                              $50
                                Nov-07   Jun-08     Dec-08      Jul-09     Jan-10      Aug-10     Feb-11      Sep-11
                                                                     Period




            To make more sense of the data visually, the curved lines are 2nd order parabolic fits of the data for each c
            that exists. The larger linear fit is the growth over the entire three cycles (i.e., three years of data). Clearly
            of each cycle's linear fit moving upward, and also subsequently tracking exactly with the larger linear fit of
            to the patter of the cycles themselves shows clearly the need in forecasting to use a 3-5 year CAGR when e

                      $190

                      $170

                      $150

                      $130
              Sales




                      $110

                        $90

                        $70

                        $50
                          Nov-07                    Jun-08                    Dec-08                     Jul-09




 Step 2 -   Subtract sales growth rate from raw sales data to normalize the sales data (note that slope is the periodic g
            the slope by 12 yields the annual growth rate), thus placing each year's cyclicality on equal footing. Do this
the slope by 12 yields the annual growth rate), thus placing each year's cyclicality on equal footing. Do this
pictured below. Once you get the slop, multiply this from the months from the start of the data series to o
raw sales data to then obtain normalized dataset.

                                                                                 Normalized
  Period           Month           Cycle           Sales           Growth          Sales
  May-08             1               1              80.5             0.0              80.5
  Jun-08             2               2              70.8             0.7              70.1
  Jul-08             3               3             101.7             1.3             100.4
  Aug-08             4               4             133.0             2.0             131.0
  Sep-08             5               5             113.2             2.6             110.5
  Oct-08             6               6             124.1             3.3             120.8
  Nov-08             7               7             156.0             4.0             152.1
  Dec-08             8               8             136.0             4.6             131.3
  Jan-09             9               9             105.2             5.3              99.9
  Feb-09            10              10              95.2             5.9              89.2
  Mar-09            11              11              95.7             6.6              89.1
  Apr-09            12              12             117.6             7.3             110.3
  May-09            13               1              86.0             7.9              78.0
  Jun-09            14               2              75.6             8.6              67.0
  Jul-09            15               3             108.6             9.3              99.4
  Aug-09            16               4             141.9             9.9             132.0
  Sep-09            17               5             120.7            10.6             110.2
  Oct-09            18               6             132.4            11.2             121.2
  Nov-09            19               7             166.4            11.9             154.5
  Dec-09            20               8             144.9            12.6             132.4
  Jan-10            21               9             112.1            13.2              98.8
  Feb-10            22              10             101.4            13.9              87.5
  Mar-10            23              11             101.9            14.5              87.3
  Apr-10            24              12             125.2            15.2             110.0
  May-10            25               1              91.5            15.9              75.6
  Jun-10            26               2              80.4            16.5              63.9
  Jul-10            27               3             115.5            17.2              98.3
  Aug-10            28               4             150.9            17.8             133.0
  Sep-10            29               5             128.3            18.5             109.8
  Oct-10            30               6             140.7            19.2             121.5
  Nov-10            31               7             176.7            19.8             156.9
  Dec-10            32               8             153.9            20.5             133.4
  Jan-11            33               9             118.9            21.2              97.8
  Feb-11            34              10             107.6            21.8              85.8
  Mar-11            35              11             108.1            22.5              85.6
  Apr-11            36              12             109.4            23.1              86.2
   Notice how the dark line is now flat since the growth has been removed, and how each cyclical curve is a
   data. Also notice that the individual growth patterns for each cycle still slope upward, identical to befor
   of each individual linear line though passes through the darker 3-yr CAGR growth line, so the individual l
               of each individual linear line though passes through the darker 3-yr CAGR growth line, so the individual l


                     170.0

                     150.0

                     130.0
             Sales
                     110.0

                      90.0

                      70.0

                      50.0
                         Nov-07               Jun-08                    Dec-08                    Jul-09



Step 3 -   Average and characterize the cycle, developing multiplication factors from the start of the cycle,
           or it can take on the form af a curvilinear (polynomial fit) of the data. Clearly from the charts below, a 4th


                                                                             Average        Normalized
Month          Cycle              Cycle 1    Cycle 2         Cycle 3          Cycle           Cycle
 May             1                  80.5       78.0            75.6            78.0           1.000
 Jun             2                  70.1       67.0            63.9            67.0           0.859
 Jul             3                 100.4       99.4            98.3            99.4           1.273
 Aug             4                 131.0      132.0           133.0           132.0           1.692
 Sep             5                 110.5      110.2           109.8           110.2           1.412
 Oct             6                 120.8      121.2           121.5           121.2           1.553
 Nov             7                 152.1      154.5           156.9           154.5           1.979
 Dec             8                 131.3      132.4           133.4           132.4           1.696
 Jan             9                  99.9       98.8            97.8            98.8           1.266
 Feb            10                  89.2       87.5            85.8            87.5           1.121
 Mar            11                  89.1       87.3            85.6            87.3           1.119
 Apr            12                 110.3      110.0            86.2           102.2           1.309
                                                                                                            Average =




           2.100                                                                                              2.100

           1.900                                                                                              1.900

           1.700                                                                                              1.700

           1.500                                                                                              1.500

           1.300                                                                                              1.300
        1.300                                                                                            1.300

        1.100                                                                                            1.100

        0.900                                y = -0.0213x2 + 0.29x + 0.6242                              0.900
                                                       R² = 0.5425
        0.700                                                                                            0.700
                 0      2           4          6           8         10          12         14


        2.100                                                                                            2.100

        1.900                                                                                            1.900

        1.700                                                                                            1.700

        1.500                                                                                            1.500

        1.300                                                                                            1.300

        1.100                                                                                            1.100

        0.900                                                                                            0.900
                                    y = 0.0015x4 - 0.0374x3 + 0.2792x2 - 0.5797x + 1.3051
        0.700                                            R² = 0.7648                                     0.700
                 0      2          4           6          8          10          12         14


         2.100

         1.900

         1.700

         1.500

         1.300

         1.100

         0.900
                     y = 4E-05x6 - 0.0016x5 + 0.024x4 - 0.1918x3 + 0.8015x2 - 1.3732x + 1.7097
         0.700                                      R² = 0.7749
                 0       2           4           6          8          10         12         14


        Fit the data to a 4th order polynomial using the curvilinear linest function, this will increase t

        4th Order Linest Fit of Normalized Cycle Data
         Normalized                                                                            From
                                         2 2        3 3     4 4
Month     Cycle (Y)   Month (X) Month (X ) Month (X ) Month (X )                              Equation
 May        1.000         1            1          1       1                                    0.969
 Jun        0.859         2            4          8      16                                    0.988
Jul      1.273            3           9           27            81      1.192
Aug      1.692            4           16          64           256      1.448
Sep      1.412            5           25          125          625      1.659
Oct      1.553            6           36          216         1296      1.763
Nov      1.979            7           49          343         2401      1.736
Dec      1.696            8           64          512         4096      1.590
Jan      1.266            9           81          729         6561      1.371
Feb      1.121           10          100         1000         10000     1.165
Mar      1.119           11          121         1331         14641     1.091
Apr      1.309           12          144         1728         20736     1.307
                                                                                Average =
      Linest Curve Fit
         0.0015        -0.0374     0.2792       -0.5797       1.3051
         0.0006         0.0168     0.1484       0.5013        0.5156
         0.7648         0.1975      #N/A         #N/A          #N/A
         5.6890         7.0000      #N/A         #N/A          #N/A
         0.8878         0.2731      #N/A         #N/A          #N/A

      Notice that these agree with numbers from 4th order trendline
      polynomial trendline on graph above, but the precision of these
      numbers is as extensive as the computer can handle, plus
      these numbers are ready to use in forecasting equations
      unlike the data on the chart.
ss driven (so                        Visually three cycles can be seen, and the overall data appears to be
spanning 2 -                         growing as well. The lowest point appears to be in June, and the peak
                                     point in November, with noise existing in the data. I have hand drawn in
                                     three curves to show the cycles.

                                            $190

                                            $170

                                            $150

                                            $130
                                    Sales




                                            $110

                                             $90

                                             $70

                                             $50
          -11                                  Nov-07   Jun-08    Dec-08   Jul-09   Jan-10   Aug-10   Feb-11   Sep-11
                                                                               Period




fits of the data for each cycle, and the smaller straight lines are linear fits of each cycle to see the growth
ee years of data). Clearly cycles exist as seen by the parabolas, and growth exists as seen by the mid-position
 th the larger linear fit of the three years of data. The noise encountered with the shorter annual cycles due
      5 year CAGR when estimating historic growth rates.




                           Jan-10                        Aug-10                     Feb-11                     Sep-11
                Period




at slope is the periodic growth rate, and since we are on an annual cycle for this data set, multiplying
n equal footing. Do this by either applying the first order linest function, or the slope equation, both
n equal footing. Do this by either applying the first order linest function, or the slope equation, both
rt of the data series to obtain the growth experienced during this time span, then subtract this from the




                           Linest Linear Fit
                            0.661006 105.2083
                             0.40488 8.590391
                            0.072695 25.23607
                            2.665373         34
                            1697.468 21653.22

                             Slope =     0.661006 Period Growth rate

                Annual Growth =              7.9

                       Growth % =          9.86%




ow each cyclical curve is at the identical elevations with one another. We have clearly normalized the
pward, identical to before. This is due to the pattern of the cyclicality, not annual growth. The center
 h line, so the individual linear fits of each cycle are tracking with the normalized data.
h line, so the individual linear fits of each cycle are tracking with the normalized data.




                            Jan-10                    Aug-10                    Feb-11                     Sep-11
               Period


t of the cycle, through to its end. This can be in the form of multipliers for each period of the cycle,
 the charts below, a 4th order polynomial is required for accurately fitting the cyclicality data.


              Actual
             Factors        % Error
                1.0          0.0%
                0.9          1.8%
                1.3          1.9%
                1.6          4.1%
                1.4          2.7%
                1.5          3.5%
                1.9          5.6%
                1.6          4.4%
                1.3          1.3%
                1.1          0.4%
                1.1          0.5%
                1.4          4.8%
            Average =        2.6%
                Max =        5.6%
                Min =        0.0%
              StDev =        1.9%

              2.100

              1.900

              1.700

              1.500

              1.300
            1.300

            1.100

            0.900                   y = 0.0019x3 - 0.0588x2 + 0.4929x + 0.3616
                                                    R² = 0.5794
            0.700
                    0      2       4          6          8         10         12         14


            2.100

            1.900

            1.700

            1.500

            1.300

            1.100

            0.900
                                y = 7E-05x5 - 0.0007x4 - 0.011x3 + 0.1416x2 - 0.279x + 1.1027
            0.700                                         R² = 0.7676
                    0      2        4          6          8         10         12         14




on, this will increase the accuracy of our calculations.


             Actual
             Factors    % Error
               1.0       3.1%
               0.9      12.9%
    1.3      4.6%
    1.6     10.9%
    1.4     20.7%
    1.5     17.6%
    1.9      7.4%
    1.6      2.2%
    1.3      9.7%
    1.1      3.5%
    1.1      3.0%
    1.4      5.0%
Average =    8.4%
    Max =   20.7%
    Min =    2.2%
  StDev =    6.1%
    Base Sale =      80
Period Growth =   0.661%

                  From      From
        Month     Ratios   Equation
          1       1.000     0.969
          2       0.859     0.988
          3       1.273     1.192
          4       1.692     1.448
          5       1.412     1.659
          6       1.553     1.763
          7       1.979     1.736
          8       1.696     1.590
          9       1.266     1.371
         10       1.121     1.165
         11       1.119     1.091
         12       1.309     1.307

       Model-Based Sales                 Normalized      Error from Actual
                 Cycle      Actual    From       From    From       From
        Month   Period      Sales     Ratios  Equation   Ratios    Equation
          1        1         80.5      81.0       78.5   0.7%        2.5%
          2        2         70.8      70.2       80.5   0.9%       13.7%
          3        3        101.7     104.0       97.5   2.3%        4.1%
          4        4        133.0     138.2      118.7   4.0%       10.8%
          5        5        113.2     116.2      136.2   2.7%       20.3%
          6        6        124.1     128.1      145.1   3.2%       16.9%
          7        7        156.0     163.0      143.4   4.5%        8.1%
          8        8        136.0     140.7      132.2   3.5%        2.8%
          9        9        105.2     106.7      115.1   1.4%        9.5%
         10       10         95.2      95.5       99.0   0.4%        4.1%
         11       11         95.7      95.9       93.6   0.2%        2.1%
         12       12        117.6     111.7      111.5   5.0%        5.2%
         13        1         86.0      87.4       84.9   1.6%        1.3%
         14        2         75.6      76.6       86.9   1.2%       14.9%
         15        3        108.6     110.4      103.9   1.7%        4.3%
         16        4        141.9     144.6      125.1   1.9%       11.9%
         17        5        120.7     122.6      142.5   1.6%       18.1%
         18        6        132.4     134.5      151.5   1.6%       14.4%
         19        7        166.4     169.4      149.8   1.8%        9.9%
         20        8        144.9     147.1      138.5   1.5%        4.4%
         21        9        112.1     113.1      121.5   0.9%        8.4%
         22       10        101.4     101.9      105.4   0.5%        4.0%
         23       11        101.9     102.3      100.0   0.4%        1.8%
         24       12        125.2     118.1      117.9   5.6%        5.8%
25    1    91.5    93.8    91.2       2.5%    0.3%
26    2    80.4    82.9    93.3       3.1%   16.0%
27    3   115.5   116.8   110.3       1.1%    4.5%
28    4   150.9   151.0   131.4       0.1%   12.9%
29    5   128.3   129.0   148.9       0.5%   16.1%
30    6   140.7   140.9   157.8       0.1%   12.2%
31    7   176.7   175.7   156.2       0.5%   11.6%
32    8   153.9   153.5   144.9       0.3%    5.8%
33    9   118.9   119.5   127.9       0.4%    7.5%
34   10   107.6   108.3   111.8       0.7%    3.9%
35   11   108.1   108.7   106.4       0.5%    1.6%
36   12   109.4   124.5   124.3      13.8%   13.6%
                          Average     2.0%    8.5%
                               Max   13.8%   20.3%
                               Min    0.1%    0.3%
                           St. Dev    2.5%    5.6%

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:12
posted:10/25/2011
language:English
pages:14
xiaohuicaicai xiaohuicaicai
About