Reference Model
Mindtree
Prepared By: Neerav Koli, Distance Learning , Mumbai
http://proschoolonline.com
Email ID :[email protected]
Mumbai
IMS Proschool Pvt Ltd,
Maharashtra High School Complex,
Principal N.M Kale Marg, Off Gokhale Rd,
Dadar (W), Mumbai 400028
Tel. No. :022-65285393/94
+91 9372895050
, Mumbai
Directions
Mindtree is one of the young IT company of the country which has seen commendable success within a few span of time. A fina
demand in the investment market. This gave us the opportunity to guide our student to come out with a financial model on the c
IMS Proschool to hone their financial modeling skills.
This model is being developed by a financial modeling student at IMS Proschool. This is provided as reference model and is fo
the objective of the reference model, the formulas and linkages of the model are kept intact.
IMS Proschool provides certificate programs in Financial Modeling. The program grooms the candidates for entry level jobs in t
analysis and modeling skills. The program aims to impart the necessary skills required to perform the role of a Financial Resea
with the practical skill sets required to perform financial analysis, modeling and valuation through Excel.
please contact the address furnished below. You can also email us at the below mentioned email-id.
uccess within a few span of time. A financial model of such a company is in great
ome out with a financial model on the company. These exercises help the student at
provided as reference model and is for information purpose only. Keeping in view
act.
the candidates for entry level jobs in the finance sector which require financial
perform the role of a Financial Research Analyst. The program equips students
through Excel.
For more details,
ed email-id.
IMS Proschool Pvt Ltd,
Maharashtra High School Complex,
Principal N.M Kale Marg, Off Gokhale Rd,
Dadar (W), Mumbai 400028
Tel. No. :022-65285393/94
+91 9372895050
Email ID : [email protected]
Mindtree Ltd.
Click on the name of Tab to go to the relevant sheet
No.
Name of Tab
Company Overview
Summary
Profit & Loss A/c
Balance Sheet
Cash Flow Statement
Geographic Revenue
Segment-wise Revenue
Cost Analysis
Debt Schedule
10
Fixed Asset
11
Valuation
12
Beta Calculation
13
Notes
Description
Overview of the Company
Financial snapshot of the company
Historical and forecasted income statement
Historical and forecasted balance sheet
Forecasted cash flow statement
Geographic revenue breakup for the historical years as reported by the Company
Segment-wise revenue breakup for the historical years as reported by the Company
Cost Structure
Debt Schedule
Fixed Asset & Depreciation Schedule
DCF Valuation
Beta Calculation of Company relative to Sensex
Notes related to preparation of Financial Statement
Mindtree Ltd.
Company Overview
(Rs. In Lacs, except per share data)
Company Information
Industry
Sector
Country
BSE/NSE Code
Market Index
Year end
Reporting currency
Company Description
IT Consulting & Software
Software-Medium
India
532819/MINDTREE
BSE - 500
31-Mar
Rs.
Stock Data
Price as on 22nd Oct 2010
52 Week High
52 Week Low
YTD Change
YTD Change %
Shares Outstanding
Market Capitalization(Lacs)
Beta Vs Sensex
Mindtree Ltd. Limited , formerly
development. It operates in two
engineering (SPE). SPE provides fu
production support for customers
supply chain management, enterp
Products & Services
Internet & Enabling Technologies
Application Maintenance
Per Share Data & Key Ratios
512.7
729.9
491
-84.95
-14.7%
407
208,669
1.16
EPS diluted
Gross
Profit
Margin
EBITDA/sales (%)
Net income/sales (%)
ROE (TTM)
EV/Sales
EV/EBITDA
P/E
P/Sales
P/BV
Geo revenue breakup for 2010
USA
Europe
India
APAC / Rest of the World
Total
84,209
25,552
7,737
12,101
129,598
25,552
7,737
12,101
84,209
USA
Europe
India
APAC / Rest of the World
Revenue Forecast
20000000%
18000000%
16000000%
14000000%
12000000%
10000000%
8000000%
6000000%
4000000%
2000000%
0%
% y/y
Growth
2010
2011
2012
2013
2014
2015E
2016E
2017E
6000000%
4000000%
2000000%
0%
2010
2011
2012
2013
2014
2015E
2016E
2017E
Revenue Data
Total Revenue
% y/y Growth
2010
129,598
2011
133,358
3%
2012
138,077
4%
2013
145,127
5%
2014
155,286
7%
mpany Description
ndtree Ltd. Limited , formerly is an international information technology (IT) consulting and implementation company. It delivers business solut
velopment. It operates in two units: production engineering (PE) services and IT services. PE services include research and development (R&D) s
gineering (SPE). SPE provides full life cycle product engineering, professional services and sustained engineering services. IT Services offer consulting
oduction support for customers in manufacturing, financial services, travel and leisure and other industries, in the areas of e-business, data warehous
pply chain management, enterprise resource planning (ERP) and maintenance and re-engineering of legacy mainframe applications.
oducts & Services
ernet & Enabling Technologies, Data Warehousing /Business Intelligence, Supply Chain Management, ERP, Product Engineering, Product Assuran
plication Maintenance
r Share Data & Key Ratios
2009-10 A
52.79
38%
19%
17%
32%
1.58
16
9.71
1.61
3.18
ITDA/sales (%)
t income/sales (%)
2010-11 E
43.04
38%
19%
13%
21%
1.5
8
11.9
1.6
2.5
2011-12 E
2012-13 E
45.35
38%
19%
13%
18%
1.5
8
11.3
1.4
2.1
48.35
38%
19%
14%
16%
1.4
7
10.6
1.4
1.7
Segment Revenue Breakup for 2010
IT Services
PE Services
Total
25,552
69,802
59,796
129,598
46%
7,737
12,101
Rest of the World
54%
% y/y
Growth
2017E
IT Services PE Services
2017E
2015E
166,156
7%
2016E
177,787
7%
2017E
186,677
5%
ny. It delivers business solutions through global software
ch and development (R&D) services and software product
s. IT Services offer consulting and implementation and post
of e-business, data warehousing and business intelligence,
lications.
Engineering, Product Assurance & Testing, Web Services.
ces PE Services
46%
Financial Summary Of Mindtree
2007-08A
2008-09A
2009-1A
73,384
26,606
12,580
11,273
10,419
123,747
54,069
33,095
6,039
5,231
129,598
49,773
24,556
25,467
21,484
Cash Flow
Cash Flow from operations
Cash Flow from investing
Cash Flow from financing
9,063
(15,003)
3,739
17,980
(22,235)
2,256
23,017
(10,045)
(13,415)
Net Cash Flow
Capex
Free Cash Flow to Firm
(2,214)
(21,778)
(12,714)
(1,958)
(4,346)
13,634
(522)
(4,573)
18,444
P&L
Revenue
Gross Profit
EBITDA
PBT
PAT
Balance Sheet
Share Capital
Share Application Money
Reserves & Surplus
Total Debt
Net Fixed Assets
Goodwill
Investments
Deferred tax assets, net
Net Current Assets
Margins
Gross Profit
EBITDA
EBIT
PBT
Net Profit
Per share Data
3,792
5
49,605
9,172
62,575
3,800
0.03
51,328
17,213
72,340
3,951
63,112
305
67,369
25,897
16,549
898
19,230
62,575
29,616
14,605
10,133
1,897
16,088
72,340
28,587
1,543
12,725
2,136
22,379
67,369
36%
17%
12%
15%
14%
44%
27%
22%
5%
4%
38%
19%
14%
20%
17%
EPS - Basic
EPS - Diluted
Book Value
27.69
26.75
137
13.84
13.70
136
54.77
52.79
161
y Of Mindtree
2010-11E
2011-12E
2012-13E
133,358
51,217
25,251
20,587
17,519
138,077
53,029
26,145
21,690
18,457
145,127
55,737
27,480
23,126
19,679
23,730
(3,655)
(1,106)
22,193
(3,821)
(771)
27,879
(4,070)
(771)
18,969
(4,706)
19,025
17,601
(4,872)
17,321
23,038
(5,121)
22,758
3,951
79,912
83,863
3,951
97,649
101,601
3,951
121,554
125,505
27,609
1,543
12,725
2,136
39,852
83,863
26,975
1,543
12,725
2,136
58,222
101,601
26,692
1,543
12,725
2,136
82,410
125,506
38%
19%
15%
15%
13%
38%
19%
15%
16%
13%
38%
19%
15%
16%
14%
44.66
43.04
202
47.05
45.35
246
50.17
48.35
305
Mindtree Ltd.
Profit & Loss Account
(Rs. In Lacs, except per share data)
Revenues
Cost of Revenues
Gross Profit
Selling, General & Administrative expenses
Provision for bad & doubtful debts
Operating profit before interest and depreciation
Interest including finance charges
Depreciation
Operating Profit
Foreign exchange gain/(loss)
Other Income
Profit Before Tax
Audited
2007-08 A
Net profit after tax
123,747
(69,678)
129,598
(79,825)
26,606
54,069
49,773
(13,875)
(151)
(20,168)
(806)
(24,978)
(239)
12,580
33,095
24,556
(590)
(3,493)
Weighted average number of shares
Basic
Diluted
Growth Analysis - Y/Y
Revenues
EBITDA
Operating Profit
Net Profit
(1,620)
(5,696)
8,497
25,779
399
2,377
(20,892)
1,152
(1,290)
436
-
10,419
EPS Basic
EPS Diluted
Audited
2009-10 A
73,384
(46,778)
11,273
Provision for taxation including FBT
Deferred tax (charge)/credit
MAT Credit Entitlement/(reverse)
Share of profits of associates
Minority interest
Audited
2008-09 A
27.69
26.75
6,039
(1,386)
681
33
274
(410)
5,231
13.84
13.70
376.3
389.5
378
382
(267)
(6,518)
17,771
6,734
962
25,467
(3,796)
(288)
101
21,484
54.77
52.79
392
407
69%
163%
203%
-50%
4.7%
-26%
-31%
311%
44%
27%
38%
19%
Growth Analysis - Q/Q
Revenues
EBITDA
Operating Profit
Net Profit
Margin Analysis ( as a % of Revenues)
Gross Profit Margin
EBITDA
36%
17%
EBIT
PBT
Net Profit Margin
12%
15%
14%
22%
5%
4%
14%
20%
17%
Interest Cover
Effective Tax rate
Financial Leverage
21
11%
1.5
20
11%
1.3
92
15%
1.4
2010-11 E
2011-12 E
2012-13 E
133,358
(82,141)
138,077
(85,047)
145,127
(89,390)
51,217
53,029
55,737
(25,703)
(263)
(26,612)
(273)
(27,971)
(287)
25,251
26,145
27,480
(30)
(5,685)
(5,506)
(5,404)
19,536
20,639
22,075
1,051
1,051
1,051
20,587
21,690
23,126
(3,069)
17,519
44.66
43.04
(3,233)
18,457
47.05
45.35
(3,447)
19,679
50.17
48.35
392
407
392
407
392
407
38%
19%
38%
19%
38%
19%
15%
15%
13%
15%
16%
13%
15%
16%
14%
15%
1.3
15%
1.3
15%
1.2
Mindtree Ltd.
Balance Sheet
(All Figs in Lacs)
Sources Of Funds
Shareholders Funds
Share Capital ( Face Value of Rs. 10 Each)
Share Application Money
Reserves & Surplus
Standalone
Audited
2007-08 A
Consolidated
Audited
2008-09 A
3,792
5
49,605
53,403
Minority Interest
3,800
0.03
51,328
55,127
Loan Funds
Secured Loans
Unsecured Loans
Consolidated
Audited
2009-10 A
3,951
63,112
67,064
3,276
9,172
-
13,757
180
305
62,575
72,340
67,369
34,744
(11,176)
23,568
2,329
25,897
49,409
(21,096)
28,313
1,304
29,616
51,334
(25,212)
26,122
2,466
28,587
16,549
898
14,605
10,133
1,897
1,543
12,725
2,136
Current assets, loans and advances
Sundry debtors
Cash and bank balances
Loans and advances
Total Current Assets
17,217
5,465
9,927
32,609
27,923
4,876
14,111
46,910
23,699
4,033
20,683
48,414
Current liabilities and provisions
Current Liabilities
Provisions
Total Current Liabilities
11,153
2,226
13,379
28,599
2,223
30,822
21,135
4,900
26,035
Net Current Assets
19,230
16,088
22,379
62,575
72,340
67,369
Total Shareholders Funds & Liabilities
Application Of Funds
Fixed Asset
Gross Block
Less Accumulated Depreciation
Net Block
Capital Work In Progress
Goodwill
Investments
Deferred tax assets, net
Total Assets
Check: Assets = Liab + Equity ?
ROE (TTM)
Book Value
Days Sales Outstanding(TTM)
Liquidity Ratio
Current Ratio
19.51%
137
86
2.4
9.49%
136
82
1.5
(0)
32.04%
161
67
1.9
Capital Structure Ratio
Debt Equity Ratio(Times)
Debt to Total Funds(%)
0.17
15%
0.25
20%
0.005
0.45%
% of Revenues
Loans and Advances
14%
11%
16%
% of Cost of revenue
Current Liabilities
24%
41%
26%
2010-11 E
3,951
79,912
83,863
2011-12 E
3,951
97,649
101,601
2012-13 E
3,951
121,554
125,505
83,863
101,601
125,505
56,040
(30,896)
25,143
2,466
27,609
60,912
(36,402)
24,510
2,466
26,975
66,033
(41,806)
24,227
2,466
26,692
1,543
12,725
2,136
1,543
12,725
2,136
1,543
12,725
2,136
28,589
23,002
18,177
69,768
29,600
40,603
18,820
89,024
31,112
63,641
19,781
114,534
25,016
4,900
29,916
25,901
4,900
30,801
27,224
4,900
32,124
39,852
58,222
82,410
83,863
101,601
125,506
(0)
(0)
(0)
20.89%
202
78
18.17%
246
78
15.68%
305
78
2.3
2.9
3.6
0.000
0%
0.000
0%
0.000
0%
14%
14%
14%
30%
30%
30%
Mindtree Ltd.
Cash Flow Statement
(All Figs in Lacs)
2007-08 A
Standalone
2008-09 A
Consolidated
Cash flows from operating activities:
Profit Before Tax
Adjustments:
Depreciation
Amortization of Stock Compensation
Interest Expense
Interest / dividend income
Loss /(profit) on sale of fixed assets
Profit on sales of investments
Exchange difference on derivatives
Effect of exchange differences on translation of foreign currency cash
and cash equivalents
Operating profit before working capital changes
11,273
3,493
192
589
(1,743)
(6)
(253)
13
13,560
Decrease/(Increase) in sundry debtors
Decrease/(Increase) in loans and advances
(Decrease)/Increase in current liabilities and provisions
Income taxes and FBT paid
(5,501)
(417)
3,270
(1,848)
Net cash provided by / (used in) operating activities
9,063
Cash flow from investing activities
Purchase of fixed assets
Proceeds on sale of fixed assets
Purchase of business/acquisition
Investment in subsidiaries
Interest /dividend received from investments
Inter-corporate advances granted
Purchase of investments
Sale/maturities of investments
Net cash Provided/ (used) in investing activities
Cash flow from financing activities
Issue of share capital (net of issue expenses paid)
Receipt of share application money
Interest paid on loans
Proceeds from term loans
Repayment of term loans
Repayment of finance lease obligation
Proceeds/ (repayment) of short term loans, net
Increase/(decrease) in bank overdraft
Dividends paid (including distribution tax)
Net cash Provided/(used) by financing activities
Effect of exchange differences on translation of foreign currency cash
and cash equivalents
6,039
5,696
100
1,620
(870)
(189)
(58)
15,229
(41)
27,525
(3,352)
(21)
(3,304)
(2,869)
17,980
(21,778)
75
847
(22,918)
28,770
(15,003)
(4,346)
651
(29,195)
###
627
###
(80,764)
90,792
(22,235)
(1,193)
5
(591)
9,550
(1,277)
(232)
(1,647)
(877)
56
0
(1,586)
10,180
(13,691)
(382)
8,583
###
(904)
3,739
2,256
(13)
41
Net increase/(decrease) in cash and cash equivalents
Cash and cash equivalents of MindTree Wireless Pvt Ltd as on
the date of acquisition
Cash and cash equivalents at the beginning of the year
Cash and cash equivalents at the end of the year
(2,214)
(1,958)
7,680
5,465
###
6,834
4,876
9,063
(21,778)
(12,714)
17,980
(4,346)
13,634
0.12
-140.28%
0.15
75.83%
23%
24%
Calculation of Free Cash Flow
Operating Cash Flow
Capital expenditure
Free Cash Flow
Cash Flow Ratios
Operating Cash Flow / Revenues
Free Cash Flow / Operating Cash Flow
Dividends paid (including distribution tax) % of Share Capital
2009-10 A
Consolidated
2010-11 E
25,468
6,518
51
267
(500)
6
(110)
(11,131)
79
20,649
4,542
6
2,181
(4,360)
20,587
5,685
51
30
(1,051)
25,302
(4,890)
2,506
3,881
(3,069)
2011-12 E
2012-13 E
21,690
5,506
51
(1,051)
26,196
(1,012)
(643)
885
(3,233)
23,126
5,404
51
(1,051)
27,531
1,511
961
1,323
(3,447)
23,017
23,730
22,193
27,879
(4,573)
50
(2,925)
###
500
(615)
(104,479)
101,997
(10,045)
(4,706)
1,051
(3,655)
(4,872)
1,051
(3,821)
(5,121)
1,051
(4,070)
941
(0)
(267)
(5,157)
###
(8,475)
###
(458)
(30)
###
(305)
(771)
(771)
(771)
(13,415)
(1,106)
(771)
(771)
(79)
(522)
18,969
17,601
23,038
764
3,791
4,033
4,033
23,002
23,002
40,603
0
40,603
63,641
23,017
(4,573)
18,444
23,730
(4,706)
19,025
22,193
(4,872)
17,321
27,879
(5,121)
22,758
0.18
80.13%
0.18
80.17%
0.16
78.05%
0.19
81.63%
12%
20%
20%
20%
Mindtree Ltd.
Geographic Revenue Model
(All Figs in Lacs)
Q1 Jun-08
Q2 Sep-08
2007-08 A
Geographic Wise Revenues
USA
Europe
India
APAC / Rest of the World
Total
48,440
13,486
4,210
7,247
73,383
Y/Y (%) Growth in Geographic Revenues
USA
Europe
India
APAC / Rest of the World
Total
Q/Q (%) Growth in Geographic Revenues
USA
Europe
India
APAC / Rest of the World
Total
Geographic Revenues as % of Total
USA
Europe
India
APAC / Rest of the World
Total
66%
18%
6%
10%
100%
13,511
5,398
1,031
2,281
22,221
20%
102%
49%
53%
38%
19,894
6,403
1,664
3,241
31,202
63%
93%
73%
88%
71%
3%
38%
-20%
9%
9%
47%
19%
61%
42%
40%
61%
24%
5%
10%
100%
64%
21%
5%
10%
100%
4
Amount In Rs.
31,202
36,380
Q2 Sep-08
Q3 Dec-08
USA
19,894
24,097
Europe
6,403
7,232
India
1,664
2,425.95
APAC/ ROW
3,241.7
2,625.76
Total
31,202
36,380
-----------------------------------------------------------------------------------------------------------3
% Wise Contribution to total Sales
Q2 Sep-08
Q3 Dec-08
USA
64%
66%
Europe
India
APAC/ ROW
Total
21%
5%
10%
100%
20%
7%
7%
100%
Q3 Dec-08
Q4 Mar-09
Q1 Jun-09
Q2 Sep-09
Q3 Dec-09
2008-09 A
24,097
7,231
2,426
2,626
36,380
103%
102%
92%
36%
95%
23,178
5,360
2,697
2,571
33,806
77%
37%
109%
23%
66%
21%
13%
46%
-19%
17%
67%
81%
86%
48%
69%
-4%
-26%
11%
-2%
-7%
66%
20%
7%
7%
100%
33,806
Q4 Mar-09
23,178
5,360
2,697
2,571
33,806
-----------------------------------------Q4 Mar-09
69%
80,680
24,392
7,818
10,719
123,747
69%
16%
8%
8%
100%
20,507
6,034
1,983
1,953
30,477
52%
12%
92%
-14%
37%
20,699
6,972
1,456
2,371
31,498
4%
9%
-12%
-27%
1%
-12%
13%
-26%
-24%
-10%
65%
20%
6%
9%
100%
67%
20%
7%
6%
100%
21,393
6,105
2,121
3,566
33,185
-11%
-16%
-13%
36%
-9%
4%
9%
-12%
-27%
3%
66%
22%
5%
8%
100%
-11%
-16%
-13%
36%
5%
64%
18%
6%
11%
100%
16%
8%
8%
100%
100000
0
0
0
0
-
Q4 Mar-10
2009-10 A
21,610
6,441
2,177
4,210
34,438
-7%
20%
-19%
64%
2%
4%
5%
-1%
13%
5%
-7%
20%
-19%
64%
4%
63%
19%
6%
12%
100%
84,209
25,552
7,737
12,101
129,598
2010-11 E
88,027
27,180
7,149
11,003
133,358
2011-12 E
2012-13 E
92,121
28,918
6,654
10,384
138,077
96,514
30,629
6,085
11,900
145,127
5%
6%
-8%
-9%
2.9%
5%
6%
-7%
-6%
4%
5%
6%
-7%
15%
5%
66%
20%
5%
8%
100%
67%
21%
5%
8%
100%
67%
21%
4%
8%
100%
65%
20%
6%
9%
100%
Mindtree Ltd.
Segment Wise Revenue
(All Figs in Lacs)
2007-08 A
2008-09 A
Segment Revenue
R & D Services
IT Services
AztecSoft Business
PE Services
Total
Other Income (Excluding Forex Gains/Losses)
Interest on deposits
Profit on sale of investments
Profit from sale of assets, net
Dividend from investments
Bad debts recovered
Liabilities no longer required written back
Miscellaneous income
Total
16,087
57,297
73,384
21,934
79,247
22,566
###
123,747
612
253
6
1,132
376
2,378
145
96
163
350
34
788
Minority interest
Share of P&L in Associate
Minority interest
Total Minority Interest
Sales Growth Analysis - Y-Y
R & D Services
IT Services
Aztec Business
PE Services
Total Sales
36%
38%
69%
Sales Growth Analysis - Q-Q
R & D Services
IT Services
Aztec Business
PE Services
Total Sales
232
(410)
(178)
Sales Weightage Analysis
R & D Services
IT Services
Aztec Business
PE Services
Total
22%
78%
100%
Other Income Analysis
Interest on deposits
Dividend from investments
612
1,132
As % of Closing Investment
Interest on deposits
Dividend from investments
3.70%
7%
18%
64%
18%
100%
145
350
1.44%
3.45%
2009-10 A
2010-11 E
2011-12 E
2012-13 E
69,802
59,796
129,598
143
110
357
163
143
46
962
274
777
1,051
274
777
1,051
274
777
1,051
-12%
34%
5%
54%
46%
100%
0%
143
357
1%
3%
0%
274
777
0%
274
777
274
777
Mindtree Ltd.
Cost Analysis
(Rs. In Lakhs )
Cost Structure
2007-08 A
2008-09 A
2009-10 A
Cost of Revenues
Selling, General & Administrative expenses
46,778
13,875
69,658
20,105
79,825
24,978
Total Operating Expnese(excluding bad debts)
60,653
89,763
104,803
151
806
239
60,804
90,569
105,042
Provision for bad & doubtful debts
Total Operating Expnese
As % of Revenues
Cost of Revenues
64%
56%
62%
Selling, General & Administrative expenses
19%
16%
19%
Total Operating Expnese(excluding bad debts)
83%
73%
81%
0.21%
0.65%
0.18%
Provision for bad & doubtful debts
2010-11 E
2011-12 E
2012-13 E
82,141
25,703
85,047
26,612
89,390
27,971
107,844
111,660
117,361
263
273
287
108,107
111,932
117,648
62%
62%
62%
19%
19%
19%
81%
81%
81%
0.20%
0.20%
0.20%
Mindtree Ltd.
Debt Schedule
(Rs. In Lakhs )
2007-08 A
2008-09 A
2009-10 A
2010-11 E
Debt Schedule
Secured Loans
From Banks
Term Loans
Other Loans
Finance lease obligation
Total Secured Loans
8,848
324
9,172
5,157
8,600
13,757
Unsecured Loans
Council for Scientific and Industrial Research
180
305
###
Total Unsecured Loan
180
305
305
305
305
Total
9,172
13,937
Interest Expense (Q Wise)
Interest Expense Rate (Q wise)
590
6.43%
1,620
11.62%
267
87.54%
30
9.89%
2011-12 E 2012-13 E
Mindtree Ltd.
Fixed Asset & Depreciation Schedule
Fixed Assets
Gross Block
Less Accumulated Depreciation
Net Block
Capital Work in Progress
Depreciation Schedule
Depreciation
Implied Depreciation Rate
Acquisition of Property and Equipment (Capex)
Capex as % Revenues
Notes
Projected Fixed Assets & Depreciation,
PY=Previous Year, CY=Current Year
1 Gross Block = PY Gross Block + New Capex
2 Accumulated Depreciation = PY Accumulated Dep. + CY Dep
3 Net Block = Gross Block - Accumulated Depreciation
4 Depreciation = Assuming Company buys new fixed asset half-way throu
for new fixed assets(capex) purchased in C Y.
CY Depreciation = Half Dep of New Capex + Full Depreciation from Ne
2007-08 A
2008-09 A
2009-10 A
2010-11 E 2011-12 E 2012-13 E
34,744
(11,176)
23,568
49,409
(21,096)
28,313
51,334
(25,212)
26,122
56,040
(30,896)
25,143
60,912
(36,402)
24,510
66,033
(41,806)
24,227
2,329
25,897
1,304
29,616
2,466
28,587
2,466
27,609
2,466
26,975
2,466
26,692
2007-08 A
3,493
15%
2008-09 A
5,696
20%
21,778
30%
4,346
4%
2009-10 A 2010-11 E 2011-12 E 2012-13 E
6,518
5,685
5,506
5,404
25%
20%
20%
20%
4,573
4%
4,706
4%
4,872
4%
5,121
4%
ed Dep. + CY Dep
fixed asset half-way through the year, so I have taken only half year depreciation
Full Depreciation from Net Block Of PY
Mindtree Ltd.
DCF Valuation
(All Figs in Lacs)
Growth Assumptions
Long term IT Industry growth rate
CAPM Assumptions
Ke
RFR
Beta
Rp
Enterpsire Value (EV)
15%
Current Market Price
Diluted Shares
Market Capitalisation
Long Term Liabilities
Less: Cash & Cash Equivalents
Enterprise Value (in lacks)
16.28%
7.0%
1.16
8%
Calculation of Free Cash Flow
2009-10A
Revenues
EBITDA
EBIT
Tax Expense
Net Operating Profit After Tax
Depreciation & Amortisation
Working Capital Change
Operating Cash Flow
Capex
FCF
Diluted Shares
129,598
24,556
18,038
(3,796)
14,242
6,518
6,729
14,031
(4,573)
9,458
407
Projection Year
Discount Factor
Present Value of FCF
Y/Y Growth Analysis
Revenues
EBITDA
EBIT
Depreciation & Amortisation
Margin Analysis (As % of Revenues)
EBITDA Margin
EBIT Margin
Depreciation & Amortisation
Capex
Tax Rate (Tax Expense/EBIT)
Terminal Value
Sum of PV of FCF for explicit forecast
WACC
Long term growth in Revenues
Present Value of terminal value
Terminal Value as % of Total Value
Explicit Forecast Period
2010-11 E
2011-12 E
2012-13 E
133,358
25,251
19,567
(3,069)
16,498
5,685
1,497
20,686
(4,706)
15,980
138,077
26,145
20,639
(3,233)
17,406
5,506
(770)
23,681
(4,872)
18,809
145,127
27,480
22,075
(3,447)
18,628
5,404
3,795
20,237
(5,121)
15,116
407
407
407
1
0.86
13,744
2
0.74
13,913
3
0.64
9,617
3%
3%
8%
-13%
4%
4%
5%
-3%
5%
5%
7%
-2%
19%
14%
5%
4%
19%
15%
4%
4%
19%
15%
4%
4%
19%
15%
4%
4%
-21%
-16%
-16%
-16%
73,686
16.27%
5%
105,602
59%
Equity Value
Enterprise Value
- Debt
+ Cash
Net Debt
Equity Value
Sensitivity - Varying Terminal Growth Rate
WACC
10.0%
11.0%
16.3%
13.0%
14.0%
PV of FCF
104,473
100,717
83,921
93,788
90,590
WACC
10.0%
11.0%
16.3%
13.0%
14.0%
PV of FCF
104,473
100,717
83,921
93,788
90,590
105,602
10.0%
11.0%
12.2%
13.0%
14.0%
PV of Terminal Value
2%
3%
92,943
99,770
76,846
81,885
62,204
65,827
54,504
57,463
46,562
48,895
Sensitivity - EV/EBITDA Exit Multiple
PV of Terminal Value (EV/EBITDA)
5.0
5.5
170,046
187,050
169,424
186,366
166,273
182,901
168,204
185,024
167,605
184,365
Recommendation
Mindtree is likely to clock EPS of 45.35 in FY2012 E. By assigning average PE pf IT Industry.i.e.12, we recommend a hold pos
Target Price
Industry Avg Pe
Forward EPS FY 2012 E
544
12
45.35
Debt Equity Weightage
E/(D+E) @ Enterprise Value
D/(D+E) @ Enterprise Value
513
407
208,669
305
4,033
204,942
First Stage of Valuation
2014E
2015E
155,286
29,403
23,621
(3,688)
27,309
5,782
3,795
29,297
(5,480)
23,817
166,156
31,461
25,274
(3,947)
29,221
6,187
3,795
31,613
(5,863)
25,750
Interest Rate (%)
Tax Rate (@)
WACC Calculation
WACC
12%
15%
16.27%
Second Stage Valuation
Terminal Year
2016E
177,787
33,664
27,043
(4,223)
31,266
6,620
3,795
34,091
(6,274)
27,818
407
407
407
4
0.55
13,033
5
0.47
12,119
6
0.40
11,260
186,677
35,347
28,395
(4,434)
32,829
6,951
3,795
35,986
(6,587)
29,398
407
7%
7%
7%
7%
7%
7%
7%
7%
7%
7%
7%
7%
5%
5%
5%
5%
19%
15%
4%
4%
19%
15%
4%
4%
19%
15%
4%
4%
19%
15%
4%
4%
-16%
-16%
-16%
-16%
179,288
(305)
4,033
3,727
183,015
99.85%
0.15%
Intrinsic Value
Equity Value
Diluted Shares
Intrinsic Value
183,015
407
450
Terminal Value
3%
107,573
87,553
69,843
60,718
51,441
nal Value (EV/EBITDA)
6.0
204,055
203,309
199,528
201,844
201,126
Intrinsic Value
2%
494
445
368
374
346
3%
3%
511
458
377
381
352
Intrinsic Value
5.5
684
725
673
715
624
665
653
694
644
685
.12, we recommend a hold position with target price of Rs.544/- in
530
472
387
389
358
6
767
756
706
736
726
I was unable to get current beta of company , hence I calculated beta using book method
Beta or
Input In Blue
Month
Stock R
1
2
3
4
5
6
7
8
9
10
11
12
n
12
Market R
3
2
-5
-4
0
-4
-2
11
-9
-16
7
8
Stk R
-1.023
Stk R - Stk
0.95
12
1
1
4
-3
0
7
0
-6
3
6
4
3
-4
-2
1
-3
-1
12
-8
-15
9
9
Mkt R
2.595
Formula
Covsm
Stk R - Stk R x Mkt R - Mkt R
n-1
Formula
2m
(Mkt R - Mkt R)2
n-1
Date
01-Oct-10
01-Sep-10
02-Aug-10
01-Jul-10
01-Jun-10
03-May-10
Mindtree
Close
525
512
503
531
551
551
Sensex
Return
2.54%
1.75%
-5.35%
-3.50%
0.00%
-4.24%
Date
01-Oct-10
01-Sep-10
02-Aug-10
01-Jul-10
01-Jun-10
03-May-10
01-Apr-10
02-Mar-10
01-Feb-10
04-Jan-10
01-Dec-09
03-Nov-09
22-Oct-09
22-Sep-09
575
589
533
583
695
647
601
598
-2.33%
10.58%
-8.73%
-16.10%
7.48%
7.67%
0.54%
#DIV/0!
01-Apr-10
02-Mar-10
01-Feb-10
04-Jan-10
01-Dec-09
03-Nov-09
22-Oct-09
22-Sep-09
a using book method
Covsm
Beta or
Mkt R - Mkt
Stk R - Stk
-2
9
-2
-2
1
-6
-3
4
-3
-9
0
3
R - Stk R x Mkt R - Mkt R
n-1
(Mkt R - Mkt R)2
n-1
1.16
x Mkt R - Mkt
-6
26
7
4
1
18
3
51
20
130
3
30
3
88
3
3
2
31
7
19
7
74
0
12
288
248
288
11
26
248
11
23
Sensex
20,261
20,069
17,971
17,868
17,701
16,945
(Mkt R - Mkt R)2
0.95%
12%
1%
1%
4%
-3%
17,559
17,528
16,430
16,358
17,465
16,926
15,896
16,886
0%
7%
0%
-6%
3%
6%
-6%
#DIV/0!
Profit & Loss A/c
1) Figures for FY 2007-08 are standalone including Audited Balance Sheet
2) For Q1 of 2008-09, I have taken standalone results, and from Q2 all figures are Consolidate
3) I have adjusted Share Count(Basic & Diluted) wherever necessary to align with reported E
Balance Sheet(BS)
4) 2007-08 figures are standalone, as I have taken standalone figures in Profit & Loss A/c
5) For 2008-09 Figs. For 3M,6M,9M and 12M I have taken standalone figs for Balance Sheet, a
Audited BS is Consolidated.
6) 2009-10 BS is Consolidated
Cash Flow Statement(CFS)
7) I have prepared CFS on yearly basis for simplicity, as I was getting errant figures by breaki
Geographic Revenue
8) I have done some small calculation, for converting USD* wise Geographic revenue into Rs
as Compnay did not reported consolidated figs. Geographic wise in Rs. terms.( Q1 figs. Are
by the Company. I took the Consolidated figs. From the fact sheet of respective quarters, a
to that respective quarters.(calculation is below the Geographic revenues break-up figure
* Despite taking exchange rates for calculation, I was not getting the accurate figs
Segment Revenue
9) Upto Q1 of 2008-09, all figs. are standalone, after that all figs. are consolidated
From FY 09-10, Segment R & D Services and AztecSoft Business is grouped under PE Servi
Hence, while calculating YoY & QoQ growth in % for 2009-10, I have grouped R&D Serv. an
Projection Assumptions
11) Share Capital = is projected on as it is/straight line basis
Debt = Assumed that it will be paid in 2010-11, hence not shown in closing balance
Fixed Asset = Detailed working is in Fixed Asset Sheet
Goodwill, Investments & Tax Assets = straight line basis
Sundry Debtors = is calculated by ; Avg. Days Sales O/s * (Projected Revenue/365)
Cash = linked from Cash Flow St.
Loans & Advances = is calculated by ; Avg % of Loans to Revenues * Projected Revenues
Current Liabilities = is calculated by ; Avg % of CL to Cost of Revenues * Projected Cost of
Provisions = straight line basis
2009-10 Annual Report Excerpts
1 As per NASSCOM, Indian IT exports is estimated to grow by 5.5% in
2 EBITDA margins on our services business is likely to be stable.
3 effective tax rate based on current taxes is about 15.6% as
compared to about 11% in the previous year.
4 NiW form part of PE Services
5 Our NIW, business is developing a ready to brand 3G smart phone
6 On our services business, based on current trends, we expect that
than industry growth estimates of 13-15%
7 pg 51 for report
ance Sheet
m Q2 all figures are Consolidated, including Audited Balance Sheet
essary to align with reported EPS by the company, as all figures in the model are in Lacs.
figures in Profit & Loss A/c
dalone figs for Balance Sheet, as Consolidated Figs. were not provided by the company, however
getting errant figures by breaking it into periods/quarters
se Geographic revenue into Rs. wise Geographic revenue for consolidated figs, from Q2-2008-09,
wise in Rs. terms.( Q1 figs. Are standalone, as consolidated break-up of Geograhic Revenues were not provided
sheet of respective quarters, and applied the USD region wise weighted % to total figure of reported Revenues
phic revenues break-up figures)
etting the accurate figs
gs. are consolidated
ness is grouped under PE Services.
0, I have grouped R&D Serv. and Aztec under PE Services
hown in closing balance
rojected Revenue/365)
venues * Projected Revenues
f Revenues * Projected Cost of Revenues
s estimated to grow by 5.5% in FY2010
ness is likely to be stable.
axes is about 15.6% as
ady to brand 3G smart phone and IP in the area of cellular infrastructure. We expect to launch these products to
current trends, we expect that we will attain growth rates higher
ny, however
Q2-2008-09,
evenues were not provided
gure of reported Revenues
to launch these products to the market in Q3/Q4 of 2010-11. Since this is a new initiative, the financial perform
nitiative, the financial performance of the NIW business would depend onthe success of the products when they
cess of the products when they are launched.If these initiatives are successful, they will greatly enhance our rev
ey will greatly enhance our revenues and profitability
