In: Math
Two independent methods of forecasting based on judgment and
experience have been prepared each month for the past 10 months.
The forecasts and actual sales are as follows:
Month | Sales | Forecast 1 | Forecast 2 |
1 | 845 | 815 | 820 |
2 | 835 | 835 | 825 |
3 | 795 | 820 | 825 |
4 | 820 | 830 | 795 |
5 | 795 | 785 | 780 |
6 | 835 | 785 | 771 |
7 | 805 | 810 | 785 |
8 | 850 | 780 | 785 |
9 | 840 | 805 | 830 |
10 | 805 | 815 | 825 |
a. Compute the MSE and MAD for each forecast.
(Round your answers to 2 decimal
places.)
MSE | MAD | |
Forecast 1 | ? | ? |
Forecast 2 | ? | ? |
b. Compute MAPE for each forecast.
(Round your intermediate calculations to 5 decimal places
and final answers to 4 decimal places.)
MAPE F1 | ? % |
MAPE F2 | ? % |
c. Prepare a naive forecast for periods 2
through 11 using the given sales data. Compute each of the
following; (1) MSE, (2) MAD, (3) tracking signal at month 10, and
(4) 2s control limits. (Round your answers to 2
decimal places.)
MSE | ? |
MAD | ? |
Tracking signal | ? |
Control limits | 0 ± ? |
a. Compute the MSE and MAD for each forecast. (Round your answers to 2 decimal places.)
Month | Sales | Forecast 1 | Forecast 2 | Error(Seles - Forecast 1) | Absolute Error | Error(Seles - Forecast 2) | Absolute Error |
1 | 845 | 815 | 820 | 30 | 30 | 25 | 25 |
2 | 835 | 835 | 825 | 0 | 0 | 10 | 10 |
3 | 795 | 820 | 825 | -25 | 25 | -30 | 30 |
4 | 820 | 830 | 795 | -10 | 10 | 25 | 25 |
5 | 795 | 785 | 780 | 10 | 10 | 15 | 15 |
6 | 835 | 785 | 771 | 50 | 50 | 64 | 64 |
7 | 805 | 810 | 785 | -5 | 5 | 20 | 20 |
8 | 850 | 780 | 785 | 70 | 70 | 65 | 65 |
9 | 840 | 805 | 830 | 35 | 35 | 10 | 10 |
10 | 805 | 815 | 825 | -10 | 10 | -20 | 20 |
MAD F1 | 24.5 | MAD F2 | 28.4 |
Month | Sales | Forecast 1 | Forecast 2 | Error(Seles - Forecast 1) | Absolute Error | Squared Absolute Error | Error(Seles - Forecast 2) | Absolute Error | Squared Absolute Error |
1 | 845 | 815 | 820 | 30 | 30 | 900 | 25 | 25 | 625 |
2 | 835 | 835 | 825 | 0 | 0 | 0 | 10 | 10 | 100 |
3 | 795 | 820 | 825 | -25 | 25 | 625 | -30 | 30 | 900 |
4 | 820 | 830 | 795 | -10 | 10 | 100 | 25 | 25 | 625 |
5 | 795 | 785 | 780 | 10 | 10 | 100 | 15 | 15 | 225 |
6 | 835 | 785 | 771 | 50 | 50 | 2500 | 64 | 64 | 4096 |
7 | 805 | 810 | 785 | -5 | 5 | 25 | 20 | 20 | 400 |
8 | 850 | 780 | 785 | 70 | 70 | 4900 | 65 | 65 | 4225 |
9 | 840 | 805 | 830 | 35 | 35 | 1225 | 10 | 10 | 100 |
10 | 805 | 815 | 825 | -10 | 10 | 100 | -20 | 20 | 400 |
MSE F1 | 1047.5 | MSE F2 | 1169.6 |
where the total MSE and MAD is calculated by finding the average of the whole column.
So, we have:
MAD F1 | 24.5 | MAD F2 | 28.4 |
MSE F1 | 1047.50 | MSE F2 | 1169.60 |
b. Compute MAPE for each forecast. (Round your intermediate calculations to 5 decimal places and final answers to 4 decimal places.)
Month | Sales | Forecast 1 | Forecast 2 | Error(Seles - Forecast 1) | Absolute Error | MAPE = (Absolute Error/Sales)*100 | Error(Seles - Forecast 2) | Absolute Error | MAPE = (Absolute Error/Sales)*100 |
1 | 845 | 815 | 820 | 30 | 30 | 3.55030 | 25 | 25 | 2.95858 |
2 | 835 | 835 | 825 | 0 | 0 | 0.00000 | 10 | 10 | 1.19760 |
3 | 795 | 820 | 825 | -25 | 25 | 3.14465 | -30 | 30 | 3.77358 |
4 | 820 | 830 | 795 | -10 | 10 | 1.21951 | 25 | 25 | 3.04878 |
5 | 795 | 785 | 780 | 10 | 10 | 1.25786 | 15 | 15 | 1.88679 |
6 | 835 | 785 | 771 | 50 | 50 | 5.98802 | 64 | 64 | 7.66467 |
7 | 805 | 810 | 785 | -5 | 5 | 0.62112 | 20 | 20 | 2.48447 |
8 | 850 | 780 | 785 | 70 | 70 | 8.23529 | 65 | 65 | 7.64706 |
9 | 840 | 805 | 830 | 35 | 35 | 4.16667 | 10 | 10 | 1.19048 |
10 | 805 | 815 | 825 | -10 | 10 | 1.24224 | -20 | 20 | 2.48447 |
MAPE F1 | 2.9426% | MAPE F2 | 3.4336% |
where the total MAPE is calculated by finding the average of the whole column.
So, we have:
MAPE F1 | 2.9426% | MAPE F2 | 3.4336% |
c. Prepare a naive forecast for periods 2 through 11 using the given sales data. Compute each of the following; (1) MSE, (2) MAD, (3) tracking signal at month 10, and (4) 2s control limits. (Round your answers to 2 decimal places.)
Month | Sales | Forecast | Error (Sales - Forecast) | Absolute Error | Squared Error | Absolute % Error (Absolute Error/Sales)*100 | |
1 | 845 | ||||||
2 | 835 | 845 | -10 | 10 | 100 | 1.197605 | |
3 | 795 | 835 | -40 | 40 | 1600 | 5.031447 | |
4 | 820 | 795 | 25 | 25 | 625 | 3.04878 | |
5 | 795 | 820 | -25 | 25 | 625 | 3.144654 | |
6 | 835 | 795 | 40 | 40 | 1600 | 4.790419 | |
7 | 805 | 835 | -30 | 30 | 900 | 3.726708 | |
8 | 850 | 805 | 45 | 45 | 2025 | 5.294118 | |
9 | 840 | 850 | -10 | 10 | 100 | 1.190476 | |
10 | 805 | 840 | -35 | 35 | 1225 | 4.347826 | |
Total | 8225 | 31.77203 | |||||
MAD | 28.88889 | MSE | 977.7778 |
where the total MSE and MAD is calculated by finding the average of the whole column.
So, we have:
MAD | 28.89 | MSE | 977.78 |
Month | Sales | Forecast | Error (Sales - Forecast) |
1 | 845 | ||
2 | 835 | 845 | -10 |
3 | 795 | 835 | -40 |
4 | 820 | 795 | 25 |
5 | 795 | 820 | -25 |
6 | 835 | 795 | 40 |
7 | 805 | 835 | -30 |
8 | 850 | 805 | 45 |
9 | 840 | 850 | -10 |
10 | 805 | 840 | -35 |
Total | -40 | ||
Tracking signal at month 10 | -1.38462 |
Tracking signal at month 10 = Total Error/MAD = -40/28.88889 = -1.38
Control limits = 0 ± 2*√(MSE) = 0 ± 2*√(977.78) = 0 ± 2*31.27 = 0 ± 62.54
Control limits = 0 ± 62.54