A major source of revenue in Texas is a state sales tax on certain types of goods and services. Data are compiled and the state comptroller use them to project future revenue for the state budget. One particular category of goods is classified as a Retail Trade. Four years of quarterly data (in $millions) for one particular area of Southeast Texas follows: ( Please show answers on excel spreadsheet) thank you!
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
A). Compute seasonal indices for each quarter based on a CMA.
B). Deseasonalize the data and develop a trend line on the deseasonalize data.
C). Use the trend line to forecast the sales for each quarter of 5 years.
D). Use the seasonal indices to adjust the forecast found in part (c) to obtain the final forecast.