Forecasting section (Solved)



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.

  1. Copy and paste the data and create four sheets within the workbook for each forecasting model.
  2. Use the first 10 years as the training data, and the last 3 years as the test data for the forecast models.
  3. For each forecast model, use MSE ass the accuracy metric. Create cells for train MSE and test MSE calculations. 
  4. 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.)
  5. 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. 
  6. Create a stationary exponential smoothing forecast model. Use excel solver to optimize the alpha parameter for the model. 
  7. Create a regression forecast model. Add the quadratic, one-period autoregressive, quarterly seasonal factors into the regression model. 
  8. Comment on the test accuracy between the four models. Write your suggestion for the best performing forecasting model. 

Related Questions in business category

The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.