Forecasting section (Solved)
business
Description
The Study Guide:
Forecasting section
a.
Moving average (Using
OFFSET functions)
b.
Weighted moving average
(With optimized weights)
c.
Exponential smoothing
(Simple exponential smoothing with optimized alpha)
d.
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.
