The Study Guide:
Moving average (Using
Weighted moving average
(With optimized weights)
(Simple exponential smoothing with optimized alpha)
Regression (Using TREND
function, quadratic, autoregressive, seasonal models)
The Assignments Requirements:
Use the QtrlySales.xlsx dataset. The dataset contains quarterly sales of a Norwegian export company over 13 consecutive years.
- Copy and paste the data and create four sheets within the workbook for each forecasting model.
- Use the first 10 years as the training data, and the last 3 years as the test data for the forecast models.
- For each forecast model, use MSE ass the accuracy metric. Create cells for train MSE and test MSE calculations.
- Create the moving average forecast. Using excel functions learned in class, create a moving average forecast model that dynamically adjusts based on the period parameter. Find the moving average period value that yields the best forecasting accuracy. (Ignore 1 period moving average. Look for the best fitting period greater or equal to 2.)
- Create a weighted moving average forecast model using the same number of periods from the moving average forecast with the highest accuracy. Use excel solver to optimize the period weights.
- Create a stationary exponential smoothing forecast model. Use excel solver to optimize the alpha parameter for the model.
- Create a regression forecast model. Add the quadratic, one-period autoregressive, quarterly seasonal factors into the regression model.
- Comment on the test accuracy between the four models. Write your suggestion for the best performing forecasting model.