tata steels sales forecast

8/14/2019 TATA Steels Sales Forecast

1/29

TATA Steels Limited

Sales Forecast ReportProduct : Charge Chrome

Submitted To: Submitted by:

Mrs. Asmita Chitnis Ashamdeep Singh Cheema ###

Harshleen Kaur Sawhne ###

Vishal Singla ###Abhisake Sharma ###

Arpit Sihra ###

Bidrupa Sinha ###

Sonam ###


2/29


3/29

Main Data Sheet : Data of Charge Chrome (A product of TATA steels) ta

S No. Quarter Unit Expressi Quarterly Sales (Rs. CroProduct Percentage

1 Mar-03 Tata Steel Ltd. 3113.6 Charge Chrom 2.142 Jun-03 Tata Steel Ltd. 3146.2 Charge Chrom 2.26

3 Sep-03 Tata Steel Ltd. 2941.5 Charge Chrom 2.26

4 Dec-03 Tata Steel Ltd. 2632.03 Charge Chrom 2.26

5 Mar-04 Tata Steel Ltd. 3200.27 Charge Chrom 2.26

6 Jun-04 Tata Steel Ltd. 3164.65 Charge Chrom 2.78






12 Dec-05 Tata Steel Ltd. 3698 Charge Chrom 3.21

13 Mar-06 Tata Steel Ltd. 4100.53 Charge Chrom 3.2114 Jun-06 Tata Steel Ltd. 3899.45 Charge Chrom 2.98











25 Mar-09 Tata Steel Ltd. 6539 Charge Chrom 4.4126 Jun-09 Tata Steel Ltd. 5615.55 Charge Chrom 4.66




4/29

ken from Prowess

######

###

###

###

###

###

###

###

###

###

###

######

###

###

###

###

###

###

###

###

###

###

######

###

###

Quarterly Sale ofProduct ChargeChrome(Rs. Crore)


5/29

Quarter X XY Y-Yt

Mar-03 0 66.63 0 0 37.07 29.56 873.73

Jun-03 1 71.10 71.10 1 45.55 25.55 653.06

Sep-03 2 66.48 132.96 4 54.03 12.45 155.04

Dec-03 3 59.48 178.45 9 62.5 -3.02 9.12

Mar-04 4 72.33 289.30 16 70.98 1.35 1.81

Jun-04 5 87.98 439.89 25 79.46 8.52 72.59Sep-04 6 103.93 623.56 36 87.93 15.99 255.75

Dec-04 7 103.73 726.11 49 96.41 7.32 53.56

Mar-05 8 107.44 859.50 64 104.89 2.55 6.5

Jun-05 9 114.18 1027.63 81 113.37 0.82 0.67

Sep-05 10 124.69 1246.90 100 121.84 2.85 8.11

Dec-05 11 118.71 1305.76 121 130.32 -11.61 134.88

Mar-06 12 131.63 1579.52 144 138.8 -7.17 51.4

Jun-06 13 116.20 1510.65 169 147.27 -31.07 965.34

Sep-06 14 125.23 1753.19 196 155.75 -30.52 931.63

Dec-06 15 133.19 1997.85 225 164.23 -31.04 963.34

Mar-07 16 148.42 2374.67 256 172.7 -24.29 589.88

Jun-07 17 126.77 2155.04 289 181.18 -54.41 2960.97Sep-07 18 144.53 2601.62 324 189.66 -45.12 2036.22

Dec-07 19 150.21 2854.04 361 198.14 -47.92 2296.65

Mar-08 20 173.16 3463.11 400 206.61 -33.46 1119.38

Jun-08 21 271.34 5698.22 441 215.09 56.25 3164.51

Sep-08 22 300.47 6610.41 484 223.57 76.91 5914.58

Dec-08 23 212.14 4879.19 529 232.04 -19.91 396.22

Mar-09 24 288.37 6920.88 576 240.52 47.85 2289.53

Jun-09 25 261.68 6542.12 625 249 12.69 160.95

Sep-09 26 265.25 6896.56 676 257.47 7.78 60.49

Dec-09 27 297.07 8020.87 729 265.95 31.12 968.29

378 4242.3 72759.1 6930 4242.34 27094.19MSE 967.65

Quarterly Sale ofProduct Charge

Chrome(Rs. Crore)X 2 Yt (Y-Yt)

2


6/29

0.00

50.00

100.00

150.00

200.00

250.00

300.00

350.00

Tren Ana ys s

Actual Value TrendCalculatedValue


7/29

13.5

72759.1

151.5

6930

b 8.48

a 37.07

Forecasting

Mar-10 28 274.43

Jun-10 29 282.91

Sep-10 30 291.38

Dec-10 31 299.86

X-=X/n

XYY

--

X2


8/29


9/29

S No.Quarter

1 Mar-03 ###

2 Jun-03 ###

263.7 65.92

3 Sep-03 ### 66.64

269.4 67.354 Dec-03 ### 69.46

286.3 71.57

5 Mar-04 ### 76.25

323.7 80.93

6 Jun-04 ### 86.46

368.0 91.99

7 Sep-04 ### 96.38

403.1 100.77

8 Dec-04 ### 104.04

429.3 107.32

9 Mar-05 ### 109.91

450.0 112.5110 Jun-05 ### 114.38

465.0 116.25

11 Sep-05 ### 119.28

489.2 122.30

12 Dec-05 ### 122.55

491.2 122.81

13 Mar-06 ### 122.87

491.8 122.94

14 Jun-06 ### 124.75

506.2 126.56

15 Sep-06 ### 128.66

523.0130.76

16 Dec-06 ### 132.08

533.6 133.40

17 Mar-07 ### 135.81

552.9 138.23

18 Jun-07 ### 140.35

569.9 142.48

19 Sep-07 ### 145.57

594.7 148.67

20 Dec-07 ### 166.74

739.2 184.81

21 Mar-08 ### 204.30

895.2223.80

22 Jun-08 ### 231.54

957.1 239.28

23 Sep-08 ### 253.68

1072.3 268.08

24 Dec-08 ### 266.87

1062.7 265.67

25 Mar-09 ### 261.26

1027.4 256.86

26 Jun-09 ### 267.48

1112.4 278.09

Quarterly Sale ofCharge Chrome(Rs.

Crore)

4 Quarter MovingTotal

4 Quarter MovingAverage

4 Quarter centredMoving Average


10/29

27 Sep-09 ###

28 Dec-09 ###

29 Mar-10

Year Quarter 1 Quarter 2 Quarter 3

2003 1.00

2004 1.05 0.98 0.93

2005 1.02 1.00 0.96

2006 0.93 1.07 1.03

2007 0.92 1.11 1.01

2008 1.18 0.85 0.84

2009 0.91 1.02

0.98 1.02 0.97

Quarter Unadjusted Indice Adjusted Constant Seasonal Index

Quarter 1 0.98 0.987 0.9686

Quarter 2 1.02 0.987 1.0067

Quarter 3 0.97 0.987 0.9606

Quarter 4 1.08 0.987 1.0641

S No. Quarter Seasonal Index Deseasonalised Dat

1 Mar-03 ### 0.97 64.54

2 Jun-03 ### 1.01 71.58

3 Sep-03 ### 0.96 63.86

4 Dec-03 ### 1.06 63.30 66.66

5 Mar-04 ### 0.97 70.05 66.25

6 Jun-04 ### 1.01 88.56 65.74

7 Sep-04 ### 0.96 99.83 73.97

8 Dec-04 ### 1.06 110.38 86.15

9 Mar-05 ### 0.97 104.06 99.59

10 Jun-05 ### 1.01 114.94 104.76

11 Sep-05 ### 0.96 119.78 109.8012 Dec-05 ### 1.06 126.32 112.93

13 Mar-06 ### 0.97 127.49 120.35

14 Jun-06 ### 1.01 116.98 124.53

15 Sep-06 ### 0.96 120.30 123.60

16 Dec-06 ### 1.06 141.73 121.59

17 Mar-07 ### 0.97 143.75 126.34

18 Jun-07 ### 1.01 127.61 135.26

19 Sep-07 ### 0.96 138.84 137.70

20 Dec-07 ### 1.06 159.85 136.74

21 Mar-08 ### 0.97 167.72 142.10

Average OfRemaining(Modified

Mean)

Quarterly Sale of

Charge Chrome(Rs.Crore)

3 Quarter Moving

Average


11/29

22 Jun-08 ### 1.01 273.15 155.47

23 Sep-08 ### 0.96 288.64 200.24

24 Dec-08 ### 1.06 225.74 243.17

25 Mar-09 ### 0.97 279.31 262.51

26 Jun-09 ### 1.01 263.43 264.56

27 Sep-09 ### 0.96 254.81 256.16

28 Dec-09 ### 1.06 316.12 265.85

29 Mar-10 278.12

MSE

Forecasted sales With Seasonal Variation

Quarter 4 Qtr Moving Avg.Mar-10 278.12 278.42 286.90

Jun-10 289.05 289.36 298.18

Sep-10 275.83 276.13 284.54

Dec-10 305.55 305.88 315.20

3 Qtr. Moving 3 Qtr Wt. moving


12/29

1.00

1.17

1.05

0.98

0.93

1.00

1.02

1.00

0.96

1.03

0.93

1.07

1.03

0.99

0.92

1.11

1.01

1.11

1.18

0.85

0.84

1.26

0.91

1.02

%age of actualto moving

average values


13/29

Quarter 4

1.17

1.00

1.03

0.99

1.11

1.26

1.08 4.05

-3.36 11.29 66.55

3.81 14.50 65.82 4.24 17.94 64.87

22.83 521.03 67.20 21.37 456.51 66.77

25.86 668.80 71.44 28.39 805.95 78.18

24.23 587.19 80.44 29.94 896.70 91.11

4.47 19.97 92.21 11.85 140.51 103.23

10.18 103.70 100.71 14.23 202.56 105.46

9.98 99.67 107.31 12.47 155.60 110.5613.39 179.32 112.29 14.03 196.76 115.55

7.15 51.05 116.28 11.22 125.81 122.24

-7.55 57.03 122.13 -5.16 26.57 125.82

-3.30 10.89 122.64 -2.35 5.50 122.04

20.14 405.75 122.77 18.96 359.51 120.39

17.42 303.44 126.62 17.13 293.44 130.46

-7.65 58.51 130.69 -3.08 9.48 139.17

1.14 1.30 133.35 5.49 30.18 135.35

23.11 534.07 137.99 21.86 477.90 135.92

25.62 656.19 142.51 25.20 635.17 147.47

Variation from 3 Q

Moving AverageStandard 2 Error

4 Quarter

Moving Average

Variation from 4

Q MovingAverageStandard 2 Error

3 Quarters

Weighted movingaverage


14/29

117.68 13849.59 148.50 124.65 15537.20 160.28

88.40 7814.75 184.89 103.75 10764.10 219.12

-17.43 303.64 222.34 3.41 11.60 263.32

16.80 282.21 238.81 40.50 1640.10 254.61

-1.14 1.29 266.71 -3.28 10.78 263.01

-1.36 1.84 264.28 -9.48 89.78 262.44

50.27 2527.39 255.82 60.30 3636.00 261.76

278.42 286.90

1162.58 1521.90

4 Qtr Wt. Avg.283.51

294.65

281.17

311.47


15/29


16/29

-3.25 10.54

5.19 26.92 65.25 4.81 23.11

21.79 474.97 66.94 21.62 467.54

21.65 468.74 75.49 24.35 592.73

19.27 371.29 86.84 23.54 554.10

0.83 0.69 98.82 5.24 27.47

9.48 89.84 103.56 11.38 129.50

9.22 85.07 109.26 10.52 110.7410.77 116.02 114.25 12.07 145.77

5.25 27.55 119.86 7.64 58.31

-8.84 78.10 124.34 -7.36 54.24

-1.74 3.04 122.28 -1.98 3.94

21.34 455.50 121.34 20.39 415.74

13.29 176.72 128.93 14.83 219.88

-11.56 133.61 135.78 -8.17 66.69

3.50 12.22 134.55 4.29 18.44

23.93 572.59 136.74 23.10 533.69

20.24 409.81 145.49 22.23 494.05

Variationfrom 3 Q Wt.

MovingAverage Standard 2 Error

4 Quarter

WeightedAverage

Variationfrom 4 Q

Wt. MovingAverage Standard 2 Error


17/29

112.87 12740.07 155.57 117.58 13825.64

69.52 4832.58 205.43 83.21 6923.92

-37.58 1412.20 246.93 -21.19 448.82

24.70 610.12 248.29 31.02 962.22

0.42 0.18 264.49 -1.06 1.13

-7.64 58.32 263.18 -8.37 70.09

54.36 2954.72 259.39 56.73 3218.76

283.51

1044.86 1223.60


18/29

= 0.2

S No. Quarter

1 Mar-03 66.63 68.86 -2.23 4.97 68.86

2 Jun-03 71.10 68.41 2.69 7.24 67.75

3 Sep-03 66.48 68.95 -2.47 6.12 69.424 Dec-03 59.48 68.46 -8.97 80.52 67.95

5 Mar-04 72.33 66.66 5.66 32.07 63.72

6 Jun-04 87.98 67.80 20.18 407.31 68.02

7 Sep-04 103.93 71.83 32.09 1030.07 78.00

8 Dec-04 103.73 78.25 25.48 649.19 90.96

9 Mar-05 107.44 83.35 24.09 580.37 97.35

10 Jun-05 114.18 88.16 26.02 676.88 102.39

11 Sep-05 124.69 93.37 31.32 981.06 108.29

12 Dec-05 118.71 99.63 19.07 363.79 116.49

13 Mar-06 131.63 103.45 28.18 794.11 117.60

14 Jun-06 116.20 109.08 7.12 50.70 124.61

15 Sep-06 125.23 110.51 14.72 216.70 120.4116 Dec-06 133.19 113.45 19.74 389.61 122.82

17 Mar-07 148.42 117.40 31.02 962.12 128.00

18 Jun-07 126.77 123.60 3.16 10.01 138.21

19 Sep-07 144.53 124.24 20.30 412.04 132.49

20 Dec-07 150.21 128.30 21.92 480.36 138.51

21 Mar-08 173.16 132.68 40.48 1638.38 144.36

22 Jun-08 271.34 140.77 130.57 17048.45 158.76

23 Sep-08 300.47 166.89 133.59 17844.99 215.05

24 Dec-08 212.14 193.61 18.53 343.49 257.76

25 Mar-09 288.37 197.31 91.06 8291.59 234.95

26 Jun-09 261.68 215.52 46.16 2130.86 261.66

27 Sep-09 265.25 224.76 40.50 1639.98 261.6728 Dec-09 297.07 232.85 64.21 4123.49 263.46

29 Mar-10 245.70 280.27

Sum Of Errors 884.19 61196.48

Mean Of Errors 31.58 2185.59

Quarterly Sale ofProduct ChargeChrome(Rs. Crore)

ForecastedSales (= 0.2)

ForecastError(ActualSales-ForecastedSales)

Standard 2

ErrorForecastedSales (= 0.5)

250.00

300.00

350.00

Exponential Smoothening


19/29

0.00

50.00

100.00

150.00

200.00

Quarterly Sale of ProductCharge Chrome(Rs. Crore)

Forecasted Sales (= 0.2) Forecaste


20/29

= 0.5 = 0.8

-2.23 4.97 68.86 -2.23 4.97

3.36 11.28 67.08 4.03 16.22

-2.95 8.68 70.30 -3.82 14.60-8.47 71.70 67.24 -7.76 60.19

8.61 74.11 61.04 11.29 127.48

19.96 398.22 70.07 17.91 320.74

25.93 672.20 84.40 19.53 381.46

12.77 162.99 100.02 3.71 13.76

10.09 101.83 102.99 4.45 19.80

11.79 139.00 106.55 7.63 58.28

16.40 269.06 112.65 12.04 144.84

2.22 4.92 122.28 -3.58 12.80

14.03 196.84 119.42 12.21 148.98

-8.41 70.70 129.19 -12.98 168.54

4.82 23.23 118.80 6.43 41.3210.37 107.58 123.94 9.25 85.52

20.41 416.70 131.34 17.08 291.61

-11.44 130.96 145.00 -18.23 332.51

12.05 145.09 130.41 14.12 199.38

11.70 136.91 141.71 8.50 72.29

28.79 829.08 148.51 24.64 607.31

112.59 12675.38 168.23 103.12 10633.09

85.42 7296.90 250.72 49.75 2475.34

-45.62 2081.51 290.52 -78.38 6144.04

53.42 2853.64 227.82 60.55 3666.84

0.02 0.00 276.26 -14.57 212.41

3.58 12.82 264.60 0.65 0.4333.61 1129.43 265.12 31.95 1020.65

290.68

422.81 30025.73 277.27 27275.40

15.10 1072.35 9.90 974.12


Standard 2

ErrorForecastedSales (= 0.8)


Standard 2

Error


21/29

d Sales (= 0.5) Forecasted Sales (= 0.8)


22/29

S No. Quarter Forecast Error Squared Error

1 Mar-03 ##

2 Jun-03 ##

3 Sep-03 ##

4 Dec-03 ## 68.07 -8.59 73.74

5 Mar-04 ## 65.69 6.64 44.066 Jun-04 ## 66.10 21.88 478.79

7 Sep-04 ## 73.26 30.66 940.28

8 Dec-04 ## 88.08 15.65 245.02

9 Mar-05 ## 98.54 8.89 79.08

10 Jun-05 ## 105.03 9.15 83.73

11 Sep-05 ## 108.45 16.24 263.75

12 Dec-05 ## 115.44 3.27 10.69

13 Mar-06 ## 119.19 12.43 154.62

14 Jun-06 ## 125.01 -8.80 77.51

15 Sep-06 ## 122.18 3.05 9.30

16 Dec-06 ## 124.35 8.84 78.09

17 Mar-07 ## 124.87 23.54 554.2918 Jun-07 ## 135.61 -8.84 78.23

19 Sep-07 ## 136.12 8.41 70.72

20 Dec-07 ## 139.91 10.31 106.22

21 Mar-08 ## 140.50 32.65 1066.10

22 Jun-08 ## 155.97 115.38 13311.71

23 Sep-08 ## 198.24 102.24 10452.18

24 Dec-08 ## 248.32 -36.19 1309.39

25 Mar-09 ## 261.32 27.05 731.78

26 Jun-09 ## 266.99 -5.31 28.19

27 Sep-09 ## 254.06 11.19 125.17

28 Dec-09 ## 271.77 25.30 640.11

29 Mar-10 274.67

MSE 1240.51

Quarterly Sale ofCharge

Chrome(Rs. Crore)3 Quarter MovingAverage

0.0

50.0

100.0

150.0

200.0

250.0

300.0

350.0

Actual V/S 3 Quarter Weighted Moving Averag

Actual Sales Forecasted Sales


23/29

0.0

50.0

100.0

150.0

200.0

250.0

300.0

350.0

Actual V/S 4 Quarter Weighted Moving Averag

Actual Sales Forecasted Sales


24/29

Forecast Error Squared Error Forecast Error

68.05 -8.56

65.92 6.40 40.98 63.75 8.5767.35 20.63 425.57 67.07 20.91

71.57 32.36 1047.18 78.01 25.92

80.93 22.80 519.91 93.34 10.39

91.99 15.45 238.62 101.17 6.27

100.77 13.41 179.93 105.62 8.57

107.32 17.37 301.75 110.19 14.50

112.51 6.20 38.39 118.31 0.39

116.25 15.37 236.34 119.95 11.68

122.30 -6.10 37.18 126.16 -9.96

122.81 2.42 5.86 121.76 3.47

122.94 10.25 105.04 123.29 9.90

126.56 21.85 477.64 127.70 20.71130.76 -3.99 15.94 139.48 -12.71

133.40 11.13 123.96 135.05 9.48

138.23 11.99 143.65 139.26 10.95

142.48 30.67 940.83 144.41 28.74

148.67 122.68 15049.52 160.74 110.61

184.81 115.66 13377.62 218.43 82.05

223.80 -11.66 135.90 269.54 -57.41

239.28 49.09 2410.03 251.45 36.92

268.08 -6.40 40.92 264.98 -3.29

265.67 -0.41 0.17 262.32 2.93

256.86 40.21 1616.69 267.92 29.15

278.09 280.57

1562.90

4 Quarter MovingAverage

3 QuartersWeighted movingaverage

e

200.0

250.0

300.0

350.0

Actual


25/29

0.0

50.0

100.0

150.0


26/29

Squared Error

73.30

73.52 64.62 7.71 59.37437.09 67.18 20.80 432.46

671.59 75.43 28.49 811.86

107.88 88.38 15.35 235.70

39.28 97.50 9.94 98.79

73.36 103.68 10.50 110.35

210.20 109.04 15.65 244.84

0.16 115.99 2.71 7.37

136.44 118.47 13.16 173.12

99.20 124.62 -8.42 70.81

12.01 122.18 3.05 9.29

98.08 123.15 10.04 100.84

429.00 127.25 21.17 448.14161.53 135.99 -9.22 85.06

89.87 134.39 10.14 102.85

119.98 138.85 11.37 129.19

826.19 143.64 29.52 871.15

12233.72 155.91 115.43 13325.07

6731.76 204.98 95.49 9118.92

3295.35 251.24 -39.11 1529.29

1363.01 246.58 41.79 1746.25

10.84 266.22 -4.53 20.56

8.59 263.66 1.59 2.54

849.92 263.49 33.58 1127.30

279.58

1126.07 1285.88

4 Quarter WeightedAverage

ForecastError

SquaredError

0.0

50.0

100.0

150.0

200.0

250.0

300.0

350.0

Ac

0.0

50.0

100.0

150.0

200.0

250.0

300.0

350.0

Ac

Ac

/S Forecasted sales


27/29

Quarterly Sale of Charge Chrome(Rs.Crore)

3 Quarter Moving Average 4 Quarter

3 Quarters Weighted moving average 4 Quarter Weighted Average


28/29

ual V/S 3 Quarter Moving Average

Actual Sales 3 Quarter MovingAverage

ual V/S 4 Quarter Movi

tual Sales 4 QuarterMovingAverage


29/29

Moving Average

tata steels sales forecast

Documents