Document Sample

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:

Tags:
microsoft excel, sales forecasting, forecasting software, neural network, how to, sales data, Excel add-in, MS Excel, neural networks, Financial Forecasting

Stats:

views: | 26 |

posted: | 10/3/2010 |

language: | English |

pages: | 5 |

OTHER DOCS BY hcj

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.