Profit and loss form
EXCEL FILE:
>Pro Forma Financial Statements, (BS/IS) forecast from 2017-2021(include Accounts Receivable Turnover, Accounts Payable Turnover, PPE Turnover, and Inventory Turnover)
>Indirect Valuation Method (Enterprise Value) (WACC)
>Direct Valuation Method (Free Cash Flow to Firm, include entity value, equity value, equity value per share)]
Include EXPLANATIONS of valuations.
Solution
Profit and Loss
Profit and Loss Statement |
|
|
|
|
Actual |
Projection |
Applicable value for forecasting |
|
|
USD Million |
|
Dec-13 |
Dec-14 |
Dec-15 |
Dec-16 |
Dec-17 |
Dec-18 |
Dec-19 |
Dec-20 |
Dec-21 |
|
Rates |
|
|
|
Revenue |
|
713.8 |
748.7 |
810.9 |
828.9 |
871.3 |
915.8 |
962.6 |
1011.7 |
1063.4 |
|
0.1 |
CAGR |
|
|
Cost of Goods Sold |
|
155.9 |
159.2 |
161.4 |
148.6 |
176.3 |
185.3 |
194.8 |
204.7 |
215.2 |
|
0.0 |
CAGR |
|
|
Gross Profit |
|
557.9 |
589.5 |
649.5 |
680.3 |
695.0 |
730.5 |
767.8 |
807.0 |
848.3 |
|
0.1 |
CAGR |
|
|
Gross Margin % |
|
0.8 |
0.8 |
0.8 |
0.8 |
0.8 |
0.8 |
0.8 |
0.8 |
0.8 |
|
0.8 |
Average |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Selling, General, & Admin. Expense |
|
230.8 |
226.3 |
243.8 |
246.8 |
252.4 |
258.1 |
263.9 |
269.9 |
276.0 |
|
0.0 |
CAGR |
|
|
Advertising |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Research & Development |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other Operating Expense |
|
22.4 |
24.3 |
86.2 |
18.8 |
17.7 |
16.7 |
15.8 |
14.9 |
14.0 |
|
-0.1 |
CAGR |
|
|
Operating Income |
|
304.7 |
338.9 |
319.5 |
414.7 |
424.9 |
455.7 |
488.1 |
522.3 |
558.3 |
|
|
|
|
Operating Margin % |
|
0.4 |
0.5 |
0.4 |
0.5 |
0.5 |
0.5 |
0.5 |
0.5 |
0.5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Interest Income |
|
0.4 |
0.3 |
0.4 |
0.6 |
0.4 |
0.4 |
0.4 |
0.4 |
0.4 |
|
0.4 |
Average |
|
Interest Expense |
|
-80.2 |
-68.1 |
-96.8 |
-100.9 |
-98.5 |
-98.5 |
-98.5 |
-98.5 |
-98.5 |
|
4% |
Average cost of debt |
Net Interest Income |
|
-79.8 |
-67.8 |
-96.4 |
-100.3 |
-98.1 |
-98.1 |
-98.1 |
-98.1 |
-98.1 |
|
|
|
|
Other Income (Expense) |
|
-6.8 |
-15.3 |
-21.6 |
-1.2 |
0.0 |
0.0 |
0.0 |
0.0 |
0.0 |
|
0.0 |
Assumption |
Other Income (Minority Interest) |
|
0.6 |
0.8 |
0.0 |
|
0.0 |
0.0 |
0.0 |
0.0 |
0.0 |
|
0.0 |
Assumption |
Pre-Tax Income |
|
218.1 |
255.8 |
201.5 |
313.2 |
326.8 |
357.6 |
390.0 |
424.2 |
460.2 |
|
|
|
|
Tax Provision |
|
-71.8 |
-80.2 |
-96.4 |
-117.7 |
-122.3 |
-133.8 |
-146.0 |
-158.8 |
-172.2 |
|
|
|
|
Tax Rate % |
|
0.3 |
0.3 |
0.5 |
0.4 |
0.4 |
0.4 |
0.4 |
0.4 |
0.4 |
|
0.4 |
Average Tax rate |
Net Income (Continuing Operations) |
|
146.3 |
175.6 |
105.1 |
195.5 |
204.5 |
223.8 |
244.1 |
265.4 |
288.0 |
|
|
|
|
Net Income (Discontinued Operations) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Net Income |
|
146.9 |
176.4 |
105.1 |
195.5 |
204.5 |
223.8 |
244.1 |
265.4 |
288.0 |
|
|
|
|
Net Margin % |
|
0.2 |
0.2 |
0.1 |
0.2 |
0.2 |
0.2 |
0.3 |
0.3 |
0.3 |
|
|
|
|
Balancesheet
Balance Sheet |
|
|
|
Actual |
Projection |
Applicable value for forecasting |
|
USD Million |
|
Dec-13 |
Dec-14 |
Dec-15 |
Dec-16 |
Dec-17 |
Dec-18 |
Dec-19 |
Dec-20 |
Dec-21 |
|
Rates |
|
|
Cash And Cash Equivalents |
|
256.9 |
208.1 |
260.4 |
361.4 |
590.6 |
849.4 |
1130.6 |
1434.4 |
1764.4 |
|
|
|
|
Marketable Securities |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Cash, Cash Equivalents, Marketable Securities |
|
256.9 |
208.1 |
260.4 |
361.4 |
590.6 |
849.4 |
1130.6 |
1434.4 |
1764.4 |
|
|
|
|
Accounts Receivable |
|
47.2 |
55.9 |
53.1 |
44.5 |
56.6 |
59.5 |
62.6 |
65.8 |
69.1 |
6.5% |
As percentage of sales |
|
Inventories, Raw Materials & Components |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Inventories, Work In Process |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Inventories, Inventories Adjustments |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Inventories, Finished Goods |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Inventories, Other |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Total Inventories |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other Current Assets |
|
157.7 |
129.4 |
244.3 |
200.4 |
217.1 |
235.1 |
254.7 |
275.8 |
298.8 |
8.3% |
|
|
|
Total Current Assets |
|
461.8 |
393.4 |
557.8 |
606.3 |
864.3 |
1144.1 |
1447.8 |
1776.0 |
2132.3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Investments And Advances |
|
170.6 |
164.5 |
106.9 |
114.7 |
139.2 |
131.3 |
123.0 |
127.1 |
130.1 |
|
Moving average |
|
Land And Improvements |
|
34.1 |
33.9 |
33.3 |
34.9 |
34.9 |
34.9 |
34.9 |
34.9 |
34.9 |
0% |
No escalation |
|
Buildings And Improvements |
|
47.9 |
49.5 |
49.3 |
50.6 |
51.5 |
52.5 |
53.5 |
54.4 |
55.4 |
2% |
CAGR |
|
|
Machinery, Furniture, Equipment |
|
43.1 |
49.3 |
53.3 |
52.9 |
56.6 |
60.6 |
64.9 |
69.5 |
74.4 |
7% |
CAGR |
|
|
Construction In Progress |
|
9.1 |
5.7 |
3.8 |
5.2 |
6.0 |
5.2 |
5.0 |
5.3 |
5.4 |
|
Moving average |
|
Gross Property, Plant and Equipment |
|
288.7 |
286.5 |
294.2 |
301.3 |
305.6 |
310.0 |
314.4 |
319.0 |
323.5 |
1% |
CAGR |
|
|
Accumulated Depreciation |
|
-105.8 |
-104.4 |
-111.6 |
-124.7 |
-131.7 |
-139.1 |
-147.0 |
-155.3 |
-164.0 |
6% |
|
|
|
Property, Plant and Equipment |
|
182.9 |
182.1 |
182.6 |
176.6 |
173.9 |
170.9 |
167.5 |
163.7 |
159.5 |
|
|
|
|
Intangible Assets |
|
2343.8 |
2317.2 |
2290.8 |
2267.0 |
2242.0 |
2217.2 |
2192.7 |
2168.5 |
2144.6 |
-1% |
|
|
|
Goodwill |
|
891.6 |
891.4 |
889.6 |
888.3 |
888.3 |
888.3 |
888.3 |
888.3 |
888.3 |
0% |
|
|
|
Other Long Term Assets |
|
75.6 |
67.3 |
59.0 |
62.6 |
62.6 |
62.6 |
62.6 |
62.6 |
62.6 |
0% |
|
|
|
Total Assets |
|
3234.7 |
3124.5 |
3197.1 |
3227.2 |
3481.9 |
3726.0 |
3993.6 |
4297.8 |
4629.1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Accounts Payable |
|
12.4 |
13.8 |
18.7 |
12.7 |
14.4 |
14.9 |
15.2 |
14.3 |
14.7 |
|
Moving average |
|
Total Tax Payable |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other Accrued Expense |
|
66.3 |
64.0 |
62.1 |
43.5 |
37.8 |
32.8 |
28.5 |
24.8 |
21.6 |
-13% |
CAGR |
|
|
Accounts Payable & Accrued Expense |
|
78.7 |
77.8 |
80.8 |
56.2 |
52.2 |
47.7 |
43.7 |
39.1 |
36.2 |
|
|
|
|
Current Portion of Long-Term Debt |
|
5.4 |
4.4 |
25.5 |
25.6 |
25.6 |
25.6 |
25.6 |
25.6 |
25.6 |
|
|
|
|
Current Deferred Revenue |
|
28.4 |
30.4 |
31.5 |
35.4 |
35.4 |
35.4 |
35.4 |
35.4 |
35.4 |
|
|
|
|
Current Deferred Taxes Liabilities |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DeferredTaxAndRevenue |
|
28.4 |
30.4 |
31.5 |
35.4 |
35.4 |
35.4 |
35.4 |
35.4 |
35.4 |
|
|
|
|
Other Current Liabilities |
|
231.7 |
243.0 |
280.9 |
307.0 |
337.2 |
370.4 |
406.8 |
446.8 |
490.7 |
10% |
CAGR |
|
|
Total Current Liabilities |
|
344.2 |
355.6 |
418.7 |
424.2 |
450.4 |
479.1 |
511.5 |
546.9 |
588.0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Long-Term Debt |
|
1818.6 |
1795.6 |
2420.6 |
2402.0 |
2402.0 |
2402.0 |
2402.0 |
2402.0 |
2402.0 |
|
|
|
|
Capital Lease Obligation |
|
7.0 |
7.6 |
7.5 |
7.6 |
7.6 |
7.6 |
7.6 |
7.6 |
7.6 |
|
|
|
|
Long-Term Debt & Capital Lease Obligation |
|
1825.6 |
1803.2 |
2428.1 |
2409.6 |
2409.6 |
2409.6 |
2409.6 |
2409.6 |
2409.6 |
|
|
|
|
Debt-to-Equity |
|
4.5 |
4.9 |
-11.1 |
-14.9 |
36.7 |
8.4 |
4.6 |
3.0 |
2.2 |
|
|
|
|
PensionAndRetirementBenefit |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
NonCurrent Deferred Liabilities |
|
572.8 |
513.7 |
492.1 |
474.0 |
445.0 |
417.8 |
392.2 |
368.3 |
345.7 |
-6% |
CAGR |
|
|
Minority Interest |
|
|
|
0.2 |
|
0.0 |
0.0 |
0.0 |
0.0 |
0.0 |
|
|
|
|
Other Long-Term Liabilities |
|
84.6 |
84.0 |
78.8 |
82.9 |
110.6 |
129.5 |
146.2 |
173.5 |
198.3 |
|
May be used for balancing |
|
Total Liabilities |
|
2827.2 |
2756.5 |
3417.7 |
3390.7 |
3415.6 |
3436.0 |
3459.5 |
3498.2 |
3541.6 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Common Stock |
|
0.1 |
0.1 |
0.1 |
0.1 |
0.1 |
0.1 |
0.1 |
0.1 |
0.1 |
|
|
|
|
Preferred Stock |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Retained Earnings |
|
-779.7 |
-711.5 |
-1076.5 |
-945.8 |
-741.3 |
-517.5 |
-273.5 |
-8.0 |
279.9 |
|
|
|
|
Accumulated other comprehensive income (loss) |
|
1.3 |
-14.0 |
-20.0 |
-24.0 |
0.0 |
0.0 |
0.0 |
0.0 |
0.0 |
|
|
|
|
Additional Paid-In Capital |
|
1196.4 |
1093.4 |
876.6 |
807.5 |
807.5 |
807.5 |
807.5 |
807.5 |
807.5 |
|
|
|
|
Treasury Stock |
|
-10.8 |
|
-1.1 |
-1.1 |
0.0 |
0.0 |
0.0 |
0.0 |
0.0 |
|
|
|
|
Total Equity |
|
407.3 |
368.0 |
-220.9 |
-163.3 |
66.3 |
290.1 |
534.1 |
799.6 |
1087.5 |
|
|
|
|
Equity-to-Asset |
|
0.1 |
0.1 |
-0.1 |
-0.1 |
0.0 |
0.1 |
0.1 |
0.2 |
0.2 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Check |
|
ok |
ok |
ok |
ok |
ok |
ok |
ok |
ok |
ok |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Working capital |
|
-139.3 |
-170.3 |
-121.3 |
-179.3 |
-176.7 |
-184.5 |
-194.3 |
-205.3 |
-220.1 |
|
|
|
|
Change in working capital |
|
|
-31.0 |
49.0 |
-58.0 |
2.6 |
-7.8 |
-9.8 |
-11.0 |
-14.8 |
|
|
|
Cashflows
Cash flows |
|
|
|
Actual |
Projection |
Applicable value for forecasting |
|
USD Million |
|
Dec-13 |
Dec-14 |
Dec-15 |
Dec-16 |
Dec-17 |
Dec-18 |
Dec-19 |
Dec-20 |
Dec-21 |
|
Rates |
|
|
Profit |
|
146.9 |
176.4 |
105.1 |
195.5 |
204.5 |
223.8 |
244.1 |
265.4 |
288.0 |
|
|
|
|
Depreciation |
|
49.4 |
45.5 |
45.2 |
42.5 |
42.5 |
42.5 |
42.5 |
42.5 |
42.5 |
|
|
|
|
Interest *(1-t) |
|
50.2 |
42.6 |
60.6 |
63.1 |
61.6 |
61.6 |
61.6 |
61.6 |
61.6 |
|
|
|
|
Change in working capital |
|
39.9 |
-14.6 |
-37.9 |
47.8 |
2.6 |
-7.8 |
-9.8 |
-11.0 |
-14.8 |
|
|
|
|
Capex |
|
-31.1 |
-23.6 |
-30.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
|
Same as historical rate |
|
FCFF |
|
175.5 |
255.5 |
218.6 |
238.1 |
290.8 |
320.5 |
342.8 |
365.4 |
391.7 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Calculation of FCFE |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Profit |
|
146.9 |
176.4 |
105.1 |
195.5 |
204.5 |
223.8 |
244.1 |
265.4 |
288.0 |
|
|
|
|
Depreciation |
|
49.4 |
45.5 |
45.2 |
42.5 |
42.5 |
42.5 |
42.5 |
42.5 |
42.5 |
|
|
|
|
Net debt raised |
|
24.2 |
-15 |
662.2 |
-25 |
0 |
0 |
0 |
0 |
0 |
|
Debt repaid = debt raised , Since capital is les sso manageble |
|
Change in working capital |
|
39.9 |
-14.6 |
-37.9 |
47.8 |
2.6 |
-7.8 |
-9.8 |
-11.0 |
-14.8 |
|
|
|
|
Capex |
|
-31.1 |
-23.6 |
-30.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
|
|
|
|
FCFE |
|
149.5 |
197.9 |
820.2 |
150.0 |
229.2 |
258.8 |
281.2 |
303.8 |
330.1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Acuumulated Cash |
|
256.9 |
208.1 |
260.4 |
361.4 |
590.6 |
849.4 |
1130.6 |
1434.4 |
1764.4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Ratio
Cash flows |
|
|
|
Key Ratio |
|
|
|
Actual |
Projection |
Applicable value for forecasting |
|
|
|
Dec-13 |
Dec-14 |
Dec-15 |
Dec-16 |
Dec-17 |
Dec-18 |
Dec-19 |
Dec-20 |
Dec-21 |
|
Rates |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Debt to Equity Ratio |
|
4.5 |
4.9 |
-11.1 |
-14.9 |
36.7 |
8.4 |
4.6 |
3.0 |
2.2 |
|
Total debt/Total Equity |
|
Equity-to-Asset |
|
0.1 |
0.1 |
-0.1 |
-0.1 |
0.0 |
0.1 |
0.1 |
0.2 |
0.2 |
|
Total equity/ Total asset |
|
Current Ratio |
|
1.3 |
1.1 |
1.3 |
1.4 |
1.9 |
2.4 |
2.8 |
3.2 |
3.6 |
|
|
|
|
Account receivable turnover |
|
15.1 |
14.5 |
14.9 |
17.0 |
17.2 |
15.8 |
15.8 |
15.8 |
15.8 |
|
|
|
|
Account payable turnover |
|
12.6 |
12.2 |
9.9 |
9.5 |
13.0 |
12.6 |
13.0 |
13.9 |
14.8 |
|
|
|
|
Number of days of accounts receivable |
|
24.1 |
25.1 |
24.5 |
21.5 |
21.2 |
23.1 |
23.1 |
23.1 |
23.1 |
|
|
|
|
Number of days of accounts payable |
|
29.0 |
30.0 |
36.7 |
38.6 |
28.1 |
28.9 |
28.2 |
26.3 |
24.6 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Wacc
WACC |
|
|
|
As per Google Finance |
|
|
Date |
Nasdaq Composite |
Percentage Change |
Date |
DNKN Price |
Percentage Change |
|
|
|
|
Range |
55.66 – 56.70 |
|
5/23/2016 |
4933.50 |
0.18% |
5/23/2016 |
43.650 |
1.00% |
|
|
|
|
52 week Range |
41.29 – 58.43 |
|
5/30/2016 |
4942.52 |
0.18% |
5/30/2016 |
44.090 |
1.0% |
|
|
|
|
Open |
56.32 |
|
6/6/2016 |
4894.55 |
-0.97% |
6/6/2016 |
45.180 |
2.5% |
|
|
|
|
Vol / Avg. |
0.00/1.40M |
|
6/13/2016 |
4800.34 |
-1.92% |
6/13/2016 |
44.980 |
-0.4% |
|
|
|
|
Mkt cap |
5.13B |
|
6/20/2016 |
4707.98 |
-1.92% |
6/20/2016 |
42.320 |
-5.9% |
|
|
|
|
P/E |
25 |
|
6/27/2016 |
4862.57 |
3.28% |
6/27/2016 |
44.240 |
4.5% |
|
|
|
|
Div/yield |
0.32/2.32 |
|
7/4/2016 |
4956.76 |
1.94% |
7/4/2016 |
44.550 |
0.7% |
|
|
|
|
EPS |
2.23 |
|
7/11/2016 |
5029.59 |
1.47% |
7/11/2016 |
46.600 |
4.6% |
|
|
|
|
Shares |
92.16M |
|
7/18/2016 |
5100.16 |
1.40% |
7/18/2016 |
47.640 |
2.2% |
|
|
|
|
Beta |
0.22 |
|
7/25/2016 |
5162.13 |
1.22% |
7/25/2016 |
45.310 |
-4.9% |
|
|
|
|
Inst. own |
99% |
|
8/1/2016 |
5221.12 |
1.14% |
8/1/2016 |
45.820 |
1.1% |
|
|
|
|
|
|
|
8/8/2016 |
5232.89 |
0.23% |
8/8/2016 |
47.300 |
3.2% |
|
|
|
|
As per regression model |
|
|
8/15/2016 |
5238.38 |
0.10% |
8/15/2016 |
47.630 |
0.7% |
|
|
|
|
Beta |
0.75 |
|
8/22/2016 |
5218.92 |
-0.37% |
8/22/2016 |
49.010 |
2.9% |
|
|
|
|
Risk Free Rate |
2.88% |
|
8/29/2016 |
5249.90 |
0.59% |
8/29/2016 |
49.350 |
0.7% |
|
|
|
|
Market risk Premium |
5.69% |
|
9/5/2016 |
5125.91 |
-2.36% |
9/5/2016 |
47.860 |
-3.0% |
|
|
|
|
Cost of Equity |
7.13% |
|
9/12/2016 |
5244.57 |
2.31% |
9/12/2016 |
48.050 |
0.4% |
|
|
|
|
Average Cost of Debt |
4.10% |
|
9/19/2016 |
5305.75 |
1.17% |
9/19/2016 |
50.110 |
4.3% |
|
|
|
|
Marginal Tax rate |
37.4% |
|
9/26/2016 |
5312.00 |
0.12% |
9/26/2016 |
52.080 |
3.9% |
|
|
|
|
Target Debt To Equity |
2.2 |
|
10/3/2016 |
5292.40 |
-0.37% |
10/3/2016 |
51.000 |
-2.1% |
|
|
|
|
Debt ratio |
69.13% |
|
10/10/2016 |
5214.16 |
-1.48% |
10/10/2016 |
51.330 |
0.6% |
|
|
|
|
Growth rate |
2% |
|
10/17/2016 |
5257.40 |
0.83% |
10/17/2016 |
48.930 |
-4.7% |
|
|
|
|
WACC |
3.98% |
|
10/24/2016 |
5190.10 |
-1.28% |
10/24/2016 |
48.990 |
0.1% |
|
|
|
|
|
|
|
10/31/2016 |
5046.37 |
-2.77% |
10/31/2016 |
46.780 |
-4.5% |
|
|
|
|
|
|
|
11/7/2016 |
5237.11 |
3.78% |
11/7/2016 |
48.850 |
4.4% |
|
|
|
|
|
|
|
11/14/2016 |
5321.51 |
1.61% |
11/14/2016 |
50.900 |
4.2% |
|
|
|
|
|
|
|
11/21/2016 |
5398.92 |
1.45% |
11/21/2016 |
53.990 |
6.1% |
|
|
|
|
|
|
|
11/28/2016 |
5255.65 |
-2.65% |
11/28/2016 |
54.270 |
0.5% |
|
|
|
|
|
|
|
12/5/2016 |
5444.50 |
3.59% |
12/5/2016 |
53.990 |
-0.5% |
|
|
|
|
|
|
|
12/12/2016 |
5437.16 |
-0.13% |
12/12/2016 |
55.440 |
2.7% |
|
|
|
|
|
|
|
12/19/2016 |
5462.69 |
0.47% |
12/19/2016 |
53.590 |
-3.3% |
|
|
|
|
|
|
|
12/26/2016 |
5383.12 |
-1.46% |
12/26/2016 |
52.440 |
-2.1% |
|
|
|
|
|
|
|
1/2/2017 |
5521.06 |
2.56% |
1/2/2017 |
51.470 |
-1.8% |
|
|
|
|
|
|
|
1/9/2017 |
5574.12 |
0.96% |
1/9/2017 |
51.840 |
0.7% |
|
|
|
|
|
|
|
1/16/2017 |
5555.33 |
-0.34% |
1/16/2017 |
51.020 |
-1.6% |
|
|
|
|
|
|
|
1/23/2017 |
5660.78 |
1.90% |
1/23/2017 |
51.820 |
1.6% |
|
|
|
|
|
|
|
1/30/2017 |
5666.77 |
0.11% |
1/30/2017 |
51.590 |
-0.4% |
|
|
|
|
|
|
|
2/6/2017 |
5734.13 |
1.19% |
2/6/2017 |
55.920 |
8.4% |
|
|
|
|
|
|
|
2/13/2017 |
5838.58 |
1.82% |
2/13/2017 |
55.010 |
-1.6% |
|
|
|
|
|
|
|
2/20/2017 |
5845.31 |
0.12% |
2/20/2017 |
54.690 |
-0.6% |
|
|
|
|
|
|
|
2/27/2017 |
5870.75 |
0.44% |
2/27/2017 |
54.790 |
0.2% |
|
|
|
|
|
|
|
3/6/2017 |
5861.73 |
-0.15% |
3/6/2017 |
53.950 |
-1.5% |
|
|
|
|
|
|
|
3/13/2017 |
5901.00 |
0.67% |
3/13/2017 |
56.530 |
4.8% |
|
|
|
|
|
|
|
3/20/2017 |
5828.74 |
-1.22% |
3/20/2017 |
55.980 |
-1.0% |
|
|
|
|
|
|
|
3/27/2017 |
5911.74 |
1.42% |
3/27/2017 |
54.680 |
-2.3% |
|
|
|
|
|
|
|
4/3/2017 |
5877.81 |
-0.57% |
4/3/2017 |
53.110 |
-2.9% |
|
|
|
|
|
|
|
4/10/2017 |
5805.15 |
-1.24% |
4/10/2017 |
53.500 |
0.7% |
|
|
|
|
|
|
|
4/17/2017 |
5910.52 |
1.82% |
4/17/2017 |
55.200 |
3.2% |
|
|
|
|
|
|
|
4/24/2017 |
6047.61 |
2.32% |
4/24/2017 |
55.860 |
1.2% |
|
|
|
|
|
|
|
5/1/2017 |
6100.76 |
0.88% |
5/1/2017 |
55.970 |
0.2% |
|
|
|
|
|
|
|
5/8/2017 |
6121.23 |
0.34% |
5/8/2017 |
56.130 |
0.3% |
|
|
|
|
|
|
|
5/15/2017 |
6011.24 |
-1.80% |
5/15/2017 |
55.660 |
-0.8% |
|
|
|
|
|
|
|
5/17/2017 |
6011.24 |
0.00% |
5/17/2017 |
55.660 |
0.0% |
|
For calculating beta |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SUMMARY OUTPUT |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Regression Statistics |
|
|
|
|
|
|
|
Multiple R |
0.393425789 |
|
|
|
|
|
|
|
R Square |
0.154783851 |
|
|
|
|
|
|
|
Adjusted R Square |
0.138210986 |
|
|
|
|
|
|
|
Standard Error |
0.027126607 |
|
|
|
|
|
|
|
Observations |
53 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ANOVA |
|
|
|
|
|
|
|
|
|
df |
SS |
MS |
F |
Significance F |
|
|
|
Regression |
1 |
0.006872567 |
0.006873 |
9.339595 |
0.003564 |
|
|
|
Residual |
51 |
0.037528494 |
0.000736 |
|
|
|
|
|
Total |
52 |
0.044401061 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
Intercept |
0.002298986 |
0.003845225 |
0.597881 |
0.552565 |
-0.00542 |
0.010019 |
-0.00542 |
0.010019 |
Percentage Change |
0.747035057 |
0.244442639 |
3.056075 |
0.003564 |
0.256296 |
1.237774 |
0.256296 |
1.237774 |
RESIDUAL OUTPUT |
|
|
|
|
|
Observation |
Predicted Percentage Change |
Residuals |
1 |
0.003643649 |
0.006356351 |
2 |
0.003664806 |
0.006415331 |
3 |
-0.004951451 |
0.029673611 |
4 |
-0.012079894 |
0.007653156 |
5 |
-0.012074175 |
-0.04706322 |
6 |
0.026828409 |
0.018540258 |
7 |
0.016769357 |
-0.009762192 |
8 |
0.013275234 |
0.032740458 |
9 |
0.012780655 |
0.009536964 |
10 |
0.011375869 |
-0.060284309 |
11 |
0.010835728 |
0.000420043 |
12 |
0.003983034 |
0.028317249 |
13 |
0.003082689 |
0.003894097 |
14 |
-0.000476161 |
0.029449433 |
15 |
0.006733454 |
0.000203906 |
16 |
-0.015344148 |
-0.014848295 |
17 |
0.019592097 |
-0.015622226 |
18 |
0.011013473 |
0.031858578 |
19 |
0.003178969 |
0.03613456 |
20 |
-0.000457407 |
-0.020279958 |
21 |
-0.008744743 |
0.015215371 |
22 |
0.008493964 |
-0.055250284 |
23 |
-0.007263785 |
0.008490068 |
24 |
-0.018388733 |
-0.026722573 |
25 |
0.03053498 |
0.013714679 |
26 |
0.014338009 |
0.027627274 |
27 |
0.013165846 |
0.047541421 |
28 |
-0.017524929 |
0.022711037 |
29 |
0.029142031 |
-0.034301382 |
30 |
0.001291893 |
0.025564876 |
31 |
0.005806634 |
-0.039176025 |
32 |
-0.008582365 |
-0.012876881 |
33 |
0.021441411 |
-0.039938703 |
34 |
0.009478354 |
-0.00228972 |
35 |
-0.000219226 |
-0.015598675 |
36 |
0.016478995 |
-0.000798869 |
37 |
0.003089498 |
-0.007527939 |
38 |
0.011178856 |
0.0727521 |
39 |
0.015906623 |
-0.032179871 |
40 |
0.003160074 |
-0.00897718 |
41 |
0.00555023 |
-0.003721705 |
42 |
0.001151216 |
-0.016482481 |
43 |
0.007303666 |
0.040518354 |
44 |
-0.006848714 |
-0.002880616 |
45 |
0.012936604 |
-0.036159183 |
46 |
-0.001988589 |
-0.026723902 |
47 |
-0.006935692 |
0.014278923 |
48 |
0.015858528 |
0.015917192 |
49 |
0.019625874 |
-0.007669353 |
50 |
0.008864363 |
-0.006895155 |
51 |
0.004805554 |
-0.00194688 |
52 |
-0.011124165 |
0.002750729 |
53 |
0.00229844 |
-0.00229844 |
Valuation
FCFF Approach |
|
Actual |
Projection |
USD Million |
|
Dec-13 |
Dec-14 |
Dec-15 |
Dec-16 |
Dec-17 |
Dec-18 |
Dec-19 |
Dec-20 |
Dec-21 |
Profit |
|
146.9 |
176.4 |
105.1 |
195.5 |
204.5 |
223.8 |
244.1 |
265.4 |
288.0 |
Depreciation |
|
49.4 |
45.5 |
45.2 |
42.5 |
42.5 |
42.5 |
42.5 |
42.5 |
42.5 |
Interest *(1-t) |
|
50.2 |
42.6 |
60.6 |
63.1 |
61.6 |
61.6 |
61.6 |
61.6 |
61.6 |
Change in working capital |
|
39.9 |
-14.6 |
-37.9 |
47.8 |
2.6 |
-7.8 |
-9.8 |
-11.0 |
-14.8 |
Capex |
|
-31.1 |
-23.6 |
-30.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
FCFF |
|
255.3 |
226.3 |
142.8 |
333.7 |
296.0 |
304.9 |
323.2 |
343.4 |
362.1 |
Terminal Value |
|
|
|
|
|
|
|
|
|
18700 |
Total value |
|
255.3 |
226.3 |
142.8 |
333.7 |
296.0 |
304.9 |
323.2 |
343.4 |
19061.8 |
Value of firm |
$ 17,207.36 |
Million |
|
|
|
|
|
|
|
|
Equity value of Firm |
$ 14,805.36 |
Million |
|
|
|
|
|
|
|
|
FCFE Approach |
|
|
|
|
|
|
|
|
|
|
Profit |
|
146.9 |
176.4 |
105.1 |
195.5 |
204.4897 |
223.7713 |
244.0719 |
265.4498 |
287.9659 |
Depreciation |
|
49.4 |
45.5 |
45.2 |
42.5 |
42.5 |
42.5 |
42.5 |
42.5 |
42.5 |
Net debt raised |
|
24.2 |
-15 |
662.2 |
-25 |
0 |
0 |
0 |
0 |
0 |
Change in working capital |
|
39.90 |
-14.60 |
-37.90 |
47.80 |
2.60 |
-7.76 |
-9.80 |
-11.01 |
-14.79 |
Capex |
|
-31.1 |
-23.6 |
-30.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
-15.2 |
FCFE |
|
229.30 |
168.70 |
744.40 |
245.60 |
234.38 |
243.31 |
261.57 |
281.74 |
300.48 |
Terminal value |
|
|
|
|
|
|
|
|
|
5973.7 |
Total value |
|
229.3 |
168.7 |
744.4 |
245.6 |
234.4 |
243.3 |
261.6 |
281.7 |
6274.1 |
Total Equity value |
$ 5,447.37 |
Million |
|
|
|
|
|
|
|
|
Dividend Discount model |
|
|
|
|
|
Historical Dividend Per Share |
|
0.76 |
0.92 |
1.06 |
1.2 |
Growth rate |
16.4% |
|
|
|
|
So next year dividend would be |
1.40 |
|
|
|
|
Price of stock |
$ 27.24 |
|
|
|
|
Number of stock outstanding |
92.16 |
Million |
|
|
|
Total Value |
$ 2,510.01 |
Million |
|
|
|
Dividend Discount model |
|
|
|
|
|
Historical Dividend Per Share |
|
0.76 |
0.92 |
1.06 |
1.2 |
Growth rate |
16.4% |
|
|
|
|
So next year dividend would be |
1.40 |
|
|
|
|
Price of stock |
$ 27.24 |
|
|
|
|
Number of stock outstanding |
92.16 |
Million |
|
|
|
Total Value |
$ 2,510.01 |
Million |
|
|
|
Approach |
Valuation in $ Million |
FCFF approach |
$ 14,805.36 |
|
FCFE approach |
$ 5,447.37 |
|
Dividend Discount model |
$ 2,510.01 |
|
Market Cap model |
$ 5,190.45 |
|