DCF Valuation Exercise

Download as xlsx, pdf, or txt
Download as xlsx, pdf, or txt
You are on page 1of 18

1 Estimate the free cash flow to firm and free cash flow to equity during 2013-14 to 2017-18.

(INR million) Mar-14 Mar-15 Mar-16


Net sales 134,120 127,853 118,486
Operating expenses 115,365 108,547 98,511
EBIDTA 18,755 19,306 19,975
Depreciation 4,109 3,883 4,268
EBIT 14,646 15,423 15,707
Interest 2,838 1,828 926
Non-operating income 979 538 680
Non-recurring income -468 -825 478
EBT 12,319 13,308 15,939
Tax 2,269 3,532 4,677
Net Income (PAT) 10,050 9,776 11,262

(INR million) Mar-14 Mar-15 Mar-16


Fixed Assets (net) 45,022 44,867 60,090
Current assets (excluding cash) 35,178 30,138 35,062
Cash & marketable securities 6,541 6,946 10,960
Current liabilities (excluding short term debt) 22,338 15,265 23,783
Total Debt 9,889 8,006 13,892
Equity 45,746 50,423 66,046

(INR million) Mar-14 Mar-15 Mar-16


Effective tax rate = Tax/EBT (%)
NOPAT = EBIT*(1-t)
Capex = ∆Net Fixed Assets + Dep
Net Working Capital = CA-CL
Change in Net Wkg Cap
Net reinvestment = [Capex - Dep] + ∆NWC

Free Cash Flow to Firm (FCFF) = NOPAT-Net reinvt


Free Cash Flow to Equity (FCFE) = PAT-Net reinvt+∆Debt

2 Forecast free cash cash flow to firm for 10 years under the following assumptions

(INR million) Mar-14 Mar-15 Mar-16


Sales growth (%) -4.7% -7.3%
EBIDTA margin (%) 14.0% 15.1% 16.9%
Dep/FA (beg.) 8.6% 9.5%
Tax/EBT (%) 18.4% 26.5% 29.3%
Capex/FA beg. (%) 0.0% 0.0%
Current assets/Sales 0.26 0.24 0.30
Current liablities/Operating Expenses 0.19 0.14 0.24
(INR million) Mar-14 Mar-15 Mar-16
Sales 134,120 127,853 118,486
EBIDTA 18,755 19,306 19,975
Operating expenses 115,365 108,547 98,511
Capex 0 0
Depreciation 4,109 3,883 4,268
Fixed Assets 45,022 44,867 60,090
Sales/Avg FA 2.84 2.26
EBIT 14,646 15,423 15,707

Current assets 35,178 30,138 35,062


Current liabilities 22,338 15,265 23,783
Net working capital 12,840 14,873 11,279
Change in NWC 2,033 -3,594

NOPAT
Net reinvestment
FCFF

Invested Capital = Net fixed assets + Working capital


Sales/Avg. Invested Capital
RoIC = NOPAT/Invested Capital
Reinvestment rate = Net reinvestment/NOPAT
Sustainable growth = RoIC x Reinvestment rate

3 Estimate the weighted-average cost of capital based on following assumptions

Risk-free rate 6.50%


Credit rating AA+
YTM for AA+ 10 year bonds 9.00%
Marginal tax rate 30%

Equity market risk premium 6.50%


Unlevered beta 0.84

Share price as on 1/4/2018 285.6


No. of shares 572.1
Market value of equity 163,392
Market value of debt 44,457 Assumed to be equal to book value

Cost of debt (post-tax)

Levered beta
Cost of equity

Weight of debt
Weight of equity

Weighted-average cost of capital

4 Estimate the terminal value of the firm after 10 years


(INR million)
Terminal Period growth rate in NOPAT 6.5%
Terminal Period WACC 0.0%
Terminal Period Excess Return = ROIC - WACC 0.0%
Terminal Period RoIC 0.0%

Terminal reinvestment rate


NOPAT in last year of explicit forecast period
NOPAT in first year of terminal growth period
Reinvestment in first year of terminal growth period
FCFF in first year of terminal growth period
Terminal Value of the firm

5 Determine intrinsic value of equity at the end of March 2018 and compare it with share price

(INR million) Mar-14 Mar-15 Mar-16


Periods from valuation date
FCFF
Terminal Value
Discount factor = 1/(1+k)^n
Present value = Cash flow x Discount Factor

Discounted FCFF
Add cash & marketable securities
Less debt
Total value of equity
Intrinsic value per share
Share price
Premium/discount to intrinsic value
Mar-17 Mar 18
131,800 148,405
113,313 131,893
18,487 16,512
4,618 5,926
13,869 10,586
1,029 1,629
1,518 1,165
0 0
14,358 10,122
3,366 2,885
10,992 7,237

Mar-17 Mar 18
94,292 125,011
42,868 49,607
7,314 19,382
29,977 38,784
32,445 44,457
72,900 97,767

Mar-17 Mar 18

Mar-17 Mar-18 Mar-19 Mar-20 Mar-21 Mar-22 Mar-23 Mar-24 Mar-25


11.2% 12.6% 20.0% 15.0% 15.0% 12.0% 12.0% 11.7% 11.4%
14.0% 11.1% 12.0% 14.0% 15.0% 15.0% 15.0% 15.0% 15.0%
7.7% 6.3% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0%
23.4% 28.5% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0%
0.0% 0.0% 15.0% 8.0% 8.0% 8.0% 20.0% 19.8% 19.6%
0.33 0.33 0.30 0.30 0.30 0.30 0.30 0.30 0.30
0.26 0.29 0.24 0.24 0.24 0.24 0.24 0.24 0.24
Mar-17 Mar-18 Mar-19 Mar-20 Mar-21 Mar-22 Mar-23 Mar-24 Mar-25
131,800 148,405
18,487 16,512
113,313 131,893
0 0
4,618 5,926
94,292 125,011
1.71 1.35
13,869 10,586

42,868 49,607
29,977 38,784
12,891 10,823
1,612 -2,068

equal to book value


Mar-17 Mar-18 Mar-19 Mar-20 Mar-21 Mar-22 Mar-23 Mar-24 Mar-25
Mar-26 Mar-27 Mar-28
11.1% 10.8% 10.5%
15.0% 15.0% 15.0%
8.0% 8.0% 8.0%
30.0% 30.0% 30.0%
19.4% 19.2% 19.0%
0.30 0.30 0.30
0.24 0.24 0.24
Mar-26 Mar-27 Mar-28
Mar-26 Mar-27 Mar-28
1 Estimate the free cash flow to firm and free cash flow to equity during 2013-14 to 2017-18.

(INR million) Mar-14 Mar-15 Mar-16


Net sales 134,120 127,853 118,486
Operating expenses 115,365 108,547 98,511
EBIDTA 18,755 19,306 19,975
Depreciation 4,109 3,883 4,268
EBIT 14,646 15,423 15,707
Interest 2,838 1,828 926
Non-operating income 979 538 680
Non-recurring income -468 -825 478
EBT 12,319 13,308 15,939
Tax 2,269 3,532 4,677
Net Income (PAT) 10,050 9,776 11,262

(INR million) Mar-14 Mar-15 Mar-16


Fixed Assets (net) 45,022 44,867 60,090
Current assets (excluding cash) 35,178 30,138 35,062
Cash & marketable securities 6,541 6,946 10,960
Current liabilities (excluding short term debt) 22,338 15,265 23,783
Total Debt 9,889 8,006 13,892
Equity 45,746 50,423 66,046

(INR million) Mar-14 Mar-15 Mar-16


Effective tax rate = Tax/EBT (%) 18.4% 26.5% 29.3%
NOPAT = EBIT*(1-t) 11,948 11,330 11,098
Capex = ∆Net Fixed Assets + Dep 3,728 19,491
Net Working Capital = CA-CL 12,840 14,873 11,279
Change in Net Wkg Cap 2,033 -3,594
Net reinvestment = [Capex - Dep] + ∆NWC 1,878 11,629

Free Cash Flow to Firm (FCFF) = NOPAT-Net reinvt 9,452 -531


Free Cash Flow to Equity (FCFE) = PAT-Net reinvt+∆Debt 6,015 5,519

2 Forecast free cash cash flow to firm for 10 years under the following assumptions

(INR million) Mar-14 Mar-15 Mar-16


Sales growth (%) -4.7% -7.3%
EBIDTA margin (%) 14.0% 15.1% 16.9%
Dep/FA (beg.) 8.6% 9.5%
Tax/EBT (%) 18.4% 26.5% 29.3%
Capex/FA beg. (%) 8.3% 43.4%
Current assets/Sales 0.26 0.24 0.30
Current liablities/Operating Expenses 0.19 0.14 0.24
(INR million) Mar-14 Mar-15 Mar-16
Sales 134,120 127,853 118,486
EBIDTA 18,755 19,306 19,975
Operating expenses 115,365 108,547 98,511
Capex 3,728 19,491
Depreciation 4,109 3,883 4,268
Fixed Assets 45,022 44,867 60,090
Sales/Avg FA 2.84 2.26
EBIT 14,646 15,423 15,707

Current assets 35,178 30,138 35,062


Current liabilities 22,338 15,265 23,783
Net working capital 12,840 14,873 11,279
Change in NWC 2,033 -3,594

NOPAT 11,948 11,330 11,098


Net reinvestment 1878 11629
FCFF 9,452 -531

Invested Capital = Net fixed assets + Working capital 57,862 59,740 71,369
Sales/Avg. Invested Capital 2.17 1.81
RoIC = NOPAT/Invested Capital 19.3% 16.9%
Reinvestment rate = Net reinvestment/NOPAT 16.6% 104.8%
Sustainable growth = RoIC x Reinvestment rate 3.2% 17.7%

3 Estimate the weighted-average cost of capital based on following assumptions

Risk-free rate 6.50%


Credit rating AA+
YTM for AA+ 10 year bonds 9.00%
Marginal tax rate 30%

Equity market risk premium 6.50%


Unlevered beta 0.84

Share price as on 1/4/2018 285.6


No. of shares 572.1
Market value of equity 163,392
Market value of debt 44,457 Assumed to be equal to book value

Cost of debt (post-tax) 6.3%

Levered beta 1.00


Cost of equity 13.00%

Weight of debt 0.21


Weight of equity 0.79

Weighted-average cost of capital 11.6%

4 Estimate the terminal value of the firm after 10 years


(INR million)
Terminal Period growth rate in NOPAT 6.5%
Terminal Period WACC 11.6%
Terminal Period Excess Return = ROIC - WACC 0.0%
Terminal Period RoIC 11.6%

Terminal reinvestment rate 56.2%


NOPAT in last year of explicit forecast period 39,539
NOPAT in first year of terminal growth period 42,109
Reinvestment in first year of terminal growth period 23,663
FCFF in first year of terminal growth period 18,446
Terminal Value of the firm 364,046

5 Determine intrinsic value of equity at the end of March 2018 and compare it with share price

(INR million) Mar-14 Mar-15 Mar-16


Periods from valuation date
FCFF
Terminal Value
Discount factor = 1/(1+k)^n
Present value = Cash flow x Discount Factor

Discounted FCFF
Add cash & marketable securities
Less debt
Total value of equity
Intrinsic value per share
Share price
Premium/discount to intrinsic value
Mar-17 Mar 18
131,800 148,405
113,313 131,893
18,487 16,512
4,618 5,926
13,869 10,586
1,029 1,629
1,518 1,165
0 0
14,358 10,122
3,366 2,885
10,992 7,237

Mar-17 Mar 18
94,292 125,011
42,868 49,607
7,314 19,382
29,977 38,784
32,445 44,457
72,900 97,767

Mar-17 Mar 18
23.4% 28.5%
10,618 7,569
38,820 36,645
12,891 10,823
1,612 -2,068
35,814 28,651

-25,196 -21,082
-6,269 -9,402

Mar-17 Mar-18 Mar-19 Mar-20 Mar-21 Mar-22 Mar-23 Mar-24 Mar-25


11.2% 12.6% 20.0% 15.0% 15.0% 12.0% 12.0% 11.7% 11.4%
14.0% 11.1% 12.0% 14.0% 15.0% 15.0% 15.0% 15.0% 15.0%
7.7% 6.3% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0%
23.4% 28.5% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0%
64.6% 38.9% 15.0% 8.0% 8.0% 8.0% 20.0% 19.8% 19.6%
0.33 0.33 0.30 0.30 0.30 0.30 0.30 0.30 0.30
0.26 0.29 0.24 0.24 0.24 0.24 0.24 0.24 0.24
Mar-17 Mar-18 Mar-19 Mar-20 Mar-21 Mar-22 Mar-23 Mar-24 Mar-25
131,800 148,405 178,086 204,799 235,519 263,781 295,435 330,001 367,621
18,487 16,512 21,370 28,672 35,328 39,567 44,315 49,500 55,143
113,313 131,893 156,716 176,127 200,191 224,214 251,119 280,500 312,478
38,820 36,645 18,752 10,701 10,701 10,701 26,752 29,663 32,828
4,618 5,926 10,001 10,701 10,701 10,701 10,701 11,985 13,399
94,292 125,011 133,762 133,762 133,762 133,762 149,813 167,491 186,920
1.71 1.35 1.38 1.53 1.76 1.97 2.08 2.08 2.07
13,869 10,586 11,369 17,971 24,627 28,866 33,614 37,515 41,744

42,868 49,607 53,426 61,440 70,656 79,134 88,630 99,000 110,286


29,977 38,784 37,612 42,270 48,046 53,811 60,269 67,320 74,995
12,891 10,823 15,814 19,169 22,610 25,323 28,362 31,680 35,292
1,612 -2,068 4,991 3,355 3,441 2,713 3,039 3,318 3,612

10,618 7,569 7,959 12,580 17,239 20,206 23,530 26,261 29,221


35814 28651 13,742 3,355 3,441 2,713 19,090 20,996 23,040
-25,196 -21,082 -5,783 9,224 13,798 17,493 4,440 5,264 6,180

107,183 135,834 149,576 152,931 156,372 159,085 178,175 199,171 222,212


1.48 1.22 1.25 1.35 1.52 1.67 1.75 1.75 1.74
11.9% 6.2% 5.6% 8.3% 11.1% 12.8% 14.0% 13.9% 13.9%
337.3% 378.5% 172.7% 26.7% 20.0% 13.4% 81.1% 80.0% 78.9%
40.1% 23.6% 9.6% 2.2% 2.2% 1.7% 11.3% 11.1% 10.9%

equal to book value


Mar-17 Mar-18 Mar-19 Mar-20 Mar-21 Mar-22 Mar-23 Mar-24 Mar-25
0.50 1.50 2.50 3.50 4.50 5.50 6.50
-5,783 9,224 13,798 17,493 4,440 5,264 6,180

0.947 0.849 0.761 0.682 0.611 0.548 0.491


-5,475 7,828 10,495 11,926 2,713 2,883 3,034

171,900
19,382
44,457
146,825
256.64
285.60
11.3%
Mar-26 Mar-27 Mar-28
11.1% 10.8% 10.5%
15.0% 15.0% 15.0%
8.0% 8.0% 8.0%
30.0% 30.0% 30.0%
19.4% 19.2% 19.0%
0.30 0.30 0.30
0.24 0.24 0.24
Mar-26 Mar-27 Mar-28
408,427 452,537 500,053
61,264 67,880 75,008
347,163 384,656 425,045
36,263 39,980 43,995
14,954 16,658 18,524
208,229 231,551 257,021
2.07 2.06 2.05
46,310 51,222 56,484

122,528 135,761 150,016


83,319 92,317 102,011
39,209 43,444 48,005
3,917 4,235 4,562

32,417 35,856 39,539


25,226 27,556 30,032
7,191 8,299 9,507

247,438 274,994 305,026


1.74 1.73 1.72
13.8% 13.7% 13.6%
77.8% 76.9% 76.0%
10.7% 10.5% 10.4%
Mar-26 Mar-27 Mar-28
7.50 8.50 9.50
7,191 8,299 9,507
364,046
0.440 0.394 0.354
3,164 3,273 132,059

You might also like