Next

Next Page
Page

Equity Valuation
of

TCSLtd Company
Prepared by :- Syed

Ibrahim

Email: - syedibrahimhuffaz@gmail.com

Introduction Sheet

Balance Sheet

Share Price of DDM

pointer
end
Start
Red
Yello
Green
End

25
3800

150
0

50
0

10 0
0

2,261

0
1000
2500
1500
5000

Income Statement

25000
20000
15000
10000

3800

2740

1370

VALUATED SHARE
PRICE

4893 4306

3914

5000

Summary Of the Co

TCS Ltd Company

Company Overview
(Rs. In Crores , except per share data)

Company Information
Industry

Computers & Softwares

Sector

Software-Top

Country

India

BSE/NSE Code

532540 /TCS

Market Index

BSE - 500

Year end

31-Mar

Reporting currency

Rs.

ISIN: INE467B01029

Total

Profit

10061
84788
71094

22404

23043

29275

38858

48426

59070

10061
84788
71094

22404

23043

29275

38858

48426

59070

year
GDP Growth
Industry Growth
company Growth

Year
2009
2010
2011
2012
2013
2014

9
5.04%
1.00%
1.5%

Total Profit
4,696.21
5,618.51
7,569.99
10,975.98
12,786.34
15,049.61

10
11.23%
0.08%
2.86%

Net Cash
479.93
3332.30
4806.67
9107.72
11202.32
12915.69

11
7.75%
14.78%
27.04%

Sales
22,403.65
23,043.07
29,274.54
38,858.28
48,426.14
59,069.55

2015
2016
2017
2018
2019
2020
2021
2022
2033
2024
Ratio Button

18,113.13
21,602.22
25,634.75
30,292.35
35,059.06
39,722.43
44,039.31
47,753.51
50,618.72
53,655.84
3

14031.27
15779.90
18129.06
21178.51
26293.32
33962.00
44632.41
58659.50
76251.95
94899.95

71,093.83
84,788.45
100,616.10
118,897.11
137,606.41
155,910.05
172,853.78
187,431.95
198,677.87
210,598.54

ncome Statement

Calculation Sheet

Cash Flows

Assumption Sheet

Dash Board
TATA CONSULTING SERVICES

Equity Dividend

20746

14

25%
20%

19132

15%

15231

9385
4893 4306

23310

17257

13160
6538

21990

10%

11137
7869

5%

2740

0%

10

11

12

13

14

15

16

17

ary Of the Company

Company Description

they are a leader in the global marketplace and among the top 10 technology firms in the
continued rapid growth is a testament to the certainty our clients experience every day. B
than 40 years of experience, we add real value to global organizations through domain ex
solutions with proven success in the field and world-class service. Its how we keep you m
Products & Services

1.Application Development & Maintenance, 2.Corporate Performance Management, 3.Enterprise Q

Services,4.Infrastructure Services ,5.Packages Application Services ,6.Product Engineering ,7.Syst

Profit

Net

Cash

210599

Sales

198678
187432
172854
155910
137606
118897
100616
84788

118897
100616
84788

10

11

12

13

14

15

16

12
3.99%
20.59%
32.73%

Sales
22403.65
23043.07
29274.54
38858.28
48426.14
59069.545

13
5.68%
9.79%
24.62%

14
6.23%
13.56%
21.98%

15
6.63%
12.59%
20.36%

16
6.86%
12.03%
19.26%

17
6.92%
11.88%
18.67%

18
6.97%
11.78%
18.17%

71093.832
84788.447
100616.1
118897.11
137606.41
155910.05
172853.78
187432
198677.87
210598.54

Competators

Cost of Equity

Beta Valuation

Assumption Sheet

VICES
25%

GDP Growth

20%

Industry Growth

40%

Industry Growth

30%

15%

20%

10%

10%

5%

0%
1

0%
1

10

Stock Data
Price as on 15nd Nov 2013
DCF Based Valuated Price
52 Week High

p 10 technology firms in the world. Our

ents experience every day. Building on more
nizations through domain expertise plus
vice. Its how we keep you moving forward.

52 Week Low
YTD Change
YTD Change in %
Beta Vs Sensex
Cost of Capital

nce Management, 3.Enterprise Quality

es ,6.Product Engineering ,7.Systems Integration.

Researcher Report

210599

78

1. The divedend of
the company is
increasing Yearly
2.The Profit & Sales
also increasing

The Calculated Sh
of the Company i
Valued so the R
Suggest to

Buy
Stock

increasing Yearly
2.The Profit & Sales
also increasing

16

Suggest to

Buy
Stock

Competators

DCF Valuation

Industry Growth

company Growth

2,025.75
DDM

FCFF

2,261

3,795.15

2090.85
1255.85

0.162412263

8.88%

Report

The Calculated Share Price

of the Company is Under
Valued so the Report
Suggest to

Buy
Stock

for the

10

Suggest to

Buy
Stock

for the

1
s.no
1-

HOME

Balance Sheet

Income Statement

Cash Flows

2009
(in Crore's)

2010

2011

97.86

195.72

195.72

100

100

100

13248.39

14820.9

19,283.77

13446.25

15116.62

19579.49

32.63

29.25

35.87

7.74

6.49

5.25

40.37

35.74

41.12

3604.18

3926.61

2490.11

1450.23

3352.74

3932.39

5054.41
18,541.03

7279.35
22,431.71

6422.5
26,043.11

4359.24
1690.16
2669.08
685.13
3354.21

4871.21

6,030.16

2110.69

2,607.98

2760.52

3422.18

940.72

1,345.37

3701.24

4767.55

5936.03

7893.39

5795.49

9290.24

11594.63

10563.04

3717.73
479.93
16.95
3910.85
1125.33

6.78

5.37

3332.3

4,806.67

212.31

224.77

4101.84

5,063.51

3183.85

5,379.75

SOURCES OF FUNDS
A

2A

Equity & Liabilities

share holders funds
(a)Equity share
(b)Preference shares
(c.) Reserves and surplus
Total Share Holders Funds
Non current Liabilities
(a)Secured Loans
(b)Unsecured Loans
Total Non current Liabilities
Current Liabilities
(a)Current Liabilities
(b)Provisions
Total Current Liabilities
TOTAL LIABILITIES
APPLICATIONS OF FUNDS
ASSETS
Non Current Assets
(a)Fixed Assets
(i)Gross block
(ii)Less Depreciation
(iii)Net block
(b)Capital working progress
Total Fixed Assets
(c)Investments
Total Non Current Assets
Current Assets
(a)Sundry debtors
(b)Cash and Bank Balance
(c)Inventories
(d)Loans & Advances
(e)Fixed Deposits

Total Current Assets

Miscellaneous Expences
TOTAL ASSETS
Check sum
Working Capital
Net Cash

9250.79
0
18,541.03
B/s Tally

10837.08
0
22,431.71
B/s Tally

15480.07
0
26,043.11
B/s Tally

4196.38

3557.73

9057.57

479.93

3332.30

4806.67

Cash Flows

Calculation Sheet
2012

2013

Assumption Sheet
2014

T A T A

2015

Beta Valuation
2016

2017

C O N S U L T A N S Y S E R V I C E S Ltd

195.72

195.72

195.72

195.72

195.72

195.72

100

100

100

100

100

100

24,560.91

32,266.53

37,948.38

45,733.40

54,599.87

64,847.35

24856.63

32562.25 38244.0967 46029.1234 54895.5906 65143.0742

93.47

161.6

NA

NA

NA

NA

2.76

1.52

NA

NA

NA

NA

96.23

163.12

4544.52

4165.66

NA

NA

NA

NA

4761.43

6121.11

NA

NA

NA

NA

9305.95
34,258.81

125.26

150.76

179.80

213.36

10286.77 14328.4098 17245.1228 20567.0048 24406.2939

43,012.14
52,697.77
63,425.00
75,642.39
89,762.73

7,282.02

9,183.36

NA

NA

NA

NA

3,218.40

4,079.08

NA

NA

NA

NA

4063.62

5104.28

NA

NA

NA

NA

1,726.88

1,763.85

NA

NA

NA

NA

5790.5

6868.13 9026.28868 10863.6938 12956.3381

15374.927

5688.39

6324.38

9151.01

11013.81

13135.37

15587.38

11478.89

13192.51

16092.04

19367.76

23098.53

27410.38

4.14

6.34

9,107.72

11,202.32

203.18

NA

NA

NA

NA

12,915.69

14,031.27

15,779.90

18,129.06

4,054.16

NA

NA

NA

NA

7,877.86

14,556.81

NA

NA

NA

NA

5,587.02

NA

NA

NA

NA

22779.92
0
34,258.81
B/s Tally

29819.63
0
43,012.14
B/s Tally

36,605.72
0
52,697.77
B/s Tally

44,057.24
0
63,425.00
B/s Tally

52,543.87
0
75,642.39
B/s Tally

62,352.35
0
89,762.73
B/s Tally

13473.97

19532.86

22277.31

26812.12

31976.86

37946.06

9107.72

11202.32

12915.69

14031.27

15779.90

18129.06

Beta Valuation

Cost of Equity

DCF Valuation

2018

2019

2020

2021

2022

195.72

195.72

195.72

195.72

195.72

100

100

100

100

100

76,683.25

88,796.09

100,646.59

111,616.63

121,055.11

76978.96956 89091.81185 100942.311 111912.3491

121350.83

I C E S Ltd

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

252.13

291.80

330.61

366.54

397.46

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

28840.69227 33378.85066 37818.7205 41928.71948 45464.91029

106,071.79
122,762.46
139,091.65
154,207.61
167,213.20

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

18168.4093 21027.33625 23824.2761 26413.41046 28641.06874

18419.46

21317.89

24153.48

26778.39

29036.83

32390.59

37487.48

42473.86

47089.76

51061.22

NA

NA

NA

NA

NA

21,178.51

26,293.32

33,962.00

44,632.41

58,659.50

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

NA

73,681.20
0
106,071.79
B/s Tally

85,274.98
0
122,762.46
B/s Tally

96,617.79
0
139,091.65
B/s Tally

107,117.85
0
154,207.61
B/s Tally

116,151.98
0
167,213.20
B/s Tally

44840.51

51896.13

58799.07

65189.14

70687.07

21178.51

26293.32

33962.00

44632.41

58659.50

2023

2024

195.72

195.72

100

100

128,336.16

136,054.07

128631.8798 136349.7926

NA

NA

NA

NA

421.31

446.58

NA

NA

NA

NA

48192.8049
177,245.99

51084.3732
187,880.75

NA

NA

NA

NA

NA

NA

NA

NA

30359.53286 32181.10484
30779.04

32625.78

54124.90

57372.39

NA

NA

76,251.95

94,899.95

NA

NA

NA

NA

NA

NA

123,121.10
0
177,245.99
B/s Tally

130,508.36
0
187,880.75
B/s Tally

74928.29

79423.99

76251.95

94899.95

HOME
S.no

Profit & Loss Account

Balance Sheet
2009

Cash Flows
2010

2011

22404
1.73
2.08
22,403.65

23045
-1.38
0.39
23,043.07

29276
-0.87
0.27
29,274.54

54
164
7370
6948
1218
629
16,382.82
6,020.83
-456
5565
7
417
-103
5037
340
4,696.21

24
184
7882
6447
1268
571
16,375.90
6,667.17
182
6849
10
469
-14
6356
738
5,618.51

18
240
10190
8136
1195
724
20,502.72
8,771.82
486
9258
20
538
0
8700
1130
7,569.99

(In Crore's)
1

Revenue from operations (Sales)

Less: Sales Return

Less: Excise duty
Revenue from operations (net)
2 Expenses
(a) Cost of materials consumed
(b) power and fuel
(d) Employee benefits expense
(e) Other Manufacturing Exp
(f)selling & Adm exp
(g)Miscellaneous Exp
Total Expenses
3 OPETATING PROFIT (1-2)
4 Other income
5 EBITDA (1-2)
6 Interest (finance cost)
7 depreciation
8 Extraordinary items
9 EBT
10 Tax expense:
Reported Net profit

Earning before Appropriation

16,329.15 16,352.15 20,484.97

Preferred Dividend
Equity Dividend
Dividend Distribution Tax

7
1370
234

17
3914
658

11
2740
451

Carried Forward to B/S

14,718

11,763

17,283

9786

19572

19572

48

29

39

Face Value per share

Shares in issue
Dividend/Share
EPS
Market Price
Market Capitalization
Dividend Growth Rate

ash Flows

Assumption Sheet
2012

2013

Beta Valuation
2014

2015

T A T A

Cost of Equity
2016

2017

C O N S U L T A N S Y S E R V I C E S Ltd

38859
-0.26
0.25
38,858.28

48427
0.00
0.82
48,426.14

59071

71096

84791

100619

2
59,069.55

2
71,093.83

2
84,788.45

3
100,616.10

12
292
14100
10576
1686
806
27,472.56
11,385.72
2685
14071
16
688
-129
13237
2261
10,975.98

25
376
17082
13386
0
3252
34,119.87
14,306.27
2230
16537
31
803
0
15703
2917
12,786.34

57
458
20494
16733
2287
1955
41,984.87
17,084.67
1410
18494
32
1083
0
17379
2329
15,049.61

69
551
24666
20139
2753
2353
50,531.38
20,562.46
1696
22259
39
1303
0
20917
2803
18,113.13

82
658
29417
24018
3283
2806
60,265.10
24,523.35
2023
26547
47
1554
0
24946
3343
21,602.22

98
780
34909
28502
3896
3330
71,514.92
29,101.18
2401
31502
55
1845
0
29602
3968
25,634.75

27,460.75 34,094.83 42,927.43 51,665.80

61,618.05

73,120.43

22
4893
797

19
4306
712

29
6538
1087

35
7869
1309

41
9385
1561

49
11137
1852

21,748

29,058

35,273

42,453

50,631

60,082

19572

19572

56

65

f Equity

DCF Valuation
2018

Report Writing

2019

2020

2021

2022

2023

118901

137610

155914

172859

187437

198684

3
118,897.11

4
137,606.41

4
155,910.05

5
172,853.78

5
187,431.95

6
198,677.87

115
922
41251
33680
4604
3935
84,508.52
34,388.59
2837
37226
65
2180
0
34981
4688
30,292.35

134
1067
47742
38980
5329
4554
97,806.53
39,799.88
3284
43084
76
2523
0
40485
5426
35,059.06

151
1209
54093
44165
6038
5160
110,816.22
45,093.84
3720
48814
86
2858
0
45870
6148
39,722.43

168
1341
59971
48965
6694
5721
122,859.31
49,994.47
4125
54119
95
3169
0
50855
6816
44,039.31

182
1454
65029
53095
7258
6203
133,221.04
54,210.91
4473
58684
103
3436
0
55144
7391
47,753.51

193
1541
68931
56280
7694
6576
141,214.30
57,463.56
4741
62205
109
3642
0
58453
7834
50,618.72

100,002.29 113,304.04

125,617.50

S E R V I C E S Ltd

86,405.74

136,211.85 144,384.56

58
13160
2189

67
15231
2533

76
17257
2870

85
19132
3182

92
20746
3451

97
21990
3658

70,999

82,171

93,101

103,219

111,924

118,639

2024
210605
6
210,598.54
204
1633
73067
59657
8155
6970
149,687.16
60,911.38
5025
65937
116
3861
0
61960
8304
53,655.84

153,047.63
103
23310
3877
125,758

S.no

HOME

Cash Flows Statement

Income Statement
2009

2010

(in Crore's)
1 Cash Flows From Operations
Profit After Tax
Add:Finance cost
Less:Other income
Change in Current Assets
Change in Current Liabilities
TOTAL C F O
2 Cash Flows From Investing
Change in Non-Current Assets
Other income
TOTAL C F I
3 Cash Flows From Financing
Change in Non-Current Liabilities
Change in share capital
Equity dividents
Preference Share divident
Divident Tax
Interest / finance cost
TOTAL C F F
TOTAL CASH FLOWS

FREE CASH FLOWS TO EQUITY

4,696.21
7
456

5,618.51
10
-182
-1586
2225
6,084.60
-2304.39
182
-2122.29
-4.63
97.86
-3914
-17
-658
-10
-4505.25
-542.94

5,732.94

atement

Balance Sheet
2011

2012

Beta Valuation

Assumption Sheet
2013

2014

2015

T A T A

2016

C O N S U L T A N S Y S E R V I C E S Lt

7,569.99 10,975.98 12,786.34

20
16
31
-486
-2685
-2230

15,049.61
32
-1410

18,113.13
39
-1696

21,602.22
47
-2023

-4643
-857
1,603.72

-7300
2883
3,890.80

-6786
4042
10,928.06

-7452
2917
11,920.91

-8487
3322
14,460.78

1031.59
486
1518.03

-915.85
2685
1769.33

5.38
0
-2740
-11
-451
-20
-3216.55

55.11
0
-4893
-22
-797
-16
-5673.67

66.89
0
-4306
-19
-712
-31
-5000.79

-37.86
0
-6538
-29
-1087
-32
-7724.72

25.50
0
-7869
-35
-1309
-39
-9226.11

29.04
0
-9385
-41
-1561
-47
-11004.68

-94.80

-13.54

43.66

1713.37

1115.58

1748.63

542.79

2,922.96

3,516.94

8,732.04

10,109.00

12,397.18

-7040
981
4,527.68

-1713.62 -2899.53381
-3275.721 -3730.7609
2230
1410
1696
2023
516.77 -1489.96932 -1579.22341 -1707.4715

Valuation

Cost of Equity
2017

2018

DCF Valuation
2019

2020

2021

2022

U L T A N S Y S E R V I C E S Ltd
25,634.75
55
-2401

30,292.35
65
-2837

35,059.06
76
-3284

39,722.43
86
-3720

44,039.31
95
-4125

47,753.51
103
-4473

-9808
3839
17,319.88

-11329
4434
20,626.03

-11594
4538
24,795.40

-11343
4440
29,184.73

-10500
4110
33,619.48

-9034
3536
37,885.95

-4311.853829 -4980.21298 -5096.887489

-4986.377 -4615.901977 -3971.46335
2401
2837
3284
3720
4125
4473
-1910.872444 -2142.99544 -1813.213815 -1265.92724 -491.1274687 501.1870262
33.56
0
-11137
-49
-1852
-55
-13059.84

38.77
0
-13160
-58
-2189
-65
-15433.59

39.67
0
-15231
-67
-2533
-76
-17867.37

38.81
0
-17257
-76
-2870
-86
-20250.13

35.93
0
-19132
-85
-3182
-95
-22457.94

30.91
0
-20746
-92
-3451
-103
-24360.04

2349.16

3049.44

5114.81

7668.68

10670.41

14027.09

14,934.85

17,871.32

21,976.14

26,426.61

31,066.28

35,689.20

2023

2024

50,618.72
109
-4741

53,655.84
116
-5025

-6969
2728
41,745.69

-7387
2892
44,250.43

-3063.673338 -3247.49373857
4741
5025
1677.3360607 1777.97622439
23.85
0
-21990
-97
-3658
-109
-25830.57

25.28
0
-23310
-103
-3877
-116
-27380.40

17592.45

18648.00

40,051.07

42,454.13

HOME

Income Statement

Balance Sheet

Sales Forecasting
Sales growth rate in % of Company

Industry Sales Growth rate in %

GDP Growth rate in %

2009

2010

NA

2.86%

2009

2010

NA

0.08%

2009

2010

5.037

11.228

Calculation of Future Growth Rate of Industry

Indusry Growth in %

2014

2015

13.56%

12.59%

Where :
Y
=

Industry sales

SLOPE of equation

GDP Growth

INTERCEPT

2009
67,646

Future Sales of Industry

2010
67,700

Calculation of Future Growth Rate of Company Using EIC Analysis

Company sales by "EIC" Analysis

2009
22,404

2010
23,045

2011
29,276

Market Capitalization

33.12%

34.04%

37.67%

Market Capitalization Growth in %

0.92%

3.64%

2009

2010

2011

99.998%
0.01%
0.24%
0.73%
32.90%
31.01%
5.44%
2.81%
-2.04%

99.992%
0.00%
0.10%
0.80%
34.21%
27.98%
5.50%
2.48%
0.79%

99.996%
0.00%
0.06%
0.82%
34.81%
27.79%
4.08%
2.47%
1.66%

1.86%
-0.46%
6.76%
347.71%

2.04%
-0.06%
11.61%
291.04%

1.84%
0.00%
12.99%
270.61%

(n)Fixed Assets % Sales

0.18%
22.56%
14.97%

0.16%
31.59%
16.06%

0.14%
21.94%
16.29%

(O)NCA % Sales

41.47%

50.32%

36.08%

(p)Investments % NCA

63.90%

68.08%

54.87%

(q)Total Assets % Sales

82.76%

97.35%

88.96%

(R)finance Cost % NCL

18.43%

26.69%

48.66%

(S)Dividend TAX

17.08%

16.80%

16.45%

(t)Equity dividend

8.39%

23.94%

13.38%

(u)Preferred dividend

0.04%

0.10%

0.05%

Expenses Forecast Based on Sales %

1 Net Sales Growth
2 Excise Duties % of Sales
3 (a) Cost of materials consumed
(b) power and fuel
(c) Employee benefits expense
(d) Other Manufacturing Exp
(e)selling & Adm exp
(f)Miscellaneous Exp
(g)Other incomes
(h)Deprication & Amortization
(i)Extraordinary items
(j)Tax % of EBT
(k)Earnings B4 Appropriation

Balance Sheet Forecast Based on Sales %

(L) NCL % Sales
(m)CL % Sales

lance Sheet

Cash Flows

Assumption Sheet

Competators

Beta Valuation

sting
2011

2012

2013

27.04%

32.73%

24.62%

The Sales Growth Rate of Company in the yea

the Industry Growth rate is On

2011

2012

2013

14.78%

20.59%

9.79%

2011

2012

2013

2014

2015

2016

7.745

3.986

5.676

6.23

6.632

6.861

2016

2017

2018

12.03%

11.88%

11.78%

E of equation

it can be calcualted by Regration formula

Y= mx + c

SLOPE

-0.02428759

INTERCEPT

0.28695861

2011
77,706

2012
93,703

2013
102,876

2014
116,831

2015
131,538

2016
147,365

2012
38,859

2013
48,427

2014
59,071

2015
71,096

2016
84,791

2017
100,619

41.47%

47.07%

50.56%

54.05%

57.54%

61.03%

C Analysis

3.80%

5.60%

2012

2013

99.999%
0.00%
0.03%
0.75%
36.29%
27.22%
4.34%
2.07%
6.91%

99.998%
0.00%
0.05%
0.78%
35.27%
27.64%
0.00%
6.72%
4.61%

1.77%
-0.33%
17.08%
250.19%

1.66%
0.00%
18.57%
266.65%

0.25%
23.95%
14.90%

0.34%
21.24%
14.18%

29.54%

27.24%

49.56%

47.94%

88.16%

88.82%

17.04%

18.77%

16.30%

16.54%

17.82%

12.63%

0.08%

0.06%

3.49%

3.49%

3.49%

3.49%

mpetators

Cost of Equity

DCF Valuation

Rate of Company in the year 2013 is 24.62% where

e Industry Growth rate is Only 9.79%

2017

2018

6.922

6.965

d by Regration formula

2017
164,877

2018
118,901
64.52%

2018
184,299

3.49%

HOME

Income Statement

Balance Sheet

Assumptions
S.no Assumption name

Assumption tpye

1 Sales Growth
2 Market Capitalization

EIC Analysis

3 Net Sales Growth

4 Excise duties
5 Expenses forecast

Historical Avg
Historical Avg

(a) Cost of materials consumed

(b) power and fuel
(c) Employee benefits expense
(d) Other Manufacturing Exp
(e)selling & Adm exp
(f)Miscellaneous Exp

(g)Other incomes
(h)Deprication & Amortization

(i)Extraordinary items
(J)TAX % of EBT
(k)Earnings b4 Appropriation
6

Historical Avg

Historical Avg
Historical Avg
Historical Avg
Historical Avg
Historical Avg
Historical Avg
Historical Avg
Historical Avg
Latest Growth
Historical Avg
Historical Avg

Balance Sheet Forecast

(L)NCL % Sales
(m)CL % Sales
(n)Fixed Assets % Sales
(O)NCA % Sales
(P)Investments % NCA
(q)Total Assets % Sales
(R)finance cost % NCL
(S)Dividend Tax
(t)Equity dividend
(u)Preferred dividend

Historical Avg
Historical Avg
Historical Avg
Latest Growth
Historical Avg
Historical Avg
Historical Avg
Historical Avg
Historical Avg
Historical Avg

Balance Sheet

Cash Flows

Calculations

Beta
Valuation

Competators

tions
Manual

Value
Value Taken From Calculation Sheet Cell (H35)
6%
3.49%

6
99%
1%
7%
5%
30%
25%
5%
5%
3%

100.00%
0.00%
0.10%
0.78%
34.69%
28.33%
3.87%
3.31%
2.39%
1.83%
0.00%
13.40%
285.24%
0.21%
24.26%
15.28%
27.24%
56.87%
89.21%
25.92%
16.63%
15.23%
0.07%

Changing the Assumption will directly a

on FCFE Share Price

Changing the Assumption will directly

Model Share Price

Competators

Cost of Equity

e Assumption will directly affect

n FCFE Share Price

he Assumption will directly affect on DDM

Model Share Price

DCF Valuation

PREVIOUS SHEET

INDUSTRY ANALYSIS

Sales Turn over of the following companies

S.no Competitors name

1
2
3
4
5
6
7
8
9
10

Infosys
WIPRO
HCL
TECH MAHINDRA
ORACLE FINANCIAL
MIND TREE
MAHINDRA SATYAM
MPHASIS
NIIT TECH
POLARIS TECH

Sales of Industries

Sales Growth in %

2009

2010

20,264.00
21,612.80
4,675.09
4,357.80
2,212.62
1,012.57
8,432.50
3,405.02
502.07
1,171.34

21,140.00
23,006.30
5,078.76
4,483.80
2,243.47
1,233.25
5,107.60
3,770.09
493.58
1,143.48

2009

2010

67,645.81

67,700.33

2009

2010

NA

0.08%

LYSIS

NEXT SHEET

2011

2012

2013

25,385.00
26,401.20
6,794.48
4,965.50
2,360.51
1,509.00
4,780.80
3,404.13
729.28
1,375.96

31,254.00
31,803.40
8,907.22
5,243.00
2,605.85
1,915.20
5,964.30
3,420.84
827.46
1,762.09

36,765.00
33,517.30
8,907.22
6,001.89
2,937.70
2,361.80
6,001.89
3,420.84
1,108.30
1,853.99

2011

2012

2013

77,705.86

93,703.36

102,875.93

2011

2012

2013

14.78%

20.59%

9.79%

HOME
1

Balance Sheet

SENSEX Market indicates of Last 5yrs

Month
Oct-09
Nov-09
Dec-09
Jan-10
Feb-10
Mar-10
Apr-10
May-10
Jun-10
Jul-10
Aug-10
Sep-10
Oct-10
Nov-10
Dec-10
Jan-11
Feb-11
Mar-11
Apr-11
May-11
Jun-11
Jul-11
Aug-11
Sep-11
Oct-11
Nov-11
Dec-11
Jan-12
Feb-12
Mar-12
Apr-12
May-12
Jun-12
Jul-12
Aug-12
Sep-12
Oct-12
Nov-12
Dec-12
Jan-13

Open
17186.2
15838.63
16947.46
17473.45
16339.32
16438.45
17555.04
17536.86
16942.82
17679.34
17911.31
18027.12
20094.1
20272.49
19529.99
20621.61
18425.18
17982.28
19463.11
19224.05
18527.12
18974.96
18352.23
16963.67
16255.97
17540.55
16555.93
15534.67
17179.64
17714.62
17429.96
17370.93
16217.48
17438.68
17244.44
17465.6
18784.64
18487.9
19342.83
19513.45

High
Low
17493.17
17290.48
17530.94
17790.33
16669.25
17793.01
18047.86
17536.86
17919.62
18237.56
18475.27
20267.98
20854.55
21108.64
20552.03
20664.8
18690.97
19575.16
19811.14
19253.87
18873.39
19131.7
18440.07
17211.8
17908.13
17702.26
17003.71
17258.97
18523.78
18040.69
17664.1
17432.33
17448.48
17631.19
17972.54
18869.94
19137.29
19372.7
19612.18
20203.66

Income Statement

Beta

ValuatIon

Returns

15805.2
15330.56
16577.78
15982.08
15651.99
16438.45
17276.8
15960.15
16318.39
17395.58
17819.99
18027.12
19768.96
18954.82
19074.57
18038.48
17295.62
17792.17
18976.19
17786.13
17314.38
18131.86
15765.53
15801.01
15745.43
15478.69
15135.86
15358.02
17061.55
16920.61
17010.16
15809.71
15748.98
16598.48
17026.97
17250.8
18393.42
18255.69
19149.03
19508.93

Close
15896.28
16926.22 0.06479126
17464.81 0.03181986
16357.96 -0.06337601
16429.55 0.00437646
17527.77 0.06684419
17558.71 0.0017652
16944.63 -0.03497296
17700.9 0.04463184
17868.29 0.00945658
17971.12 0.00575489
20069.12 0.11674286
20032.34 -0.00183267
19521.25 -0.02551325
20509.09 0.05060332
18327.76 -0.10635918
17823.4 -0.02751891
19445.22 0.09099386
19135.96 -0.01590417
18503.28 -0.03306236
18845.87 0.0185151
18197.2 -0.03441974
16676.75 -0.08355406
16453.76 -0.01337131
17705.01 0.07604645
16123.46 -0.08932782
15454.92 -0.0414638
17193.55 0.11249686
17752.68 0.03251975
17404.2 -0.01962971
17318.81 -0.00490629
16218.53 -0.06353092
17429.98 0.07469543
17236.18 -0.01111877
17429.56 0.01121942
18762.74 0.0764896
18505.38 -0.01371655
19339.9 0.04509607
19426.71 0.00448865
19894.98 0.02410444

Feb-13
Mar-13
Apr-13
May-13
Jun-13
Jul-13
Aug-13
Sep-13
Oct-13

19907.21
18876.68
18890.81
19459.33
19859.22
19352.48
19443.29
18691.83
19452.05

19966.69
19754.66
19622.68
20443.62
19860.19
20351.06
19569.2
20739.69
20323.77

18793.97
18568.43
18144.22
19451.26
18467.16
19126.82
17448.71
18166.17
19264.72

18861.54 -0.05194476
18835.77 -0.00136627
19504.18 0.03548621
19760.3 0.01313154
19395.81 -0.01844557
19345.7 -0.00258355
18619.72 -0.03752669
19379.77 0.04081963
20272.91 0.0460862

Calculation
Sheet

Cash Flows

ValuatIon

Assumption Sheet

Beta Valuation

TCS Share Prices of Last 5yrs

Month
Oct-09
Nov-09
Dec-09
Jan-10
Feb-10
Mar-10
Apr-10
May-10
Jun-10
Jul-10
Aug-10
Sep-10
Oct-10
Nov-10
Dec-10
Jan-11
Feb-11
Mar-11
Apr-11
May-11
Jun-11
Jul-11
Aug-11
Sep-11
Oct-11
Nov-11
Dec-11
Jan-12
Feb-12
Mar-12
Apr-12
May-12
Jun-12
Jul-12
Aug-12
Sep-12
Oct-12
Nov-12
Dec-12
Jan-13

Open
618
636
703.4
754.8
734.35
762
786.35
761.25
743.05
750
843
842
927
1052.05
1064
1167
1165
1113
1185
1170
1157
1198
1142.4
1073
1020
1108
1140
1161
1129.4
1223
1170
1242.1
1240
1279
1238.1
1350
1295
1314.9
1315
1266.95

High
657.9
711.65
757
829
778.55
845.9
834.9
843
794
859.8
885
960
1072.95
1108
1180
1220
1198.9
1198
1246.95
1183.4
1205
1205
1149.9
1074.75
1154
1156
1196.9
1209.9
1280.85
1227
1250
1295.25
1287
1284
1388.8
1439.8
1334.9
1348
1322
1400

Low

Close
556.65
601
677.05
696.5
708.15
755
755.6
685.25
730
725.75
828.55
835.1
869
998.95
1048.4
1087.8
1055.9
1057.25
1140
1108.1
1058
1112.1
902
969
1015.3
1041
1114.4
1045.3
1108.25
1111.8
1047.65
1194.1
1196.2
1175.2
1218.1
1261.1
1055
1256.05
1197
1255.3

628.3
688.4
750.25
736.2
761.8
780.65
765.4
743.05
751
839.8
843.75
926.95
1052.9
1076
1165.65
1159.5
1110.25
1183.9
1165.65
1157.15
1184.2
1137
1042.4
1037.3
1115.8
1115.55
1160.65
1132.4
1221.95
1168.8
1246.6
1245.35
1277.45
1243.65
1343.75
1295.9
1315.5
1314
1255.85
1344.15

Returns
0.09565494
0.08984602
-0.0187271
0.03477316
0.02474403
-0.019535
-0.0292004
0.01069915
0.11824234
0.0047035
0.09860741
0.13587572
0.02193941
0.08331784
-0.005276
-0.0424752
0.06633641
-0.0154152
-0.0072921
0.0233764
-0.0398581
-0.0832014
-0.0048926
0.07567724
-0.0002241
0.04042849
-0.0243398
0.07907983
-0.0434961
0.066564
-0.0010027
0.02577589
-0.026459
0.08048888
-0.0356093
0.01512462
-0.0011403
-0.0442542
0.07031094

Feb-13
Mar-13
Apr-13
May-13
Jun-13
Jul-13
Aug-13
Sep-13
Oct-13

1344
1515.1
1565
1373.5
1504
1512
1810.05
2020
1932

1525
1597.6
1575
1527.5
1544.7
1831.5
2050
2078.8
2098

1342.15
1491
1365
1373.5
1381.05
1465
1695.2
1894.05
1916.7

1517
1575.75
1378.4
1498.45
1518.15
1815.5
2033.2
1927.8
2090.85

0.12859428
0.03872775
-0.1252419
0.08709373
0.01314692
0.19586339
0.11991187
-0.0518395
0.08457828

luation

Cost of Equity

DCF Valuation
0.2
0
Beta Value

Historical Beta

Latest Beta

0.1624122625

-0.1828750586

Dash Board
1

Income Statement

Cash Flows

Calculation Sheet

Cost of Equity
Rf
Rm
Rm - Rf
Beta()

7.50%
15.50%
8%
0.16241226

Formula:-

Rf+(Rm-Rf)

Cost of Equity

8.80%

Where:Rf :
Rm :
Beta :
Wd :
We :
Kd :
Ke :

Beta Assumption
Risk Free Asset
Market Risk
Systematic Risk
weight of debt
weight of Equity
cost of debt
cost of equity

Change the Beta

Calculation Sheet

Assumption Sheet

Beta Valuation

Cost of Capital
Wd
kd
We
ke
Formula:WACC

eta Assumption
Historical Beta
0.162412263

0.50%
25.92%
99.50%
8.80%
(wd*kd + we*ke)
8.88%

Balance Sheet

DCF Valuation

DCF Valuation

HOME
Dash Board

Income Statement

Latest Growth rate of sales

Long term Growth rate
Cost of Equity
Eternal Growth rate of Divident

1 Dividend Discount Model

Years
Dividends
Sales Growth Rate

Income Statement
1

Balance Sheet

=
=
=
=
2014
1yr
6538
21.98%

Terminal Value (Yr 11 to Infinate as on Yr 10)

Present Value of (TV)

832,702
358,285

Present Value of Dividends

Total PV Of Dividends
Total Value of Equity

6,009
84,187
442,472

Calculated Share Price

2 Free Cash Flows Model

Years Add: Cash Flows From

Operations
Operations
Less:
Less: Total
Total Fixed
Fixed
Investments
Add: Net Borrowings
FCFE
FCFE
Terminal Value (TV)from Yr 11 to till infinate
Present Value of (TV)
PV of FCFE
Sum of the PV of FCFE
Total value of Equity
FCFE

Calculated Share Price

2,260.74
2014
1yr
8,732.04

1,516,599
599,769
8,026
143,018.10
742,786.79
3,795.15

Income Statement

nce Sheet

Cash Flows

Balance Sheet

Cash Flows

Calculation Sheet

Assumption Sheet
Assumption Sheet

Beta Valuation
Beta Valuation

Disconted Cash Flows (DCF) Valuation

18.17%
6%
8.80%
6%

Eternal Growth rate of FCFE =

WACC

Cost of Equity
=
Share Value as on 10/11/2012 =

2015
2yr`
7869
20.36%

2016
3yr
9385
19.26%

2017
4yr
11137
18.67%

2018
5yr
13160
18.17%

2019
6yr
15231
15.74%

2020
7yr
17257
13.30%

6,648

7,287

7,948

8,632

9,183

9,563

2015
2yr`
10,109.00

2016
3yr
12,397.18

2017
4yr
14,934.85

2018
5yr
17,871.32

2019
6yr
21,976.14

2020
7yr
26,426.61

8,540

9,626

10,659

11,723

13,249

14,644

Market Value as on 10/11/2013

Rs.2099.50

Beta Valuation
Beta Valuation

Calculations
Cost of Equity

Cost of Equity
Report Writing

tion
6%
8.88%
8.80%
Rs.2099.50 (BSE INDEX)

=
n 10/11/2012 =

.2099.50

2021
8yr
19132
10.87%

2022
9yr
20746
8.43%

2023
10yr
21990
6.00%

2024

9,744

9,712

9,462

2021
8yr
31,066.28

2022
9yr
35,689.20

2023
10yr
40,051.07

2024
11yr
42,454.13

15,823

16,707

17,233

16,789

INFINATE Yr

23310

Go Back