BUSINESS STATISTICS CALCULATION

QUESTION

MCD2080: Business Statistics

Assignment 3: Time series

Status: Individual

Hurdle:

Weighting: 5%

Word limit:

Due date: Monday – Teaching Week 12: (14th May 2012)

________________________________________________________________________________                             

This assignment 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_T112.xlsx.

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 file MCD2080_A3_T112.xlsx contains data on Australia’s electricity and fuel expenditure [in millions of dollars ($)](Source: http://www.abs.gov.au/statistics/ Accessed: 22 November 2011). The data covers the period from the first quarter of 2000 to the end of 2006. The data is also reproduced here:

Year and quarter Time Quarterly electricity and fuel expenditure ($millions) Year and quarter Time Quarterly electricity and fuel expenditure ($millions)
2000 Q1

1

1479

2003 Q3

15

2959

2000 Q2

2

1918

2003 Q4

16

2315

2000 Q3

3

2420

2004 Q1

17

2258

2000 Q4

4

1840

2004 Q2

18

2621

2001 Q1

5

1735

2004 Q3

19

3175

2001 Q2

6

2130

2004 Q4

20

2472

2001 Q3

7

2543

2005 Q1

21

2532

2001 Q4

8

1928

2005 Q2

22

2660

2002 Q1

9

1906

2005 Q3

23

3280

2002 Q2

10

2210

2005 Q4

24

2545

2002 Q3

11

2719

2006 Q3

25

2469

2002 Q4

12

2115

2006 Q2

26

2941

2003 Q1

13

1983

2006 Q3

27

3332

2003 Q2

14

2412

2006 Q4

28

2706

 

(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 the main table 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 electricity and fuel expenditure, 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).  Why is time series data frequently deseasonalised? Are there any extreme values in the quarterly electricity and fuel expenditures which are not wholly explained by the seasonal component of the time series? Discuss briefly in Textbox (e).

[9 marks]

Presentation:

Marks are taken off for poor spelling/ grammar/ punctuation; poor layout of work (including tables/ graphs across two pages, missing text in text boxes); poor rounding of figures, inappropriate font size / printing size or graph colouring and poor presentation of tables (e.g. missing borders).

[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 trend-line 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.

SOLUTION

Year Quarter Time in quarters (t) Quarterly Electricity and Fuel Expenditure ($ million) (Yt) (c)  4 quarter centred Moving average (MA) Quarterly Expenditure as proportion of MA value (Yt/MA) Seasonal indices Deseasonalised data
Graph (a)
2000 1 1 1479 1479.0 1.000 0.000 0.00
2 2 1918 1698.5 1.129 247.866 194.38
3 3 2420 1939.0 1.248 600.320 385.40
4 4 1840 1914.3 0.961 -71.370 -77.25
2001 1 5 1735 1878.4 0.924 -132.453 -155.25
2 6 2130 1920.3 1.109 232.559 189.03
3 7 2543 2009.3 1.266 675.482 421.70
4 8 1928 1999.1 0.964 -68.595 -73.75
2002 1 9 1906 1988.8 0.958 -79.332 -86.37
2 10 2210 2010.9 1.099 218.813 181.16
3 11 2719 2075.3 1.310 843.405 491.32
4 12 2115 2078.6 1.018 37.055 35.79
2003 1 13 1983 2071.2 0.957 -84.472 -92.16
2 14 2412 2095.6 1.151 364.209 274.92
3 15 2959 2153.1 1.374 1107.483 586.39
4 16 2315 2163.3 1.070 162.395 141.80
2004 1 17 2258 2168.8 1.041 92.843 85.65
2 18 2621 2193.9 1.195 510.183 357.47
3 19 3175 2245.6 1.414 1314.098 657.35
4 20 2472 2256.9 1.095 235.601 196.38
2005 1 21 2532 2270.0 1.115 292.240 234.89
2 22 2660 2287.7 1.163 432.851 320.17
3 23 3280 2330.9 1.407 1335.616 674.48
4 24 2545 2339.8 1.088 223.206 188.66
2006 1 25 2469 2345.0 1.053 130.601 117.81
2 26 2941 2367.9 1.242 711.830 461.43
3 27 3332 2403.6 1.386 1287.012 669.72
4 28 2706 2414.4 1.121 326.827 260.18
Table (d)
Quarter 2000 2001 2002 2003 2004 2005 2006 Seasonal average
1 1479 1735 1906 1983 2258 2532 2469 2052
2 1918 2130 2210 2412 2621 2660 2941 2413
3 2420 2543 2719 2959 3175 3280 3332 2918
4 1840 1928 2115 2315 2472 2545 2706 2274
Graph (e)
Textbox (e) here

LB75

“The presented piece of writing is a good example how the academic paper should be written. However, the text can’t be used as a part of your own and submitted to your professor – it will be considered as plagiarism.

But you can order it from our service and receive complete high-quality custom paper.  Our service offers Statistics  essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”

Please  Click on the  below links to Chat Now  or fill the Order Form !
order-now-new                                     chat-new (1)