A major source of revenue in Texas is a state sales tax on c

A major source of revenue in Texas is a state sales tax on certain types of goods and services. Data are complied and the state comptroller uses them to project future revenues for the state budget. One particular category of foods is classified as Retail Trade. Four years of quarterly data (in $ millions) for one particular area of southeast Texas follow:

Quarter

Year 1

Year 2

Year 3

Year 4

1

218

225

234

250

2

247

254

265

283

3

243

255

264

289

4

292

299

327

356

Using Excel QM find the following:

A) Compute seasonal indices for each quarter based on a CMA (please explain)

B) Deseasonalize the data and develop a trend line on the deseasonalized data

C) Use the trend line to forecast the sales for each quarter of year 5

D) Use the season indices to adjust the forecasts found in part C to obtain the final forecasts.

Quarter	Year 1	Year 2	Year 3	Year 4
1	218	225	234	250
2	247	254	265	283
3	243	255	264	289
4	292	299	327	356

Solution

a) In order to build the seasonal indices based on a CMA, we must first compute a series of moving averages (MA) and then average the MA. The formula used to calculate the seasonal index for each quarter can be found in the following Powerpoint presentation, slide 28. The document calls it \"seasonal ratio\" instead of seasonal index. In order to easily compute all these averages, I copied the data you provide into an Excel worksheet and did the calculations with that program. In any case, the formulas were taken from the links above. I obtained the following results (these are not the season indices yet). Quarter Data MA CMA Percentage 1 218 2 247 3 243 250 250.875 96.86098655 4 292 251.75 252.625 115.5863434 1 225 253.5 255 88.23529412 2 254 256.5 257.375 98.68868383 3 255 258.25 259.375 98.31325301 4 299 260.5 261.875 114.176611 1 234 263.25 264.375 88.5106383 2 265 265.5 269 98.51301115 3 264 272.5 274.5 96.17486339 4 327 276.5 278.75 117.309417 1 250 281 284.125 87.98944127 2 283 287.25 290.875 97.29265148 3 289 294.5 4 356 The percentage column is simply the data column, divided by the CMA, and multiplied by 100. Now, we average the percentages for each quarter. We get: Quarter 1: 88.24 Quarter 2: 98.16 Quarter 3: 97.11 Quarter 4: 115.69 Since the average of these four number is 99.8, we multiply them by 100/99.8=1.001 in order to normalize them, thus getting: Quarter 1: 88.41 Quarter 2: 98.35 Quarter 3: 97.3 Quarter 4: 115.91 So these are the seasonal indices. b) In order to deseasonalize the data, we simply multiply each data value by 100/(seasonal index). We get: Quarter Data Desasonalized 1 218 246.5556045 2 247 251.1252326 3 243 249.7255331 4 292 251.902901 1 225 254.4725276 2 254 258.242142 3 255 262.0576582 4 299 257.9416691 1 234 264.6514287 2 265 269.4258569 3 264 271.3067521 4 327 282.0967418 1 250 282.7472529 2 283 287.7264811 3 289 296.9986793 4 356 307.1144957 As you can see, the spikes in every 4th quarter are smoothed. In order to compute the trend line, we must run a least squares regression. The \'explanatory\' variable here will be simply a time index. Therefore, calling Y the explained variable (the actual data) and X the explanatory variable, you would have to run a regression on the following data (also adding a constant) Y X 246.5556045 1 251.1252326 2 249.7255331 3 251.902901 4 254.4725276 5 258.242142 6 262.0576582 7 257.9416691 8 264.6514287 9 269.4258569 10 271.3067521 11 282.0967418 12 282.7472529 13 287.7264811 14 296.9986793 15 307.1144957 16 So, we have to find the coefficients \'a\' and \'b\' in the following regression: Y = a + bX The formula for a and b can be found in slide 6 in the Powerpoint presentation I provided above. We get that these values are: a = 237.28 b = 3.65 So the trend line would then be... Time Trend 1 240.93 2 244.58 3 248.23 4 251.88 5 255.53 6 259.18 7 262.83 8 266.48 9 270.13 10 273.78 11 277.43 12 281.08 13 284.73 14 288.38 15 292.03 16 295.68 Each value was calculated using the coefficients \'a\' and \'b\'. For example, the first value is simply 237.28+3.65*1, the next one is 237.28+3.65*2 ans so on. c) This forecast can be obtained by simply using as \"explanatory variables\" the values 17, 18, 19 and 20, which would correspond to each quarter of the fifth yeard (recall that the 4th quarter of the 4th year would be the 16th value). We get then: 17 299.33 18 302.98 19 306.63 20 310.28 d) Since the trend forecasts were done using deseasonalized data, we must now adjust each forecast to see the actual value for each quarter. This is simply a matter of undoing what we did in question b. We must take each value and multiply it by (seasonal index)/100. We then get: Forecasts deseasonalized final forecast 17 299.33 264.1441313 18 302.98 297.419657 19 306.63 297.7879181 20 310.28 358.9653847 Notice how we not only get the trend that shows that revenue is growing quarter after quarter, but we also get the reasonable forecast that the first quarter will be significantly worse, and the fourth one will be significantly better (the data provided shows that this is what happened in the previous 4 years)