F Dewhurst: Quantitative Methods

The following table shows new car registrations in the UK from 1999 to 2002.

(a)draw a graph of the data

(b)Estimate the trend in the data by eye

(c)What seasonal factors are evident? Estimate their magnitude.

(d)Can any cyclical factors be seen in the graph?

New car registrations, UK

1999 / 2000 / 2001 / 2002
Jan / 185 / Jan / 190.3 / Jan / 197.9 / Jan / 213.5
Feb / 83.1 / Feb / 79.3 / Feb / 83.8 / Feb / 98.9
Mar / 376.8 / Mar / 412.6 / Mar / 426.2 / Mar / 446.3
Apr / 183.3 / Apr / 176.2 / Apr / 185.8 / Apr / 214
May / 179.2 / May / 201 / May / 206.6 / May / 219
Jun / 199.9 / Jun / 203.8 / Jun / 226.2 / Jun / 217
Jul / 172.8 / Jul / 164.2 / Jul / 183.7 / Jul / 204.8
Aug / 78.4 / Aug / 79.3 / Aug / 83.4 / Aug / 93
Sep / 398 / Sep / 369 / Sep / 462.5 / Sep / 446.9
Oct / 254.8 / Oct / 165.4 / Oct / 195.8 / Oct / 193
Nov / 153.2 / Nov / 176.3 / Nov / 197.3 / Nov / 182.9
Dec / 92.8 / Dec / 117.6 / Dec / 136.9 / Dec / 152.8

Source: UK Department for Transport,

Answer Guidelines

(a) The following graph has been drawn using Excel.

(b) Note that there is a clear upward trend in the peaks, but in the low months the trend is less clear. Overall, registrations seem to be increasing by about 10000 per annum.

(c) Peaks occur each year in March and September (corresponding to regular changes in the registration numbering system). These peaks appear to be about double the average monthly number. Troughs occur in the months immediately preceding the registration number changes (February and August), which appear to lead to registration levels that are about half the monthly average. Less pronounced troughs also occur in December (about 0.75 of monthly average) and April (about 90% to 95% of monthly average).

(d) Looking at the peaks, it seems possible that there is a cyclical component, with a trough in 2000/1.A longer time series would be needed to confirm this.

Use the additive classical time series model, TSt=Tt+St+It, to forecast periods 16 to 20 for the following data:

Period (t) / Income (xt)
1 / 35
2 / 51
3 / 55
4 / 41
5 / 59
6 / 64
7 / 62
8 / 75
9 / 88
10 / 78
11 / 89
12 / 109
13 / 92
14 / 108
15 / 127

Answer Guidelines

First consider the graph of the data:

It appears that the data has a distinct upward trend and has a seasonal element with a length of three periods. The trend is calculated using least squares (in Excel the Intercept and Slope functions can be used –remember to get the x and y axes the right way round). For this exercise the slope is 5.67 and the intercept is 30.19. The seasonal element is estimated by taking the trend from the original data, then taking the median difference for each position in the cycle (see table 2.2). The forecasts are in table 2.1.

Table 2.1

Period (t) / Income (xt) / Forecast / Trend (at+b) / xt-trend
1 / 35 / 35.9 / -0.9
2 / 51 / 41.5 / 9.5
3 / 55 / 47.2 / 7.8
4 / 41 / 52.9 / -11.9
5 / 59 / 58.5 / 0.5
6 / 64 / 64.2 / -0.2
7 / 62 / 69.9 / -7.9
8 / 75 / 75.5 / -0.5
9 / 88 / 81.2 / 6.8
10 / 78 / 86.9 / -8.9
11 / 89 / 92.5 / -3.5
12 / 109 / 98.2 / 10.8
13 / 92 / 103.9 / -11.9
14 / 108 / 109.5 / -1.5
15 / 127 / 115.2 / 11.8
16 / 112.5 / 120.9
17 / 126.5 / 126.5
18 / 140.6 / 132.2
19 / 129.5 / 137.9
20 / 143.5 / 143.5

Table 2.2 calculation of St (following the method used in example 12.2.3 in the book)

Period / xt-trend / Period / xt-trend / Period / xt-trend / Period / xt-trend / Period / xt-trend / Median / Adj median
1 / -0.9 / 4 / -11.9 / 7 / -7.9 / 10 / -8.9 / 13 / -11.9 / -8.9 / -8.3
2 / 9.5 / 5 / 0.5 / 8 / -0.5 / 11 / -3.5 / 14 / -1.5 / -0.5 / 0.0
3 / 7.8 / 6 / -0.2 / 9 / 6.8 / 12 / 10.8 / 15 / 11.8 / 7.8 / 8.3
Adjustment factor / -1.6

Finally the forecasts can be graphed to ensure that they make sense:

Develop a classical time series forecast of new car registrations for each month of 2003. Use the multiplicative model TSt=Tt*St*It (i.e. ignore the cyclical component)

New car registrations, UK

Source: UK Department for Transport,

Answer Guidelines

The linear trend is calculated using slope and intercept functions in Excel (alternatively it can be calculated using formulae 9.3.5 and 9.3.6 in the book). Months (t) were numbered from 1 to 48. This givesslope (a) = 0.647, Intercept (b) =191.668

Seasonal indices are calculated using the ratio to trend method, again in excel. This yields the following results:

Ratio to trend for seasonal variation
1999 / 2000 / 2001 / 2002 / Median /

Adjusted

Jan / 0.96 / 0.95 / 0.95 / 0.99 / 0.96 / 0.96
Feb / 0.43 / 0.40 / 0.40 / 0.46 / 0.42 / 0.42
Mar / 1.95 / 2.05 / 2.04 / 2.06 / 2.04 / 2.05
Apr / 0.94 / 0.87 / 0.89 / 0.98 / 0.91 / 0.92
May / 0.92 / 0.99 / 0.98 / 1.00 / 0.99 / 0.99
Jun / 1.02 / 1.00 / 1.07 / 0.99 / 1.01 / 1.02
Jul / 0.88 / 0.81 / 0.87 / 0.93 / 0.87 / 0.88
Aug / 0.40 / 0.39 / 0.39 / 0.42 / 0.40 / 0.40
Sep / 2.02 / 1.80 / 2.17 / 2.02 / 2.02 / 2.03
Oct / 1.29 / 0.80 / 0.92 / 0.87 / 0.89 / 0.90
Nov / 0.77 / 0.85 / 0.92 / 0.82 / 0.84 / 0.84
Dec / 0.47 / 0.57 / 0.64 / 0.69 / 0.60 / 0.60
Total / 11.96 / 12.00

Forecasts are then constructed by using the formula

yt=(at+b)(St). Note that t is a month number.

Year / Month / Month number (t) / Forecast registrations
(yt)
2003 / Jan / 49 / 215

Feb

/ 50 / 94
Mar / 51 / 461
Apr / 52 / 207
May / 53 / 224
Jun / 54 / 230
Jul / 55 / 199
Aug / 56 / 90
Sep / 57 / 463
Oct / 58 / 206
Nov / 59 / 193
Dec / 60 / 139

The graph of the resulting forecasts is shown below.

The data in the table show sales of a product for the first 11 months of a year. Use the simple exponential smoothing model to forecast sales for December. Try this with α=0.1, 0.5 and 0.9. Compare the mean square errors of the models.

Month

Month number

Sales

Jan

200

Feb

135

Mar

195

Apr

197

May

310

Jun

175

Jul

155

Aug

130

Sep

220

Oct

277

Nov

235

Answer Guidelines

Forecasts were constructed in excel as shown below

Month / Month number / Sales / Forecasts α=0.1 / Forecasts α=0.5 / Forecasts α=0.9 / MSE α=0.1 / MSE α=0.5 / MSE α=0.9
Jan / 1 / 200
Feb / 2 / 135 / 200.0 / 200.0 / 200.0
Mar / 3 / 195 / 193.5 / 167.5 / 141.5 / 2.25 / 756.25 / 2862.25
Apr / 4 / 197 / 193.7 / 181.3 / 189.7 / 11.2225 / 248.0625 / 54.0225
May / 5 / 310 / 194.0 / 189.1 / 196.3 / 13459.48 / 14610.77 / 12935.65
Jun / 6 / 175 / 205.6 / 249.6 / 298.6 / 935.534 / 5559.566 / 15283.51
Jul / 7 / 155 / 202.5 / 212.3 / 187.4 / 2258.897 / 3281.142 / 1047.341
Aug / 8 / 130 / 197.8 / 183.6 / 158.2 / 4593.459 / 2877.317 / 797.2867
Sep / 9 / 220 / 191.0 / 156.8 / 132.8 / 841.1416 / 3991.673 / 7599.72
Oct / 10 / 277 / 193.9 / 188.4 / 211.3 / 6905.975 / 7848.16 / 4318.808
Nov / 11 / 235 / 202.2 / 232.7 / 270.4 / 1075.314 / 5.266666 / 1255.16
Dec / 205.5 / 233.9 / 238.5
Total / 3342.586 / 4353.134 / 5128.194

Performance of the model with different levels of α are shown in the MSE columns. In this case a lower value for alpha (i.e. the smoothing that gives the least emphasis to the most recent data) performed best. The forecasts are illustrated in the graph below: notice how the curve for α=0.1 is much flatter (less responsive) than the curve for α=0.9.

Use the Holt-Winters exponential smoothing model to forecast new car registrations in 2003. Use the following values for the parameters: α=0.2, β=0.5, γ=0.5 (if you input these values to cells in the spreadsheet and use the cell references in the formulae, then you can test the effects of different values afterwards).

New car registrations, UK

Source: UK Department for Transport,

Answer Guidelines

The results are shown in the spreadsheet below. Formulae for successive values for L, T, S and F use formulae 12.3.5 to 12.3.8 in the book. The starting values used are: L1 = average of 48 data points; T1 = trend estimated in Q3; S1 to S12 =seasonal factors estimated in Q3.

The table immediately below compares the results from the classical time series forecast (see Q3) and the Holt-Winters ESM:

Year / Month / Forecast registrations
(Classical) / Forecast registrations
Holt-Winters
2003 / Jan / 215 / 216

Feb

/ 94 / 96
Mar / 461 / 460
Apr / 207 / 210
May / 224 / 226
Jun / 230 / 234
Jul / 199 / 206
Aug / 90 / 94
Sep / 463 / 473
Oct / 206 / 206
Nov / 193 / 203
Dec / 139 / 153

Holt Winters ESM: New car registrations: UK

t / TS / T / L / S / F
1999 / Jan / 1 / 185.0 / 0.647 / 207.5 / 0.96
Feb / 2 / 83.1 / -0.282 / 206.3 / 0.42 / 200
Mar / 3 / 376.8 / -2.514 / 201.6 / 2.05 / 86
Apr / 4 / 183.3 / -2.453 / 199.2 / 0.92 / 408
May / 5 / 179.2 / -4.032 / 193.6 / 0.99 / 181
Jun / 6 / 199.9 / -3.311 / 191.0 / 1.02 / 188
Jul / 7 / 172.8 / -2.384 / 189.5 / 0.88 / 191
Aug / 8 / 78.4 / -1.347 / 189.2 / 0.40 / 164
Sep / 9 / 398.0 / -0.501 / 189.5 / 2.03 / 75
Oct / 10 / 254.8 / 8.988 / 208.0 / 0.90 / 383
Nov / 11 / 153.2 / 5.488 / 210.0 / 0.84 / 195
Dec / 12 / 92.8 / -0.710 / 203.1 / 0.60 / 181
2000 / Jan / 13 / 190.3 / -1.140 / 201.5 / 0.95 / 194
Feb / 14 / 79.3 / -2.201 / 198.3 / 0.41 / 84
Mar / 15 / 412.6 / -1.692 / 197.1 / 2.07 / 402
Apr / 16 / 176.2 / -2.039 / 194.7 / 0.91 / 179
May / 17 / 201.0 / -1.012 / 194.7 / 1.01 / 191
Jun / 18 / 203.8 / -0.326 / 195.1 / 1.03 / 197
Jul / 19 / 164.2 / -1.088 / 193.2 / 0.86 / 171
Aug / 20 / 79.3 / -0.326 / 193.7 / 0.40 / 76
Sep / 21 / 369.0 / -1.458 / 191.1 / 1.98 / 392
Oct / 22 / 165.4 / -1.988 / 188.5 / 0.89 / 170
Nov / 23 / 176.3 / 0.300 / 191.1 / 0.88 / 157
Dec / 24 / 117.6 / 0.610 / 192.1 / 0.61 / 116
2001 / Jan / 25 / 197.9 / 2.121 / 195.7 / 0.98 / 184
Feb / 26 / 83.8 / 2.833 / 199.2 / 0.41 / 81
Mar / 27 / 426.2 / 3.192 / 202.8 / 2.09 / 419
Apr / 28 / 185.8 / 2.978 / 205.5 / 0.91 / 188
May / 29 / 206.6 / 2.552 / 207.7 / 1.00 / 211
Jun / 30 / 226.2 / 3.481 / 212.1 / 1.05 / 217
Jul / 31 / 183.7 / 3.195 / 215.0 / 0.86 / 186
Aug / 32 / 83.4 / 2.059 / 215.9 / 0.39 / 88
Sep / 33 / 462.5 / 3.628 / 221.1 / 2.04 / 431
Oct / 34 / 195.8 / 3.220 / 223.9 / 0.88 / 199
Nov / 35 / 197.3 / 2.874 / 226.5 / 0.88 / 200
Dec / 36 / 136.9 / 2.442 / 228.5 / 0.60 / 140
2002 / Jan / 37 / 213.5 / 1.095 / 228.2 / 0.96 / 227
Feb / 38 / 98.9 / 2.010 / 231.1 / 0.42 / 95
Mar / 39 / 446.3 / 0.079 / 229.3 / 2.02 / 487
Apr / 40 / 214.0 / 0.718 / 230.6 / 0.92 / 208
May / 41 / 219.0 / -0.586 / 228.8 / 0.98 / 232
Jun / 42 / 217.0 / -2.706 / 223.9 / 1.01 / 239
Jul / 43 / 204.8 / -0.988 / 224.7 / 0.89 / 190
Aug / 44 / 93.0 / 0.205 / 226.1 / 0.40 / 88
Sep / 45 / 446.9 / -0.466 / 224.9 / 2.01 / 461
Oct / 46 / 193.0 / -1.000 / 223.4 / 0.87 / 198
Nov / 47 / 182.9 / -2.375 / 219.6 / 0.85 / 195
Dec / 48 / 152.8 / 1.205 / 224.4 / 0.64 / 131
2003 / Jan / 49 / 216.3 / 1.205 / 225.6 / 0.96 / 216
Feb / 50 / 95.6 / 1.205 / 226.8 / 0.42 / 96
Mar / 51 / 459.9 / 1.205 / 228.0 / 2.02 / 460
Apr / 52 / 210.4 / 1.205 / 229.2 / 0.92 / 210
May / 53 / 225.9 / 1.205 / 230.4 / 0.98 / 226
Jun / 54 / 233.7 / 1.205 / 231.6 / 1.01 / 234
Jul / 55 / 206.1 / 1.205 / 232.8 / 0.89 / 206
Aug / 56 / 94.3 / 1.205 / 234.1 / 0.40 / 94
Sep / 57 / 473.2 / 1.205 / 235.3 / 2.01 / 473
Oct / 58 / 206.3 / 1.205 / 236.5 / 0.87 / 206
Nov / 59 / 203.1 / 1.205 / 237.7 / 0.85 / 203
Dec / 60 / 153.4 / 1.205 / 238.9 / 0.64 / 153