Excel – Quant decision making
Using the following data:
Week Sales
1 105
2 135
3 120
4 105
5 90
6 120
7 145
8 140
9 100
10 80
11 100
For each of the following forecast methods, generate a forecast for Week 12 Sales along with MAE, MSE and MAPE:
Naïve,
Historical Average
4-Week Moving Average
4-Week Weighted Moving average with the following weights:
a. Most recent week: 7
b. 2nd most recent week: 5
c. 3rd most recent week: 3
d. 4th most recent week: 1
Exponential Smoothing with alpha = 0.3
Simple Linear Regression ( Do not perform diagnostic tests. The x-variable will fail – ignore this.)
Which of the above methods results in the best MAE? The best MSE? The best MAPE?
Determine alpha for exponential smoothing that results in an MSE of exactly 505.
Each of the following forecast methods, generate a forecast for Week 12 Sales along with MAE, MSE and MAPE.
