Homework SourseThe following table shows Walker Corporations sales for the
) The following table shows Walker Corporation’s sales for the last 12 months. Using a three month moving average:
Calculate forecasted sales for each month
Calculate the forecast error for each month
Calculate the Root Mean Square Error (RMSE)
Is 3 months the “best” moving average? How would you determine the “best” moving average period (number of months?
Actual
Forecasted
Forecast
Month
Sales
Sales
Error
January-14
1,950
February-14
1,400
March-14
1,925
April-14
1,960
May-14
2,800
June-14
1,800
July-14
1,600
August-14
1,450
September-14
2,000
October-14
2,250
November-14
1,950
December-14
2,650
January-15
RMSE =
Solution
excel is used for the analysis.
Rsme=sqrt(sum(yi-yestimated)^2/n
rsme=310.8881
To decide the period for moving average, we would try to use other periods for the data and calculate their rsme. Whichver period has lesser rsme is said to be the best period to be used for this data.
| Moving Average | |||
| number of periods: | 3 | ||
| t | Data | 3MA | error |
| 1 | 1,950 | ||
| 2 | 1,400 | ||
| 3 | 1,925 | 1,758.3 | 167 |
| 4 | 1,960 | 1,761.7 | 198 |
| 5 | 2,800 | 2,228.3 | 572 |
| 6 | 1,800 | 2,186.7 | -387 |
| 7 | 1,600 | 2,066.7 | -467 |
| 8 | 1,450 | 1,616.7 | -167 |
| 9 | 2,000 | 1,683.3 | 317 |
| 10 | 2,250 | 1,900.0 | 350 |
| 11 | 1,950 | 2,066.7 | -117 |
| 12 | 2,650 | 2,283.3 | 367 |
