Forecasting

Document Sample
Forecasting Powered By Docstoc
					                                                     TOC


                               EXCEL TEMPLATES FOR USE WITH

                      FUNDAMENTALS OF OPERATIONS MANAGEMENT 2nd CDN EDITION
                                         BY
                             DAVIS, HEINEKE, BALAKRISHNAN

                                         DEVELOPED BY

                                        ORE A. SOLUADE

                                 Chapter 9: FORECASTING

These templates are meant to show the relationship between
Operations Management and spreadsheet analysis.
To go to a specific Worksheet, click on the appropriate tab.

                                   LIST OF WORKSHEETS
TOC                   Table of Contents
3-per.MA              Table of 3-period moving average calculations
3-per.MA_Chart        Graph of 3-period moving average calculations
3-per.MA_TS(t)        Tracking Signal for 3-period moving average calculations
3-per.WMA             Table of 3-period Weighted Moving Average calculations
3-per.WMA_Chart       Graph of 3-period Weighted Moving Average calculations
3-per.WMA_TS(t)       Tracking Signal for 3-period Weighted Moving Average calculations
4-per.WMA             Table of 4-period Weighted Moving Average calculations
4-per.AMA_Chart       Graph of 4-period Weighted Moving Average calculations
4-per.WMA_TS(t)       Tracking Signal for 4-period Weighted Moving Average calculations
5-per.WMA             Table of 5-period Weighted Moving Average calculations
5-per.AMA_Chart       Graph of 5-period Weighted Moving Average calculations
5-per.WMA_TS(t)       Tracking Signal for 5-period Weighted Moving Average calculations
Exp.Smthng            Table of Exponential Smoothing calculations
Exp_Chart             Graph of exponential Smoothing calculations
Exp_TS(t)             Tracking Signal for Exponential Smoothing calculations
Regression            Table of Regression calculations
Reg_Chart             Graph of Linear Regression calculations




                                                    Page 1
                                                   3-per.MA



Forecast using Moving Averages

OBJECTIVE:
This template is used to make forecasts based on a 3-period Moving Average technique.
The 3-period moving average calculation is for a time series of 30 periods.
By entering data in column B, error measurements, Tracking Signal, as well as the
charts, are automatically generated.

INPUT:
                                              (At - Ft)2   |At - Ft|
                  At         Ft     At - Ft     Error Absolute
   Period     Actual Forecast        Error    Squared Deviation        RSFE     MAD(t)   TS(t)
        1       800
        2      1400
        3      1000
        4      1500      1067          433      187778         433      433        433      1
        5      1500      1300          200       40000         200      633        317      2
        6      1300      1333          -33        1111          33      600        222      3
        7      1800      1433          367      134444         367      967        258      4
        8      1700      1533          167       27778         167     1133        240      5
        9      1300      1600         -300       90000         300      833        250      3
       10      1700      1600          100       10000         100      933        229      4
       11      1700      1567          133       17778         133     1067        217      5
       12      1500      1567          -67        4444          67     1000        200      5
       13      2300      1633          667      444444         667     1667        247      7
       14      2300      1833          467      217778         467     2133        267      8
       15      2000      2033          -33        1111          33     2100        247      8
       16      1700      2200         -500      250000         500     1600        267      6
       17      1800      2000         -200       40000         200     1400        262      5
       18      2200      1833          367      134444         367     1767        269      7
       19      1900      1900            0           0           0     1767        252      7
       20      2400      1967          433      187778         433     2200        263      8
       21      2400      2167          233       54444         233     2433        261      9
       22      2600      2233          367      134444         367     2800        267     11
       23      2000      2467         -467      217778         467     2333        277      8
       24      2500      2333          167       27778         167     2500        271      9
       25      2600      2367          233       54444         233     2733        270     10
       26      2200      2367         -167       27778         167     2567        265     10
       27      2200      2433         -233       54444         233     2333        264      9
       28      2500      2333          167       27778         167     2500        260     10
       29      2400      2300          100       10000         100     2600        254     10
       30      2100      2367         -267       71111         267     2333        254      9
                                  2333.333     2468889        6867



OUTPUT:
a       Bias                                  86.41975
b       Mean Square Error                     91440.33
c       Standard Error                         302.391


                                                    Page 2
                                   3-per.MA


d   Mean Absolute Deviation      254.321
e   Tracking signal           See Column I
f   Graph                     Click on 3 -per.MA_Chart Tab




                                    Page 3
                                                                 3-per.MA_Chart



                                                          3-period Moving Average


         3000




         2500




         2000
Demand




         1500




         1000




          500




            0
                1   2   3   4   5   6   7   8   9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
                                                                  Period

                                                              Actual       Forecast




                                                                       Page 4
                                                                          3-per.MA_TS(t)



                                                                3-period Moving Average


                  12




                  10




                   8
Tracking Signal




                   6




                   4




                   2




                   0
                       1   2   3   4   5   6   7   8   9   10   11   12    13     14     15   16   17   18   19   20   21   22   23   24   25   26   27   28
                                                                                  Period




                                                                                Page 5
                                                  3-per.WMA



Forecasting using Weighted Moving Averages

OBJECTIVE:
This template is used to make forecasts based on a 3-period Weighted Moving Average technique.
The 3-period Weighted Moving Average calculation is for a time series of 30 periods.
The weights: a1, a2, and a3 are entered as required.
By entering data in column B, error measurements, Tracking Signal, as well as the
charts, are automatically generated.

INPUT:
     a1 =       0.40
      a2 =      0.35
      a3 =      0.25
                                              (At - Ft)2   |At - Ft|
                  At         Ft     At - Ft     Error Absolute
   Period     Actual Forecast        Error    Squared Deviation        RSFE    MAD(t)       TS(t)
        1       800
        2      1400
        3      1000
        4      1500      1060          440      193600         440      440       440             1
        5      1500      1285          215       46225         215      655       328             2
        6      1300      1300            0           0           0      655       218             3
        7      1800      1450          350      122500         350     1005       251             4
        8      1700      1505          195       38025         195     1200       240             5
        9      1300      1575         -275       75625         275      925       246             4
       10      1700      1640           60        3600          60      985       219             4
       11      1700      1560          140       19600         140     1125       209             5
       12      1500      1540          -40        1600          40     1085       191             6
       13      2300      1650          650      422500         650     1735       237             7
       14      2300      1780          520      270400         520     2255       262             9
       15      2000      1980           20         400          20     2275       242             9
       16      1700      2225         -525      275625         525     1750       264             7
       17      1800      2045         -245       60025         245     1505       263             6
       18      2200      1845          355      126025         355     1860       269             7
       19      1900      1860           40        1600          40     1900       254             7
       20      2400      1965          435      189225         435     2335       265             9
       21      2400      2145          255       65025         255     2590       264            10
       22      2600      2200          400      160000         400     2990       272            11
       23      2000      2450         -450      202500         450     2540       281             9
       24      2500      2370          130       16900         130     2670       273            10
       25      2600      2365          235       55225         235     2905       272            11
       26      2200      2325         -125       15625         125     2780       265            10
       27      2200      2460         -260       67600         260     2520       265            10
       28      2500      2360          140       19600         140     2660       260            10
       29      2400      2275          125       15625         125     2785       255            11
       30      2100      2355         -255       65025         255     2530       255            10
                                      2530     2529700        6880

OUTPUT:


                                                    Page 6
                                  3-per.WMA


a   Bias                         93.7037
b   Mean Square Error          93692.59
c   Standard Error             306.0925
d   Mean Absolute Deviation    254.8148
e   Tracking signal           See Column I
f   Graph                     Click on 3-per.WMA_Chart Tab




                                    Page 7
                                                                3-per.WMA_Chart



                                                      3-period Weighted Moving Average


         3000




         2500




         2000
Demand




         1500




         1000




          500




            0
                1   2   3   4   5   6   7   8   9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
                                                                  Period

                                                              Actual       Forecast




                                                                       Page 8
                                                                       3-per.WMA_TS(t)



                                                           3-period Weighted Moving Average


                  12




                  10




                   8
Tracking Signal




                   6




                   4




                   2




                   0
                       1   2   3   4   5   6   7   8   9     10   11   12   13    14      15   16   17   18   19   20   21   22   23   24   25   26   27
                                                                                 Period




                                                                            Page 9
                                                  4-per.WMA



Forecasting using Weighted Moving Averages

OBJECTIVE:
This template is used to make forecasts based on a 4-period Weighted Moving Average technique.
The 4-period Weighted Moving Average calculation is for a time series of 30 periods.
The weights: b1, b2, b3 and b4 are entered as required.
By entering data in column B, error measurements, Tracking Signal, as well as the
charts, are automatically generated.

INPUT:
     b1 =       0.40
      b2 =      0.30
      b3 =      0.20
      b4 =      0.10
                                              (At - Ft)2   |At - Ft|
                  At         Ft     At - Ft     Error Absolute
   Period     Actual Forecast        Error    Squared Deviation        RSFE    MAD(t)       TS(t)
        1       800
        2      1400
        3      1000
        4      1500
        5      1500      1090          410      168100         410      410       410             1
        6      1300      1310          -10         100          10      400       210             2
        7      1800      1280          520      270400         520      920       313             3
        8      1700      1490          210       44100         210     1130       288             4
        9      1300      1520         -220       48400         220      910       274             3
       10      1700      1530          170       28900         170     1080       257             4
       11      1700      1660           40        1600          40     1120       226             5
       12      1500      1580          -80        6400          80     1040       208             5
       13      2300      1520          780      608400         780     1820       271             7
       14      2300      1720          580      336400         580     2400       302             8
       15      2000      1820          180       32400         180     2580       291             9
       16      1700      1950         -250       62500         250     2330       288             8
       17      1800      2180         -380      144400         380     1950       295             7
       18      2200      2040          160       25600         160     2110       285             7
       19      1900      1890           10         100          10     2120       267             8
       20      2400      1850          550      302500         550     2670       284             9
       21      2400      2000          400      160000         400     3070       291            11
       22      2600      2170          430      184900         430     3500       299            12
       23      2000      2220         -220       48400         220     3280       295            11
       24      2500      2400          100       10000         100     3380       285            12
       25      2600      2390          210       44100         210     3590       281            13
       26      2200      2400         -200       40000         200     3390       278            12
       27      2200      2290          -90        8100          90     3300       270            12
       28      2500      2440           60        3600          60     3360       261            13
       29      2400      2390           10         100          10     3370       251            13
       30      2100      2280         -180       32400         180     3190       248            13
                                      3190     2611900        6450



                                                   Page 10
                                        4-per.WMA


OUTPUT:
a         Bias                       122.6923
b         Mean Square Error          100457.7
c         Standard Error             316.9506
d         Mean Absolute Deviation    248.0769
e         Tracking signal           See Column I
f         Graph                     Click on 4-per.WMA_Chart Tab




                                         Page 11
                                                                4-per.WMA_Chart



                                                        4-per.Weighted Moving Average


        3000




        2500




        2000



                                                                                                                    Actual
Sales




        1500
                                                                                                                    Forecast




        1000




         500




           0
               1   2   3   4   5   6   7   8   9   10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
                                                                  Period




                                                                    Page 12
                                                                     4-per.WMA_TS(t)



                                                       4-per. Weighted Moving Average


                  16



                  14



                  12



                  10
Tracking Signal




                   8



                   6



                   4



                   2



                   0
                       1   2   3   4   5   6   7   8   9   10   11    12     13      14   15   16   17   18   19   20   21   22   23   24   25   26
                                                                              Period




                                                                           Page 13
                                                  5-per.WMA



Forecasting using Weighted Moving Averages

OBJECTIVE:
This template is used to make forecasts based on a 5-period Weighted Moving Average technique.
The 5-period Weighted Moving Average calculation is for a time series of 30 periods.
The weights: c1, c2, c3 c4 and c5 are entered as required.
By entering data in column B, error measurements, Tracking Signal, as well as the
charts, are automatically generated.

INPUT:
     c1 =       0.30
      c2 =      0.25
      c3 =      0.20
      c4 =      0.15
      c5 =      0.10
                                              (At - Ft)2   |At - Ft|
                  At         Ft     At - Ft     Error Absolute
   Period     Actual Forecast        Error    Squared Deviation        RSFE    MAD(t)       TS(t)
        1       800
        2      1400
        3      1000
        4      1500
        5      1500
        6      1300      1165          135       18225          135     135       135             1
        7      1800      1325          475      225625          475     610       305             2
        8      1700      1350          350      122500          350     960       320             3
        9      1300      1525         -225       50625          225     735       296             2
       10      1700      1520          180       32400          180     915       273             3
       11      1700      1545          155       24025          155    1070       253             4
       12      1500      1650         -150       22500          150     920       239             4
       13      2300      1580          720      518400          720    1640       299             5
       14      2300      1610          690      476100          690    2330       342             7
       15      2000      1810          190       36100          190    2520       327             8
       16      1700      1890         -190       36100          190    2330       315             7
       17      1800      1955         -155       24025          155    2175       301             7
       18      2200      2100          100       10000          100    2275       286             8
       19      1900      2020         -120       14400          120    2155       274             8
       20      2400      1905          495      245025          495    2650       289             9
       21      2400      1925          475      225625          475    3125       300            10
       22      2600      2070          530      280900          530    3655       314            12
       23      2000      2235         -235       55225          235    3420       309            11
       24      2500      2240          260       67600          260    3680       307            12
       25      2600      2390          210       44100          210    3890       302            13
       26      2200      2405         -205       42025          205    3685       297            12
       27      2200      2390         -190       36100          190    3495       293            12
       28      2500      2295          205       42025          205    3700       289            13
       29      2400      2420          -20         400           20    3680       278            13
       30      2100      2385         -285       81225          285    3395       278            12



                                                   Page 14
                                               5-per.WMA


                                    3395    2731275        6945

OUTPUT:
a         Bias                                  135.8
b         Mean Square Error                   109251
c         Standard Error                    330.5314
d         Mean Absolute Deviation               277.8
e         Tracking signal                  See Column I
f         Graph                            Click on 5-per.WMA_Chart Tab




                                                Page 15
                                                                5-per.WMA_Chart



                                                       5-per. Weighted Moving Average


        3000




        2500




        2000



                                                                                                                    Actual
Sales




        1500
                                                                                                                    Forecast




        1000




         500




           0
               1   2   3   4   5   6   7   8   9   10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
                                                                  Period




                                                                    Page 16
                                                                5-per.WMA_TS(t)



                                                       5-per. Weighted Moving Average


                  14




                  12




                  10
Tracking Signal




                   8




                   6




                   4




                   2




                   0
                       1   2   3   4   5   6   7   8   9   10   11   12    13      14   15   16   17   18   19   20   21   22   23   24   25
                                                                          Period




                                                                     Page 17
                                                   Exp.Smthng



Forecast using Exponential smoothing

OBJECTIVE:
This template is used to make forecasts based on the Exponential smoothing technique
for a time series of 30 periods. The value of the smoothing constant in entered in cell B15.
By entering data in column B, error measurements, Tracking Signal, as well as the
charts, are automatically generated.

INPUT:
alpha =           0.3
                                                (At - Ft)2     |At - Ft|
                    At         Ft     At - Ft      Error     Absolute
   Period      Actual Forecast          Error   Squared      Deviation       RSFE      MAD(t)   TS(t)
        1        800       800            N/A       N/A            N/A         N/A       N/A     N/A
        2       1400       800            600    360000            600         600       600       1
        3       1000       980             20       400             20         620       310       2
        4       1500       986            514    264196            514        1134       378       3
        5       1500      1140            360    129456            360        1494       373       4
        6       1300      1248             52      2689             52        1546       309       5
        7       1800      1264            536    287620            536        2082       347       6
        8       1700      1425            275     75851            275        2357       337       7
        9       1300      1507           -207     42937            207        2150       321       7
       10       1700      1445            255     65000            255        2405       313       8
       11       1700      1522            178     31850            178        2584       300       9
       12       1500      1575            -75      5636             75        2509       279       9
       13       2300      1553            747    558679            747        3256       318      10
       14       2300      1777            523    273753            523        3779       334      11
       15       2000      1934             66      4389             66        3845       315      12
       16       1700      1954           -254     64326            254        3592       311      12
       17       1800      1878            -78      6012             78        3514       296      12
       18       2200      1854            346    119525            346        3860       299      13
       19       1900      1958            -58      3363             58        3802       286      13
       20       2400      1941            459    211053            459        4261       295      14
       21       2400      2078            322    103416            322        4583       296      15
       22       2600      2175            425    180717            425        5008       302      17
       23       2000      2302           -302     91460            302        4706       302      16
       24       2500      2212            288     83119            288        4994       302      17
       25       2600      2298            302     91091            302        5296       302      18
       26       2200      2389           -189     35620            189        5107       297      17
       27       2200      2332           -132     17454            132        4975       291      17
       28       2500      2292            208     43065            208        5182       288      18
       29       2400      2355             45      2049             45        5228       279      19
       30       2100      2368           -268     71993            268        4959       279      18
                                        4959    3226718          8085

OUTPUT:
a           Bias                                171.0138
b           Mean Square Error                   111266.1
c           Standard Error                      333.5658
d           Mean Absolute Deviation             278.8086


                                                     Page 18
                         Exp.Smthng


e   Tracking signal   See Column I
f   Graph             Click on Exp_Chart Tab




                           Page 19
                                                                       Exp_Chart



                                                           Exponential Smoothing


         3000




         2500




         2000
Demand




         1500




         1000




          500




            0
                1   2   3   4   5   6   7   8   9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
                                                                  Period

                                                              Actual        Forecast




                                                                       Page 20
                                                                               Exp_TS(t)



                                                                 Exponential Smoothing


                  20


                  18


                  16


                  14


                  12
Tracking Signal




                  10


                   8


                   6


                   4


                   2


                   0
                       1   2   3   4   5   6   7   8   9   10   11   12   13    14    15   16   17   18   19   20   21   22   23   24   25   26   27   28   29
                                                                                     Period




                                                                               Page 21
                                                               Regression




Forecasting using Regression Analysis

OBJECTIVE:
This template is used to make forecasts based on Regression Analysis for a
30-period data set.
By entering data in column B, error measurements, Tracking Signal, as well as the
charts, are automatically generated.

INPUT:
                                                                                         (At - Ft)2    |At - Ft|
        Actual                                 Trend             At         Ft At - Ft       Error    Absolute
    t    At    t*At         t2       At2      Tt         t   Actual   Forecast     Error  Squared     Deviation      RSFE
    1   600    600          1    360000   801.3          1     600      801.3    -201.3   40514.60      201.28     -201.28
    2 1550    3100          4   2402500 1160.9           2    1550     1160.9     389.1 151400.52       389.10      187.82
    3 1500    4500          9   2250000 1520.5           3    1500     1520.5     -20.5     420.79        20.51     167.31
    4 1500    6000         16   2250000 1880.1           4    1500     1880.1    -380.1 144497.75       380.13     -212.82
    5 2400 12000           25   5760000 2239.7           5    2400     2239.7     160.3   25681.99      160.26      -52.57
    6 3100 18600           36   9610000 2599.4           6    3100     2599.4     500.6 250641.01       500.64      448.07
    7 2600 18200           49   6760000 2959.0           7    2600     2959.0    -359.0 128862.91       358.97       89.10
    8 2900 23200           64   8410000 3318.6           8    2900     3318.6    -418.6 175217.76       418.59     -329.49
    9 3800 34200           81 14440000 3678.2            9    3800     3678.2     121.8   14833.88      121.79     -207.70
   10 4500 45000          100 20250000 4037.8           10    4500     4037.8     462.2 213609.43       462.18      254.48
   11 4000 44000          121 16000000 4397.4           11    4000     4397.4    -397.4 157955.69       397.44     -142.95
   12 4900 58800          144 24010000 4757.1           12    4900     4757.1     142.9   20434.19      142.95        -0.01
   78 33350 268200        650 112502500 28491.7                                     0.0 1324070.51     3553.85

t bar   =           6.5
A bar   =        2779.2
b(1)    =        359.62
b(0)    =        441.67

              =
Tt = b(0) + b(1)*t        441.67+ 359.62*t

OUTPUT:
a   Bias                                      -0.0004
b   Mean Square Error                          132407
c   Standard Error                            363.878
d   Mean Absolute Deviation                   355.385
e   Tracking signal                          See Column P.
f   Graph                                    Click on Reg_Chart Tab




                                                                Page 22
                 Regression




MAD(t)   TS(t)
201.28   -1.00
295.19    0.64
203.63    0.82
247.76   -0.86
230.26   -0.23
275.32    1.63
287.27    0.31
303.69   -1.08
283.48   -0.73
301.35    0.84
310.08   -0.46
296.15    0.00




                  Page 23
                                           Reg_Chart



                                   Regression Analysis


        6000




        5000




        4000
Sales




        3000




        2000




        1000




           0
               1   2   3   4   5      6            7           8   9   10   11   12
                                          Period

                                      Actual           Trend




                                            Page 24

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:192
posted:9/29/2012
language:Unknown
pages:24