Statistics Assignment

Statistics Assignment

Data:
For this assignment, please download the Homework data file.
Requirements:
1. Create a line chart and identify the time series components in the time series. Then, compute
the correlation between the time series variable and time using either the =CORREL() function or
the correlation tool in the date analysis toolpack. Justify your answer. (Hint: it should be some
combination of average or base, cycle, trend, and random variation.)
2. Create as many forecast as possible on the historical data using each of the methods below.
a. 3-period moving average.
c. Exponential smoothing forecast with alpha = 0.8.
d. Trend forecast (whether or not there is a trend). Use the =TREND() function in Excel.

IMPORTANT NOTES: When computing your moving average forecasts, do not use the Moving
Average Data Analysis tool. This tool will not give you a valid forecast because it uses the
current period in the computation. Instead, use the AVERAGE() function. Also, for both the ES
and the MA forecasts, do not include the period you are forecasting in the history you are using
to compute the forecast.
3. Starting with the fourth period, compute the MAE for each forecasting model, and choose the
best model based on this analysis.
4. Using the best model, make a new forecast for the next period.
Deliverables
Please place all of your analysis on a single spreadsheet. Clearly label your answers. When you
have completed the assignment, post your Excel file on the HW 4 assignment dropbox.
Hints: In this assignment, you are using your entire history to build good models and to test the
forecasting skill of the models. Once you have computed the forecasts and calculated the MAEs
for each model, you will choose the most accurate model on historical data to make a future
forecast. The moving average forecasts will begin at period 4, the ES forecasts will begin at
period 2 (with the naïve starting value), and the trend forecasts will begin at period 1. For
consistency, you should compute the MAEs for periods 3-98 for each. the first draft is by
Wednesday