Docstoc

Forecasting in Excel

Document Sample
Forecasting in Excel Powered By Docstoc
					Using Excel 97 to model a simple time series.

Aim
The aim of this workshop is to show you how to forecast an economic time
series using simple additive and multiplicative seasonal models.

Data
The data for this exercise is the number of outgoing air passengers (quarterly,
millions) from the UK for the period 1993-97. The approximate numbers are
on your disk as airsea.xls under the sheet labelled example.

Looking at the Data
Before analysing any data have a look at it to get some idea of its
characteristics, to spot any patterns or unusual features.

 Use the Chart Wizard to construct a line plot with markers of the observed
  data. By default Excel chooses the vertical axis to start at 0. Double click
  on this axis and alter the scale to start at 6000 so that the detail of the plot
  is clearer.

Look at your plot. What is the underlying trend? _______________________

What is the seasonal pattern? __________________________________________

How do you explain that? ________________________________________

Finding the trend

   Click on one of the plotted points to select the data series.
   Select Chart - Add Trendline - OK.
   From the dialogue box select the linear trendline and under Options ask
    to display the Equation and R-Squared Value to obtain:

                                             Outward UK air passengers 1993-1997

                                     20000

                                     18000   y = 219.01x + 10266
               passengers (1000's)




                                                      2
                                     16000
                                                     R = 0.2039

                                     14000

                                     12000

                                     10000

                                      8000

                                      6000
                                             1   2    3   4   5   6   7   8   9 10 11 12 13 14 15 16 17 18 19 20

                                                                          time in quarters


       With the Trendline highlighted you can use Format Selected
        Trendline to experiment with different styles of line.




                                                                                                                   1
The equation of the trend line.

       The x in the equation refers to the time period, with the first entry
        counting as 1. The time periods correspond to our 2nd column.

       For Quarter1 of 1994, which is period 5, the equation for the value of
        the trendline is
        219.01x + 10266 = 219.01*5 + 10266 = 1095.05 + 10266= 11361.05

       The value of R2 indicates how good a fit the line is to the data.
        Obviously it is not going to fit very well as there is too much
        seasonality, but it indicates which of all the possible trendlines is a
        good choice. When deciding which trendline is best, we must maintain
        a balance between getting a good fit and keeping the model
        reasonably simple.

       Investigate the different types. Which is best?

Trend type              Equation                 R-squared                Comment on fit
Linear
___________________________________________________________________________
Logarithmic
___________________________________________________________________________
Exponential
___________________________________________________________________________
Power
__________________________________________________________________________
Polynomial (order 2)
_____________________________________________________________
You will notice that we are not looking at the Moving Averages, these are unreliable in Excel.

 A good compromise here seems to be the quadratic trend (polynomial
  order 2)       y = -1.7542x2 + 255.85x + 10131
 Highlight this equation and copy it into E3, edit the entry so that it reads
                   = -1.7542*x^2 + 255.85*x + 10131
 Copy this down to E22. This gives you the values for the trend line.

The Additive Model
The additive time series model is: Observed = Trend + Seasonal + Error

To obtain the Seasonal factors the Detrended series must be calculated:
 Detrended = Observed - Trend (= Seasonal + Error)
 In F3 enter the formula, =D3-E2 and Copy down to F22.

  The Seasonal factors are the average Detrended values for each
   quarter.
 We shall calculate the average of the detrended values for each of the
   first quarters. In G2 enter the formula, =AVERAGE(F3,F7,F11,F15,F19)
   (You can click on each of the cells F3, F7 in turn separating them by a
   comma.)
 Copy this down only as far as G6. This gives us the correct formulae for
  the averages of each of the other quarters, but copying further would mean
  that Excel would be choosing empty cells to calculate from.


                                                                                                 2
 We need to copy these 4 values into the rest of the cells. In G7, enter the
  formula =G3 ; copy down to G22. Can you see how this works?

What is the seasonal factor for the first quarter? _____________

What does it mean? _________________________________________


Forecasting from the additive model

The forecasts from the additive model are     Forecast = Trend + Seasonal
 Enter the formula in H3, =E3+G3 and copy down to H22.
 Highlight H3:H22 and drag it onto your chart.
  How closely does it match the data?


                                       Outward UK air passengers 1993-1997
                               20000

                               18000
         passengers (1000's)




                               16000

                               14000

                               12000

                               10000

                               8000

                               6000
                                       1   2   3   4   5   6   7   8   9 10 11 12 13 14 15 16 17 18 19 20

                                                                   time in quarters



Forecasting Errors

To assess the accuracy of the model we need to calculate the forecasting
errors:      Error = Observed - Forecast = Observed - Trend - Seasonal

 Enter the formula in I3, =D3-H3 and Copy down to I22.

The average percentage error (%MAD) in the forecasts can be calculated:
 In J22 type 'MAD%' and in J23 enter the formula
  =100*AVEDEV(I3:I22)/AVERAGE(D3:D22)

What is your average percentage forecasting error? ________

Use the Chart Wizard to plot a Scatterplot of your Errors against Time.
If the model is a good fit, the Errors should appear randomly scattered over
Time.



                                                                                                            3
Do you detect any pattern in your Errors? ______________________

                            Plot of errors against time

                1000

                 500

                   0
                        0    5        10        15        20   25
                 -500

                -1000



Forecasting 1998

To forecast the data for the next year, simply copy down the formulae for a
further 4 rows in all the columns except column D (Observed) and I (Error).
Complete the table below. Calculate the %MAD.

1998 Forecast     Q1                Q2                Q3            Q4            %MAD
Additive
Actual figure     11796             16378             19966         14155



It is suggested that you name your sheet 'additive' and save your work.



                        The Multiplicative Model
Aim
This exercise introduces you to the multiplicative model. Copy your first
sheet into another sheet of your workbook and label this second sheet
'multiplicative'.

In many economic series the seasonal effects are a percentage of the trend
rather than fixed amounts. This implies a multiplicative model:
                    Observed = Trend x Seasonal x Error

The trend will be the same for your multiplicative model. The changes
needed to the spreadsheet will be those to calculate the detrended values,
the Forecast and errors.
 Detrended = Observed/Trend             in F3, =100*D3/E3 and Copy down
 Forecast = Trend*Seasonal/100          in H3, =E3*G3/100 and Copy down
 %Error = 100*Observed/Forecast-100 in I3, =100*D3/H3-100,Copy down
 %MAD = Average(%Error)                 in J23, =AVEDEV(I3:I22)

   What is the seasonal factor for the first quarter? ____________

   What does it mean? ________________

                                                                              4
   Is this model a better fit? _________ How can you tell? _________


Forecasting 1998

To forecast the data for the next year we simply have to copy down the
formulae for a further 4 rows in all the columns except column D (Observed)
and I (Error). Complete the table below.

1998 Forecast    Q1              Q2              Q3            Q4                 %MAD

Multiplicative

Actual figure    11796           16378           19966         14155




Analysing Your Own Data
To analyse your own data, simply overwrite the Observed column entries and
the first entry in the Year column. You will need to fit a new Trendline and
enter its formula in the Trend column. (In Office 2000 this is automatically
updated.) Everything else should update automatically.

More data for time series analysis can be found on the resource page of my
website at http://www.mis.coventry.ac.uk/~styrrell/resource.htm




                                                                              5

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:26
posted:10/3/2010
language:English
pages:5