Assignment 3
Time series
Due date: 10th of May 2009
This assignment is marked out of 50 and is worth 5% of the assessment in this unit. You must use Excel to generate the relevant output. The data is provided in the file MCD2080_ A3_T109.xls.
This assignment is to be handed in as a printout of the completed time series worksheet, attached to a coversheet in the usual way. The worksheet has been set up to make printing as easy as possible (in “portrait” setup), but it is 留学生市场营销分析作业代写your responsibility to ensure that all required information actually appears on the pages you hand in. The presentation of the assignment is important and marks will be deducted for poorly presented work. This means that your work must be easy to read. However, elaborate features are not required. You must be able to provide a replacement copy of the assignment if this is needed for any reason, so make sure you keep a printed copy or an electronic copy with back-up.
Question
The quarterly earnings (in millions of dollars) of a large retail company, “Buymore”, have been recorded for the past four years. These data are shown in the following table and stored in the file MCD2080_A3_T109.xls. The data are also reproduced here:
Earnings ($ millions)
Quarter Year
2002 2003 2004 2005
(a) Graph the given time series as Graph (a). [6 marks]
(b) Comment on the components underlying the series [Textbox (b)].
[8 marks]
(c) If we calculate a four-quarter centred moving average, what components of the series is it expected to retain? [Textbox (c)]
In the appropriately-labelled column of Table (c) calculate a four quarter centred moving average for the time series. Add this averaged series to Graph (a).
Comment on the shape of this moving average series and what it indicates about the original series in Textbox (c).
[12 marks]
(d) Use the moving average values obtained in part (c) to determine the quarterly seasonal indices. [Fill in Column F of the worksheet and Table (d), and then fill in Column G of the worksheet.] Describe the typical quarterly fluctuations in sales, as indicated by the values of the seasonal indices in Textbox (d).
[10 marks]
(e) Use the seasonal indices computed in part (d) above to deseasonalise the original time series data [in Column H of the spreadsheet] and plot the deseasonalised time series along with the original time series as Graph (e). Make sure that your graph does not show any other series except the original data and the deseasonalised data.
Why is time series data frequently deseasonalised? Are their any extreme values in the quarterly earnings which are not wholly explained by the seasonal component of the time series? Discuss briefly in Textbox (e).
[9 marks]
Presentation: [5 marks]
留学生市场营销分析作业代写Please note that there is no assignment on forecasting or index numbers, but you should make sure that you prepare for these topics as well as all the others in the exam.
Excel tips
Part (a)
It is most convenient to use “Line graph” to graph the series so that you can use the Series tab in Step 2 of the Chart Wizard to choose the list of quarters as x axis labels.
Part (c), (e)
In order to add the moving average series to the graph (a), you can use the Series tab in Step 2 of the Chart Wizard again, or you can use the following method:
First highlight Graph (a). There should be a blue line around the series column, and the blue outline can be extended to include the next column by dragging the blue square at bottom right of the blue outline.
In order to add the deseasonalised values to the graph, highlight graph (e). This time, the blue outline should be extended to the rest of the table (columns all the way to H)
You can then click on chart wizard, go into the series tab of “Step 2” and “Remove” all series except the original series and the deseasonalised series.