Initation of Coverage Report Equity Research plus excel financial forecast

profileTennis30
DCFABCInc_RV.xlsx
Home>Business & Finance homework help>Financial markets homework help>Initation of Coverage Report Equity Research plus excel financial forecast

ABC Inc

FY14 FY15 FY16 FY17 FY18 FY19 FY20 FY21 FY22 FY23 FY24E FY25E FY26E FY27E FY28E
DEC '14 DEC '15 DEC '16 DEC '17 DEC '18 DEC '19 DEC '20 DEC '21 DEC '22 DEC '23
Sales 88.99 107.01 135.99 177.87 232.89 280.52 386.06 469.82 513.98 574.79 661.0 760.2 851.4 953.5 1,029.8
Sales growth (Y-o-Y) 20.2% 27.1% 30.8% 30.9% 20.5% 37.6% 21.7% 9.4% 11.8% 15.0% 15.0% 12.0% 12.0% 8.0% 12-15%
Cost of Goods Sold (COGS) incl. D&A 62.75 71.65 88.27 111.93 139.16 165.54 233.31 272.34 288.83 304.74 350.44 394.10 432.24 473.86 500.70
COGS excluding D&A 59.53 66.00 82.21 102.74 123.82 143.75 208.06 238.05 246.91 256.08 297.45 334.47 366.09 400.49 422.23
% sales 66.9% 61.7% 60.5% 57.8% 53.2% 51.2% 53.9% 50.7% 48.0% 44.6% 45.0% 44.0% 43.0% 42.0% 41.0% Avg = 50%, trending down, industry average =40%
Depreciation & Amortization Expense 3.22 5.65 6.05 9.20 15.34 21.79 25.25 34.30 41.92 48.66 52.98 59.63 66.15 73.37 78.47
% of fixed assets 15.3% 21.2% 17.9% 14.0% 19.1% 18.7% 14.8% 14.5% 15.0% 15.8% 15.0% 15.0% 15.0% 15.0% 15.0% Avg = 15%
Gross Income 26.24 35.36 47.72 65.93 93.73 114.99 152.76 197.48 225.15 270.05 310.57 366.05 419.13 479.68 529.12
% margin 29.5% 33.0% 35.1% 37.1% 40.2% 41.0% 39.6% 42.0% 43.8% 47.0% 47.0% 48.2% 49.2% 50.3% 51.4%
SG&A Expense 25.93 32.95 43.37 61.61 81.01 100.24 129.93 172.54 211.64 232.43 264.40 304.06 340.55 381.41 411.93
% sales 29.1% 30.8% 31.9% 34.6% 34.8% 35.7% 33.7% 36.7% 41.2% 40.4% 40.0% 40.0% 40.0% 40.0% 40.0% Trending up, keep it at 40%
EBIT (Operating Income) 0.31 2.40 4.35 4.32 12.72 14.74 22.82 24.94 13.51 37.62 46.17 61.99 78.58 98.26 117.19
% margin 0.3% 2.2% 3.2% 2.4% 5.5% 5.3% 5.9% 5.3% 2.6% 6.5% 7.0% 8.2% 9.2% 10.3% 11.4%
Nonoperating Income - Net -0.21 -0.38 -0.04 0.23 0.09 0.82 1.50 2.18 -1.08 2.11 2.11 2.11 2.11 2.11 2.11
Interest Expense 0.21 0.46 0.48 0.85 1.42 1.60 1.65 1.81 2.37 3.18 4.64 4.64 4.64 4.64 4.64
Interest rate 1.3% 2.6% 2.4% 1.9% 2.9% 2.1% 1.6% 1.4% 1.5% 2.1% 3.0% 3.0% 3.0% 3.0% 3.0%
Unusual Expense - Net 0.00 -0.07 -0.11 0.13 -0.01 -1.50 -12.84 16.00 -1.01
Pretax Income -0.11 1.57 3.89 3.81 11.26 13.98 24.18 38.15 -5.94 37.56 43.64 59.46 76.05 95.74 114.67
Income Taxes 0.17 0.95 1.43 0.77 1.20 2.37 2.86 4.79 -3.22 7.12 8.73 11.89 15.21 19.15 22.93
Tax rate -150.5% 60.6% 36.6% 20.2% 10.6% 17.0% 11.8% 12.6% 54.2% 19.0% 20.0% 20.0% 20.0% 20.0% 20.0%
Equity in Earnings of Affiliates 0.04 -0.02 -0.10 -0.00 0.01 -0.01 0.02 0.00 -0.00 -0.01
Net Income -0.24 0.60 2.37 3.03 10.07 11.59 21.33 33.36 -2.72 30.42 34.91 47.57 60.84 76.59 91.73
% margin -0.3% 0.6% 1.7% 1.7% 4.3% 4.1% 5.5% 7.1% -0.5% 5.3% 5.3% 6.3% 7.1% 8.0% 8.9%
Net Income available to Common -0.24 0.60 2.37 3.03 10.07 11.59 21.33 33.36 -2.72 30.42
DPS 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Dividend 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
∆Retained Earnings 34.91 47.57 60.84 76.59 91.73
Per Share
EPS (recurring) -0.03 0.06 0.24 0.30 1.02 1.15 1.99 2.37 0.83 2.83 3.36 4.58 5.86 7.38 8.83
Basic Shares Outstanding (M) 9,240.00 9,340.00 9,480.00 9,600.00 9,740.00 9,880.00 10,000.00 10,120.00 10,189.00 10,304.00 10,304.00 10,304.00 10,304.00 10,304.00 10,304.00
Total Shares Outstanding (M) 9,300.00 9,420.00 9,540.00 9,680.00 9,820.00 9,960.00 10,060.00 10,180.00 10,242.00 10,383.00 10,383.00 10,383.00 10,383.00 10,383.00 10,383.00
EPS (diluted) -0.03 0.06 0.25 0.31 1.01 1.15 2.09 3.24 -0.27 2.90 3.39 4.62 5.90 7.43 8.90
Diluted Shares Outstanding (M) 9,240.00 9,540.00 9,680.00 9,860.00 10,000.00 10,080.00 10,200.00 10,300.00 10,189.00 10,492.00
Total Shares Outstanding (M) 9,300.00 9,420.00 9,540.00 9,680.00 9,820.00 9,960.00 10,060.00 10,180.00 10,242.00 10,383.00
Earnings Persistence 63.04 70.92 73.97 57.53 76.59 74.36 77.34 68.89 79.50 85.32
EBITDA
EBITDA 3.53 8.05 10.41 13.52 28.06 36.53 48.08 59.24 55.43 86.28 99.15 121.62 144.73 171.64 195.67
EBIT 0.31 2.40 4.35 4.32 12.72 14.74 22.82 24.94 13.51 37.62
Depreciation & Amortization Expense 3.22 5.65 6.05 9.20 15.34 21.79 25.25 34.30 41.92 48.66
All figures in billions of U.S. Dollar except per share and labeled items.
Assets DEC '14 DEC '15 DEC '16 DEC '17 DEC '18 DEC '19 DEC '20 DEC '21 DEC '22 DEC '23
Cash & Short-Term Investments 17.87 20.09 26.58 32.32 41.68 55.34 84.65 96.31 70.39 87.28 76.75 78.53 94.10 120.28 175.55
Short-Term Receivables 5.16 5.37 7.74 11.83 16.25 20.50 24.29 32.63 42.00 51.75 59.51 68.44 76.65 85.85 92.72
% of sales 5.8% 5.0% 5.7% 6.7% 7.0% 7.3% 6.3% 6.9% 8.2% 9.0% 9.0% 9.0% 9.0% 9.0% 9.0%
Inventories 8.30 10.24 11.46 16.05 17.17 20.50 23.80 32.64 34.41 33.32 38.67 43.48 47.59 52.06 54.89
% of COGS 13.9% 15.5% 13.9% 15.6% 13.9% 14.3% 11.4% 13.7% 13.9% 13.0% 13.0% 13.0% 13.0% 13.0% 13.0%
Total Current Assets 31.33 35.71 45.78 60.20 75.10 96.33 132.73 161.58 146.79 172.35 174.93 190.45 218.34 258.19 323.16
Net Property, Plant & Equipment 16.97 21.84 29.11 48.87 61.80 97.85 150.67 216.36 252.84 276.69 318.19 357.27 395.89 438.63 468.57
% of sales 19.1% 20.4% 21.4% 27.5% 26.5% 34.9% 39.0% 46.1% 49.2% 48.1% 48.1% 47.0% 46.5% 46.0% 45.5%
Total Long-Term Investments 0.89 0.42 0.72 1.40 1.52 0.71 3.02 3.42 2.11 2.21 2.21 2.21 2.21 2.21 2.21
Intangible Assets 4.08 4.75 4.64 16.72 18.66 18.80 20.00 20.48 26.39 30.48 35.03 40.29 45.12 50.54 54.58
% of sales 4.6% 4.4% 3.4% 9.4% 8.0% 6.7% 5.2% 4.4% 5.1% 5.3% 5.3% 5.3% 5.3% 5.3% 5.3%
Deferred Tax Assets 2.11 2.34 3.07
Other Assets 0.97 1.95 2.70 4.13 5.58 11.55 14.78 18.71 34.55 46.13 46.13 46.13 46.13 46.13 46.13
Total Assets 56.34 67.01 86.02 131.31 162.65 225.25 321.20 420.55 462.68 527.85 576.50 636.35 707.69 795.69 894.64
Liabilities & Shareholders' Equity
Current
ST Debt & Curr. Portion LT Debt 3.60 3.34 5.20 6.18 9.50 14.33 16.12 15.92 14.85 18.95 18.95 18.95 18.95 18.95 18.95
Accounts Payable 16.46 20.40 25.31 34.62 38.19 47.18 72.54 78.66 79.60 84.98 98.71 111.00 121.49 132.90 140.12
% of COGS 27.6% 30.9% 30.8% 33.7% 30.8% 32.8% 34.9% 33.0% 32.2% 33.2% 33% 33% 33% 33% 33%
Other Current Liabilities 8.03 10.15 13.31 17.09 20.70 26.30 37.73 47.68 60.94 60.99 60.99 60.99 60.99 60.99 60.99
Total Current Liabilities 28.09 33.89 43.82 57.88 68.39 87.81 126.39 142.27 155.39 164.92 178.65 190.93 201.43 212.84 220.06
Long-Term
Long-Term Debt 12.49 14.18 15.21 37.93 39.79 63.21 84.39 116.40 140.12 135.61 135.61 135.61 135.61 135.61 135.61
Provision for Risks & Charges 0.51 0.93 1.40 1.00 0.90 0.00 0.00 0.00 0.00 0.00
Deferred Tax Liabilities 2.86 2.67 3.01 0.99 1.49
Other Liabilities 1.66 1.96 3.30 5.80 8.54 12.17 17.02 23.64 21.12 25.45 25.45 25.45 25.45 25.45 25.45
Total Liabilities 45.60 53.62 66.74 103.60 119.10 163.19 227.79 282.30 316.63 325.98 339.71 351.99 362.49 373.90 381.12
Equity
Common Equity 10.74 13.38 19.29 27.71 43.55 62.06 93.40 138.25 146.04 201.88 236.79 284.36 345.20 421.79 513.52
Retained Earnings 1.95 2.55 4.92 8.64 19.63 31.22 52.55 85.92 83.19 113.62 148.53 196.10 256.94 333.53 425.27
Common equity excluding retained earnings 8.79 10.84 14.37 19.07 23.92 30.84 40.85 52.33 62.85 88.26 88.26 88.26 88.26 88.26 88.26
Total Shareholders' Equity 10.74 13.38 19.29 27.71 43.55 62.06 93.40 138.25 146.04 201.88 236.79 284.36 345.20 421.79 513.52
Total Liabilities & Shareholders' Equity 56.34 67.01 86.02 131.31 162.65 225.25 321.20 420.55 462.68 527.85 576.50 636.35 707.69 795.69 894.64
Per Share
Book Value per Share 1.15 1.42 2.02 2.86 4.43 6.23 9.28 13.58 14.26 19.44
Tangible Book Value per Share 0.72 0.92 1.54 1.14 2.53 4.34 7.30 11.57 11.68 16.51
All figures in billions of U.S. Dollar except per share items.
- 0 - 0 - 0 - 0 - 0
Net working capital ex cash & Stdebt -11.03 -14.94 -19.42 -23.83 -25.46 -32.49 -62.19 -61.07 -64.14 -60.90 -61.52 -60.07 -58.24 -55.98 -53.50
# shares O/S (billions) 9.24 9.34 9.48 9.60 9.74 9.88 10.00 10.12 10.19 10.30 10.30 10.30 10.30 10.30 10.30
Stock price 19.94 15.63 32.81 37.90 58.60 73.26 93.75 163.50 167.55 151.54
Market cap 184.25 145.98 311.04 363.84 570.76 723.81 937.50 1,654.62 1,707.17 1,561.47
EPS (recurring) -0.03 0.06 0.24 0.30 1.02 1.15 1.99 2.37 0.83 2.83 3.36 4.58 5.86 7.38 8.83
Book Value per Share 1.16 1.43 2.03 2.89 4.47 6.28 9.34 13.66 14.33 19.59 22.98 27.60 33.50 40.93 49.84
Sales 88.99 107.01 135.99 177.87 232.89 280.52 386.06 469.82 513.98 574.79 661.00 760.15 851.37 953.54 1,029.82
EBITDA 3.53 8.05 10.41 13.52 28.06 36.53 48.08 59.24 55.43 86.28 99.15 0.15 0.15 0.15 0.15
EV 211.98 179.52 351.19 435.12 648.19 831.65 1,080.64 1,840.62 1,953.41 1,800.16
g 276.3% 24.9% 239.0% 13.0% 73.1% 19.0% -64.8% 240.4% 18.7% 36.3% 27.9% 25.9% 19.8%
Appropriate ratios (base case) Base case Best case (Bullish) Worst case (Bearish)
P/E 85.00 150.00 50.00
P/S 3.30 5.50 1.60
P/B 13.00 24.00 6.00
EV/EBITDA 13.00 21.00 8.00
PEG
Target price
Using PE 285.8 504.4 168.1
EV/EBITDA method
EV 1289.0 2082.2 793.2
net debt 238.7 238.7 238.7
MVE 1050.3 1843.5 554.5
Target price 113.7 199.5 60.0
P/B 298.74 551.52 137.88
P/S 211.7 352.8 102.6

FCFF

FY24E FY25E FY26E FY27E FY28E
Net Income 34.91 47.57 60.84 76.59 91.73
Depreciation 52.98 59.63 66.15 73.37 78.47
Interest 4.64 4.64 4.64 4.64 4.64
OCF 92.5324653725 111.8417439196 131.6321794747 154.5994592889 174.8422684371
∆NWC (Exclude cash & short term debt) -0.62 1.45 1.83 2.26 2.48
Capex (∆ Net Fixed asset + depreciation) 99.04 103.97 109.60 121.53 112.46
FCFF (5.89) 6.42 20.20 30.82 59.91 62.60
Terminal value 4,173.49
Year 1 2 3 4 5
Discounted cash flow (5.56) 5.71 16.96 24.41 3,163.44
Value of firm 3,204.96
Net debt 154.56
Equity value 3,050.41
Target price 293.79
WACC 6.00%
Terminal growth 4.5%

FCFE

FY24E FY25E FY26E FY27E FY28E
Net Income 34.91 47.57 60.84 76.59 91.73
Depreciation 52.98 59.63 66.15 73.37 78.47
OCF(Equity) 87.8957853725 107.2050639196 126.9954994747 149.9627792889 170.2055884371
∆NWC (Exclude cash & short term debt) -0.62 1.45 1.83 2.26 2.48
Capex (∆ Net Fixed asset + depreciation) 99.04 103.97 109.60 121.53 112.46
∆(Long term debt + short term debt)
FCFE -10.5312934161 1.7827408043 15.5640957224 26.179354197 55.2698535207 58.0333461968
Terminal value 2763.4926760364
Year 1 2 3 4 5
Discounted cash flow -9.8423302954 1.557114861 12.7049382939 19.9721039714 2009.7387268207
Equity value 2034.1305536515
Target price 195.9097133441
Discount rate (Cost of equity) 7%
Terminal growth 5%

image2.png

image1.png