Description
A spreadsheet showing the workings for deriving the forecast model for Yash Transport.
Quarter 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
Spares Inventory 3260771 3850771 2476151 2496151 1321550 1721550 1800340 1600340 2287550 2587231 5515041 6050481 5936011 4836011 3615410 6154911 6863940 4863940 5604490 5504890 8763221 8675034 9490676 8496406 7765480 8207056
4 period center moving avg
Yt-Tt
Q1 589079
Q2 -286520.8791 -1371.2715 -966577.1642 -286520.8791 -926627.6357 192639.4038
2778558.296 2270003.165 1919374.259 1722921.578 1731695.199 1960655.214 2533202.812 3553807.9 4566133.049 5303288.168 5346931.82 5122531.722 5251576.635 5371058.934 5623185.155 5790567.674 5946725.209 6660522.042 7622681.974 8482394.596 8731616.44 8548401.597
-302407 -597824 226147.7 -245653 -597824 589079 -1371.27 1240755 68644.97 1140539 -360315 -245653 -966577 948907.5 747192.4 589079 -286521 -1636167 783851.7 1240755 -926628 -342235 -1155632 1140539 192639.4 759059.3 -51995.9
10000000 9000000 8000000 7000000
7000000 6000000 5000000 4000000 3000000 2000000 1000000 0 1 2 3 4 5 6 7 8 9 10 11
Q3 Q4 68644.97 -51995.9 68644.97 948907.5 -1636167 -342235 759059.3 -360315 747192.4 783851.7 -1155632 -51995.9
it 79801.8
New Q1 509277.2
New Q2 -366323
New Q3 -11156.8
New Q4 -131798
Seasonal Index St 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708
Sales Forecast
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Yt-St Decentralized 3211779.056 3890532.644 2521987.739 2459544.006 1272558.532 1761312.12 1846177.015 1563733.282 2238558.226 2626992.549 5560877.367 6013873.633 5887019.069 4875772.657 3661246.476 6118303.742 6814948.264 4903701.852 5650327.05 5468283.317 8714228.969 8714795.814 9536512.541 8459798.807 7716488.226 8246817.814 45836.84708
New Tt with Regression 1383437.899 1671951.085 1960464.271 2248977.457 2537490.642 2826003.828 3114517.014 3403030.2 3691543.386 3980056.571 4268569.757 4557082.943 4845596.129 5134109.314 5422622.5 5711135.686 5999648.872 6288162.058 6576675.243 6865188.429 7153701.615 7442214.801 7730727.987 8019241.172 8307754.358 8596267.544
Error 1877333 2178820 515687 247173 -1215940 -1104454 -1314177 -1802690 -1403993 -1392826 1246471 1493398 1090415 -298098 -1807213 443775 864291 -1424222 -972185 -1360298 1609519 1232819 1759948 477165 -542274 -389212 9230
Forecast
SUMMARY OUTPUT Regression Statistics Multiple R 0.862587 R Square 0.744057 Adjusted R Square 0.733392 Standard Error 1320921 Observations 26 ANOVA df Regression Residual Total SS MS 1 1.22E+14 1.22E+14 24 4.19E+13 1.74E+12 25 1.64E+14 F Significance F 69.7708 1.46E-08
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%Lower 95.0% Intercept 1094925 533424.6 2.052633 0.051166 -6009.45 2195859 -6009.45 X Variable 1 288513.2 34540.52 8.352892 1.46E-08 217225.1 359801.3 217225.1
RESIDUAL OUTPUT ObservationPredicted Y 1 1383438 2 1671951 3 1960464 4 2248977 5 2537491 6 2826004 7 3114517 8 3403030 9 3691543 10 3980057 11 4268570 12 4557083 13 4845596 14 5134109 15 5422623 16 5711136 17 5999649 18 6288162 19 6576675 20 6865188 21 7153702 Residuals 1828341 2218582 561523.5 210566.5 -1264932 -1064692 -1268340 -1839297 -1452985 -1353064 1292308 1456791 1041423 -258337 -1761376 407168.1 815299.4 -1384460 -926348 -1396905 1560527
22 23 24 25 26
7442215 1272581 7730728 1805785 8019241 440557.6 8307754 -591266 8596268 -349450
Upper 95.0% 2195859 359801.3
Lower 95.0% Upper 95.0% 219.0009 288.4118 -0.80564 3.376193
doc_393450976.xlsx
A spreadsheet showing the workings for deriving the forecast model for Yash Transport.
Quarter 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
Spares Inventory 3260771 3850771 2476151 2496151 1321550 1721550 1800340 1600340 2287550 2587231 5515041 6050481 5936011 4836011 3615410 6154911 6863940 4863940 5604490 5504890 8763221 8675034 9490676 8496406 7765480 8207056
4 period center moving avg
Yt-Tt
Q1 589079
Q2 -286520.8791 -1371.2715 -966577.1642 -286520.8791 -926627.6357 192639.4038
2778558.296 2270003.165 1919374.259 1722921.578 1731695.199 1960655.214 2533202.812 3553807.9 4566133.049 5303288.168 5346931.82 5122531.722 5251576.635 5371058.934 5623185.155 5790567.674 5946725.209 6660522.042 7622681.974 8482394.596 8731616.44 8548401.597
-302407 -597824 226147.7 -245653 -597824 589079 -1371.27 1240755 68644.97 1140539 -360315 -245653 -966577 948907.5 747192.4 589079 -286521 -1636167 783851.7 1240755 -926628 -342235 -1155632 1140539 192639.4 759059.3 -51995.9
10000000 9000000 8000000 7000000
7000000 6000000 5000000 4000000 3000000 2000000 1000000 0 1 2 3 4 5 6 7 8 9 10 11
Q3 Q4 68644.97 -51995.9 68644.97 948907.5 -1636167 -342235 759059.3 -360315 747192.4 783851.7 -1155632 -51995.9
it 79801.8
New Q1 509277.2
New Q2 -366323
New Q3 -11156.8
New Q4 -131798
Seasonal Index St 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708 36606.88623 48991.77436 -39761.81352 -45836.84708
Sales Forecast
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Yt-St Decentralized 3211779.056 3890532.644 2521987.739 2459544.006 1272558.532 1761312.12 1846177.015 1563733.282 2238558.226 2626992.549 5560877.367 6013873.633 5887019.069 4875772.657 3661246.476 6118303.742 6814948.264 4903701.852 5650327.05 5468283.317 8714228.969 8714795.814 9536512.541 8459798.807 7716488.226 8246817.814 45836.84708
New Tt with Regression 1383437.899 1671951.085 1960464.271 2248977.457 2537490.642 2826003.828 3114517.014 3403030.2 3691543.386 3980056.571 4268569.757 4557082.943 4845596.129 5134109.314 5422622.5 5711135.686 5999648.872 6288162.058 6576675.243 6865188.429 7153701.615 7442214.801 7730727.987 8019241.172 8307754.358 8596267.544
Error 1877333 2178820 515687 247173 -1215940 -1104454 -1314177 -1802690 -1403993 -1392826 1246471 1493398 1090415 -298098 -1807213 443775 864291 -1424222 -972185 -1360298 1609519 1232819 1759948 477165 -542274 -389212 9230
Forecast
SUMMARY OUTPUT Regression Statistics Multiple R 0.862587 R Square 0.744057 Adjusted R Square 0.733392 Standard Error 1320921 Observations 26 ANOVA df Regression Residual Total SS MS 1 1.22E+14 1.22E+14 24 4.19E+13 1.74E+12 25 1.64E+14 F Significance F 69.7708 1.46E-08
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%Lower 95.0% Intercept 1094925 533424.6 2.052633 0.051166 -6009.45 2195859 -6009.45 X Variable 1 288513.2 34540.52 8.352892 1.46E-08 217225.1 359801.3 217225.1
RESIDUAL OUTPUT ObservationPredicted Y 1 1383438 2 1671951 3 1960464 4 2248977 5 2537491 6 2826004 7 3114517 8 3403030 9 3691543 10 3980057 11 4268570 12 4557083 13 4845596 14 5134109 15 5422623 16 5711136 17 5999649 18 6288162 19 6576675 20 6865188 21 7153702 Residuals 1828341 2218582 561523.5 210566.5 -1264932 -1064692 -1268340 -1839297 -1452985 -1353064 1292308 1456791 1041423 -258337 -1761376 407168.1 815299.4 -1384460 -926348 -1396905 1560527
22 23 24 25 26
7442215 1272581 7730728 1805785 8019241 440557.6 8307754 -591266 8596268 -349450
Upper 95.0% 2195859 359801.3
Lower 95.0% Upper 95.0% 219.0009 288.4118 -0.80564 3.376193
doc_393450976.xlsx