forcasting technique
DESCRIPTION
Excel sheet showing forecasting calculationsTRANSCRIPT
Sr. No. Month Demand in Units Formula
1 January 78,750
2 February 80,625
3 March 75,000
4 April 82,500
5 May 93,750
6 June 88,125
7 July 99,375
8 August 91,875
9 September 101,250
10 October 106,875
11 November 118,125
12 December 112,500
13 January 117,500 3605.8*A14 + 7062514 February 121,106 3605.8*A15 + 7062515 March 124,712 3605.8*A16 + 7062516 April 128,318 3605.8*A17 + 70625
0 2 4 6 8 10 12 140
20,000
40,000
60,000
80,000
100,000
120,000
140,000
f(x) = 3605.76923076923 x + 70625R² = 0.87896867675261
Demand in Units
Month
Dem
and
0 2 4 6 8 10 12 14 16 180
20,000
40,000
60,000
80,000
100,000
120,000
140,000
Chart Title
Month
Dem
and
Month Sales Ratio Formula Seasonal Factor Formula Month1 900 0.88 B2/1025 0.78 AVERAGE(C2,C14) 25
2 800 0.78 B3/1025 0.88 AVERAGE(C3,C15) 26
3 1000 0.98 B4/1025 0.93 AVERAGE(C4,C16) 27
4 1000 0.98 B5/1025 1.12 AVERAGE(C5,C17) 28
5 1400 1.37 1.27 29
6 1500 1.46 1.51 30
7 1500 1.46 1.37 31
8 1400 1.37 1.22 32
9 800 0.78 0.93 33
10 600 0.59 0.73 34
11 300 0.29 0.49 35
12 1000 0.98 0.78 36
13 700 0.68
14 1000 0.98
15 900 0.88
16 1300 1.27
17 1200 1.17
18 1600 1.56
19 1300 1.27
20 1100 1.07
21 1100 1.07
22 900 0.88
23 700 0.68
24 600 0.59Average 1025 24.00
0 5 10 15 20 25 300
200
400
600
800
1000
1200
1400
1600
1800
Sales
Seasonal Factor Forecast Formula0.78 800 J2*10250.88 900 J3*10250.93 950 J4*10251.12 1150 J5*10251.27 13001.51 15501.37 14001.22 12500.93 9500.73 7500.49 5000.78 800
Month Sales Fcst Formula De-Trended Demand Formula Seasonal Demand1 591 495 6.1104*A2 + 488.54 1.19 B2/C2 1.17
2 550 501 6.1104*A3 + 488.54 1.10 B3/C3 1.10
3 502 507 6.1104*A4 + 488.54 0.99 B4/C4 1.01
4 469 513 0.91 0.93
5 462 519 0.89 0.90
6 461 525 0.88 0.89
7 465 531 0.88 0.88
8 472 537 0.88 0.89
9 495 544 0.91 0.91
10 581 550 1.06 1.03
11 644 556 1.16 1.13
12 662 562 1.18 1.16
13 656 568 1.15
14 632 574 1.10
15 592 580 1.02
16 557 586 0.95
17 543 592 0.92
18 537 599 0.90
19 540 605 0.89
20 548 611 0.90
21 563 617 0.91
22 626 623 1.00
23 691 629 1.10
24 719 635 1.13Average 564.92 1.00
0 5 10 15 20 25 300
100
200
300
400
500
600
700
800
f(x) = 6.1104347826087 x + 488.536231884058R² = 0.320366480226555
Sales
Month Trend Formula Seasonal Factor Frcst Formula25 641 6.1104*J2+ 488.54 1.17 753 K2*M226 647 6.1104*J3+ 488.54 1.10 712 K3*M327 654 6.1104*J4+ 488.54 1.01 657 K4*M428 660 0.93 61529 666 0.90 60130 672 0.89 59631 678 0.88 59932 684 0.89 60733 690 0.91 62934 696 1.03 71835 702 1.13 79336 709 1.16 818