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:

1. Naïve,
2. Historical Average
3. 4-Week Moving Average
4. 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

5. Exponential Smoothing with alpha = 0.3
6. 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.