Description
A spreadsheet showing the workings to come up with the forecasting model for Kingfisher.
Sales 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 March June Sept Dec March June Sept Dec March June Sept Dec March June Sept Dec March June Sept Dec
Quarterly data 838 397 358 474 437 503 445 567 557 1397 1322 1447 1101 1345 1567 1143 1093 1245 1545 1356
Average
Raw Seasonal Index
Median
Avg
467 430 454 476 503 630 851 1071 1249 1310 1334 1327 1288 1275 1259 1283
-109 44 -17 27 -58 -63 -294 326 73 137 -233 18 279 -132 -166 -38
8 -9 -200 22
-45
52 36 -155 67 52 36 -155 67 52 36 -155 67 52 36 -155 67
33 39
1800
1600
1400
1200
1000
800
600
400
200
200
0 1 2 3 4 5
Deseason Seasonalised Regression data al data regression data
Error
306 438 592 436 393 531 712 1330 1270 1411 1256 1278 1515 1107 1248 1178
376.7720588 451.5441176 526.3161765 601.0882353 675.8602941 750.6323529 825.4044118 900.1764706 974.9485294 1049.720588 1124.492647 1199.264706 1274.036765 1348.808824 1423.580882 1498.352941
429 487 371 668 728 786 670 967 1027 1085 969 1266 1326 1384 1269 1565
-71 -13 66 -165 -283 -219 -113 430 295 362 132 79 241 -241 -176 -320 0
Gross Revenue Forecast
6
7
8
9
10
11
12
13
14
15
16
SUMMARY OUTPUT Regression Statistics Multiple R 0.830754 R Square 0.690152 Adjusted R Square 0.668021 Standard Error 246.8973 Observations 16 ANOVA df Regression Residual Total SS MS F Significance F 1 1900893 1900893 31.18352 6.73E-05 14 853415.5 60958.25 15 2754308
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%Lower 95.0% Intercept 152.4559 153.5464 0.992898 0.337612 -176.868 481.7802 -176.868 X Variable 1 74.77206 13.38988 5.58422 6.73E-05 46.05362 103.4905 46.05362
RESIDUAL OUTPUT ObservationPredicted Y 1 376.7721 2 451.5441 3 526.3162 4 601.0882 5 675.8603 6 750.6324 7 825.4044 Gross Revenue 8 900.1765 9 974.9485 Forecast 10 1049.721 11 1124.493 12 1199.265 13 1274.037 14 1348.809 15 1423.581 16 1498.353 Residuals -71.1783 -13.0754 65.71507 -165.182 -283.267 -219.164 -113.373 429.7298 294.6452 361.7482 131.5386 78.64154 240.557 -241.34 -175.55 -320.447
Upper 95.0% 481.7802 103.4905
Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan
8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1
20 27 23 19 26 19 22 -6 24 23 20 25 31 37 31 40 20 45
SUMMARY OUTPUT Regression Statistics Multiple R 0.177819 R Square 0.03162 Adjusted R Square -0.0289 Standard Error 10.89313 Observations 18 ANOVA df Regression Residual Total SS MS F Significance F 1 61.99228 61.99228 0.522435 0.480238 16 1898.563 118.6602 17 1960.556
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%Lower 95.0% Intercept 20.99747 5.68875 3.691053 0.001979 8.937864 33.05709 8.937864 X Variable 1 0.51198 0.708332 0.722797 0.480238 -0.98962 2.013577 -0.98962
RESIDUAL OUTPUT ObservationPredicted Y Residuals 1 25.09332 -4.82943
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
25.6053 26.11728 26.62926 27.14124 21.50946 22.02144 22.53342 23.0454 23.55738 24.06936 24.58134 25.09332 25.6053 26.11728 26.62926 27.14124 21.50946
0.950258 -3.60339 -7.74037 -1.46068 -2.8289 0.28412 -28.3945 0.926826 -0.29349 -4.5138 -0.06745 5.795572 11.07526 4.563278 13.6763 -7.00235 23.46277
Upper 95.0% 33.05709 2.013577
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr
Monthly data Year Data Avg 19 21.5 17 20 21 29 23 19 17 20 21 29 23 24 33 22 25 31 28 32 39 26 44 57 33 39 27.41667
21.5 33
1800 1600 1400 1200 1000 800 600 400 200 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Series1 Series2 Series3
15
16
17
18
doc_180120244.xlsx
A spreadsheet showing the workings to come up with the forecasting model for Kingfisher.
Sales 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 March June Sept Dec March June Sept Dec March June Sept Dec March June Sept Dec March June Sept Dec
Quarterly data 838 397 358 474 437 503 445 567 557 1397 1322 1447 1101 1345 1567 1143 1093 1245 1545 1356
Average
Raw Seasonal Index
Median
Avg
467 430 454 476 503 630 851 1071 1249 1310 1334 1327 1288 1275 1259 1283
-109 44 -17 27 -58 -63 -294 326 73 137 -233 18 279 -132 -166 -38
8 -9 -200 22
-45
52 36 -155 67 52 36 -155 67 52 36 -155 67 52 36 -155 67
33 39
1800
1600
1400
1200
1000
800
600
400
200
200
0 1 2 3 4 5
Deseason Seasonalised Regression data al data regression data
Error
306 438 592 436 393 531 712 1330 1270 1411 1256 1278 1515 1107 1248 1178
376.7720588 451.5441176 526.3161765 601.0882353 675.8602941 750.6323529 825.4044118 900.1764706 974.9485294 1049.720588 1124.492647 1199.264706 1274.036765 1348.808824 1423.580882 1498.352941
429 487 371 668 728 786 670 967 1027 1085 969 1266 1326 1384 1269 1565
-71 -13 66 -165 -283 -219 -113 430 295 362 132 79 241 -241 -176 -320 0
Gross Revenue Forecast
6
7
8
9
10
11
12
13
14
15
16
SUMMARY OUTPUT Regression Statistics Multiple R 0.830754 R Square 0.690152 Adjusted R Square 0.668021 Standard Error 246.8973 Observations 16 ANOVA df Regression Residual Total SS MS F Significance F 1 1900893 1900893 31.18352 6.73E-05 14 853415.5 60958.25 15 2754308
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%Lower 95.0% Intercept 152.4559 153.5464 0.992898 0.337612 -176.868 481.7802 -176.868 X Variable 1 74.77206 13.38988 5.58422 6.73E-05 46.05362 103.4905 46.05362
RESIDUAL OUTPUT ObservationPredicted Y 1 376.7721 2 451.5441 3 526.3162 4 601.0882 5 675.8603 6 750.6324 7 825.4044 Gross Revenue 8 900.1765 9 974.9485 Forecast 10 1049.721 11 1124.493 12 1199.265 13 1274.037 14 1348.809 15 1423.581 16 1498.353 Residuals -71.1783 -13.0754 65.71507 -165.182 -283.267 -219.164 -113.373 429.7298 294.6452 361.7482 131.5386 78.64154 240.557 -241.34 -175.55 -320.447
Upper 95.0% 481.7802 103.4905
Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan
8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1
20 27 23 19 26 19 22 -6 24 23 20 25 31 37 31 40 20 45
SUMMARY OUTPUT Regression Statistics Multiple R 0.177819 R Square 0.03162 Adjusted R Square -0.0289 Standard Error 10.89313 Observations 18 ANOVA df Regression Residual Total SS MS F Significance F 1 61.99228 61.99228 0.522435 0.480238 16 1898.563 118.6602 17 1960.556
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%Lower 95.0% Intercept 20.99747 5.68875 3.691053 0.001979 8.937864 33.05709 8.937864 X Variable 1 0.51198 0.708332 0.722797 0.480238 -0.98962 2.013577 -0.98962
RESIDUAL OUTPUT ObservationPredicted Y Residuals 1 25.09332 -4.82943
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
25.6053 26.11728 26.62926 27.14124 21.50946 22.02144 22.53342 23.0454 23.55738 24.06936 24.58134 25.09332 25.6053 26.11728 26.62926 27.14124 21.50946
0.950258 -3.60339 -7.74037 -1.46068 -2.8289 0.28412 -28.3945 0.926826 -0.29349 -4.5138 -0.06745 5.795572 11.07526 4.563278 13.6763 -7.00235 23.46277
Upper 95.0% 33.05709 2.013577
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr
Monthly data Year Data Avg 19 21.5 17 20 21 29 23 19 17 20 21 29 23 24 33 22 25 31 28 32 39 26 44 57 33 39 27.41667
21.5 33
1800 1600 1400 1200 1000 800 600 400 200 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Series1 Series2 Series3
15
16
17
18
doc_180120244.xlsx