NOTE 1: In creating spreadsheets, you are to apply the fundamental spreadsheet skills that you acquired in your previous spreadsheet classes. That is, you are to use relative and absolute referencing, functions, properly sized column widths and row heights, consistent application of font types, effects and sizes, consistent application of decimal places, number formats/data types, and so forth, wherever applicable. You may place any responses that require a textual answer in comment boxes, text boxes, or simply place them in a cell; be sure to place and identify these answers in such a way that they can be found easily. (If you place comments conspicuously and save the file with them in view, when the file is reopened they will be in plain sight). NOTE 2: Files must have formulas in any cell where they are applicable. If the spreadsheet does not have live formulas, the file will be returned ungraded.

CHAPTER 12: A major source of revenue in Texas is a state sales tax. One category of tax is classified as Retail Trade. The following table gives four years of quarterly data:

YEAR	QUARTER	SALES
2015	1	218
	2	247
	3	243
	4	292
2016	5	225
	6	254
	7	255
	8	299
2017	9	234
	10	265
	11	264
	12	327
2018	13	250
	14	283
	15	289
	16	356

Note that you may want your spreadsheet to be laid out such that your columns of forecasted data are just to the right of the given data, with separate columns for each forecasting method and error measurement, such as:

Year

Quarter

Sales

Naive Forecast

Weighted moving average forecast

linear trend line forecast

Seasonally Adjusted LTL Forecast

Naive Absolute Error

WMA Absolute Error

LTL Absolute Error

Seasonally adjusted Absolute Error

(You may choose to reorder the columns of reword the headings, of course.)

a. Graph the sales tax revenues. Can you identify any trends, cycles, and/or seasonal patterns? Be sure to fully describe and explain your analysis.

b. Use the quarterly sales tax revenues to compute a nave forecast for the periods between the fourth quarter of 2015 through the fourth quarter 2018. Extend the forecast into the first quarter of 2019.

c. Use the quarterly sales tax revenues to compute a weighted three-period moving average forecast for all of the periods between the fourth quarter of 2015 through the fourth quarter 2018 using weights of 0.60, 0.25, and 0.15 for the most recent, next recent, and most distant period, respectively (smallest weight is oldest period). Extend the forecast into the first quarter of 2019

EXCEL PROBLEM SET 1 d. How would you extend the forecast through the entirety of 2019? Could you use any of the time series forecast methods (moving average, weighted moving average, exponential smoothing, linear trend line, etc.) to do this? Could you forecast 2020? Explain your reasoning.

e. Develop a linear trend line model using the least squares method and the existing data to develop a linear equation for your model. Use the equation to compute a sales tax revenue forecast for the first quarter of 2015 through the fourth quarter 2018. Extend the forecast into the first quarter of 2019.

f. Calculate the slope and intercept variables for the linear trend line model in Step e using Excels SLOPE and INTERCEPT functions. (NOTE: These can be used to check the values calculated in Step e).

g. Develop seasonal weights using the sales tax revenue data for 2015 through 2018. Use the weights you've developed to create a seasonally adjusted forecast for the first quarter of 2015 through the fourth quarter 2018 using the linear trend line model developed under Step e.

h. Compute the absolute value of the forecast error for the periods between the fourth quarter of 2015 through the fourth quarter of 2018 for each of the four forecast methods developed in Steps b, c, e, and g (the nave, the three-period moving average, the linear trend line, and the seasonally adjusted linear trend line forecasts).

i. Calculate the Mean Absolute Deviation (MAD) for each of the forecasts developed in Steps b, c, e, and g using the absolute value of the forecast error calculations developed under Step h. Compare the accuracy of the forecasts using MAD. Which forecast appears to be the most accurate? Use the numbers you've generated to support your assessment.

You are on the right track: if the slope value for the least squares calculations under Step E is 5.01; if the seasonal weight for the first quarter under Step G is 21.6 (or 0.86 using the indices method); if the absolute value of the nave forecast error for the 4th quarter of 2015is 49; and if the MAD values for the forecasts developed under Steps B, C, E & G are 41.0, 33.4, 25.9, and 5.3, respectively (MAD for the seasonally adjusted forecast may also be 6.0 or 3.5, depending on the method used under Step G).