Initation of Coverage Report Equity Research plus excel financial forecast

profileTennis30
DCFABCInc_raw1.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
Sales growth (Y-o-Y) 20.2% 27.1% 30.8% 30.9% 20.5% 37.6% 21.7% 9.4% 11.8% 12-15% 12.0% 12.0% 10.0% 10.0% 8.0%
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
COGS excluding D&A 59.53 66.00 82.21 102.74 123.82 143.75 208.06 238.05 246.91 256.08 - 0 - 0 - 0 - 0 - 0
% sales 66.9% 61.7% 60.5% 57.8% 53.2% 51.2% 53.9% 50.7% 48.0% 44.6% Avg = 50%, trending down, industry average =40% 45.0% 44.0% 43.0% 42.0% 41.0%
Depreciation & Amortization Expense 3.22 5.65 6.05 9.20 15.34 21.79 25.25 34.30 41.92 48.66 - 0 - 0 - 0 - 0 - 0
% of fixed assets 15.3% 21.2% 17.9% 14.0% 19.1% 18.7% 14.8% 14.5% 15.0% 15.8% Avg = 15% 15.0% 15.0% 15.0% 15.0% 15.0%
Depreciation 3.04 5.38 5.77 8.83
Amortization of Intangibles 0.18 0.27 0.29 0.37
Gross Income 26.24 35.36 47.72 65.93 93.73 114.99 152.76 197.48 225.15 270.05 - 0 - 0 - 0 - 0 - 0
% margin 29.5% 33.0% 35.1% 37.1% 40.2% 41.0% 39.6% 42.0% 43.8% 47.0% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
SG&A Expense 25.93 32.95 43.37 61.61 81.01 100.24 129.93 172.54 211.64 232.43 - 0 - 0 - 0 - 0 - 0
% sales 29.1% 30.8% 31.9% 34.6% 34.8% 35.7% 33.7% 36.7% 41.2% 40.4% Trending up, keep it at 40% 40.0% 40.0% 40.0% 40.0% 40.0%
Research & Development 9.28 12.54 16.09 22.62 28.84 35.93 42.74 56.05 73.21 85.62
Other SG&A 16.65 20.41 27.28 38.99 52.18 64.31 87.19 116.49 138.43 146.81
EBIT (Operating Income) 0.31 2.40 4.35 4.32 12.72 14.74 22.82 24.94 13.51 37.62 - 0 - 0 - 0 - 0 - 0
% margin 0.3% 2.2% 3.2% 2.4% 5.5% 5.3% 5.9% 5.3% 2.6% 6.5% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
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 0.00 2.11 0.00 2.11
Nonoperating Interest Income 0.04 0.05 0.10 0.20 0.44 0.83 0.56 0.45 0.99 2.95
Other Income (Expense) -0.25 -0.43 -0.14 0.02 -0.35 -0.01 0.95 1.73 -2.07 -0.84
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%
Gross Interest Expense 0.21 0.46 0.48 0.85 1.42 1.60 1.65 1.81 2.37 3.18
Unusual Expense - Net 0.00 -0.07 -0.11 0.13 -0.01 -1.50 -12.84 16.00 -1.01
Unrealized Valuation Gain/Loss 0.00 0.07 0.11 -0.13 0.01 1.50 12.84 -16.00 1.01
Investments 11.53 -13.87 0.98
Hedges/Derivatives 0.00 0.07 0.11 -0.13 0.01 1.50 1.32 -2.13 0.03
Pretax Income -0.11 1.57 3.89 3.81 11.26 13.98 24.18 38.15 -5.94 37.56 (2.53) (4.64) (2.53) (4.64) (2.53)
Income Taxes 0.17 0.95 1.43 0.77 1.20 2.37 2.86 4.79 -3.22 7.12 (0.51) (0.93) (0.51) (0.93) (0.51)
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%
Income Taxes - Current Domestic 0.28 0.45 1.34 0.07 0.19 0.44 2.46 2.89 3.25 10.81
Income Taxes - Current Foreign 0.20 0.42 0.33 0.72 0.56 1.14 0.96 2.21 1.68 2.19
Income Taxes - Deferred Domestic -0.14 0.30 0.09 -0.23 0.57 0.92 -0.34 -0.02 -7.99 -6.00
Income Taxes - Deferred Foreign -0.18 -0.22 -0.33 0.20 -0.13 -0.13 -0.21 -0.29 -0.16 0.13
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
Consolidated Net Income -0.24 0.60 2.37 3.03 10.07 11.59 21.33 33.36 -2.72 30.43
Net Income -0.24 0.60 2.37 3.03 10.07 11.59 21.33 33.36 -2.72 30.43 (2.02) (3.71) (2.02) (3.71) (2.02)
% margin -0.3% 0.6% 1.7% 1.7% 4.3% 4.1% 5.5% 7.1% -0.5% 5.3% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Net Income available to Common -0.24 0.60 2.37 3.03 10.07 11.59 21.33 33.36 -2.72 30.43
DPS 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 (2.02) (3.71) (2.02) (3.71) (2.02)
Per Share
EPS (recurring) -0.03 0.06 0.24 0.30 1.02 1.15 1.99 2.37 0.83 2.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
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
EPS (diluted) -0.03 0.06 0.25 0.31 1.01 1.15 2.09 3.24 -0.27 2.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
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 87.28 87.28 87.28 87.28 87.28
Cash Only 15.01 16.18 19.93 21.86 32.18 36.41 42.38 36.48 54.25 73.89
Total Short Term Investments 2.86 3.92 6.65 10.46 9.50 18.93 42.27 59.83 16.14 13.39
Short-Term Receivables 5.16 5.37 7.74 11.83 16.25 20.50 24.29 32.63 42.00 51.75 - 0 - 0 - 0 - 0 - 0
% of sales 5.8% 5.0% 5.7% 6.7% 7.0% 7.3% 6.3% 6.9% 8.2% 9.0% 8.5% 8.5% 8.5% 8.5% 8.5%
Accounts Receivables, Net 2.85 3.82 5.30 7.67 12.17 20.50 24.29 32.63 42.00 51.75
Accounts Receivables, Gross 3.04 4.00 5.54 8.01 12.67 21.21 25.39 33.73 43.40 53.45
Bad Debt/Doubtful Accounts -0.19 -0.19 -0.24 -0.35 -0.50 -0.72 -1.10 -1.10 -1.40 -1.70
Other Receivables 2.31 1.55 2.44 4.16 4.08 0.00 0.00 0.00 0.00 0.00
Inventories 8.30 10.24 11.46 16.05 17.17 20.50 23.80 32.64 34.41 33.32 - 0 - 0 - 0 - 0 - 0
% 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%
Finished Goods 8.30 10.24 11.46 16.05 17.17 20.50 23.80 32.64 34.41 33.32
Total Current Assets 31.33 35.71 45.78 60.20 75.10 96.33 132.73 161.58 146.79 172.35 87.28 87.28 87.28 87.28 87.28
Net Property, Plant & Equipment 16.97 21.84 29.11 48.87 61.80 97.85 150.67 216.36 252.84 276.69 - 0 - 0 - 0 - 0 - 0
% of sales 19.1% 20.4% 21.4% 27.5% 26.5% 34.9% 39.0% 46.1% 49.2% 48.1% 47.5% 47.0% 46.5% 46.0% 45.5%
Property, Plant & Equipment - Gross 22.73 30.05 42.44 72.66 95.77 144.82 211.10 294.88 349.85 396.80
Buildings 7.15 9.77 14.00 23.90 31.74 39.22 57.32 81.10 91.65 105.29
Machinery & Equipment 12.91 17.02 24.59 42.24 54.59 71.31 97.22 128.68 157.46 185.04
Construction in Progress 1.06 1.53 1.81 4.08 6.86 6.04 15.23 24.90 30.02 28.84
Computer Software and Equipment 1.30 1.40 1.40
Other Property, Plant & Equipment 0.30 0.33 0.65 2.44 2.58 3.11 3.77 4.12 4.60 5.12
Operating Lease Right-of-Use Assets 25.14 37.55 56.08 66.12 72.51
Accumulated Depreciation 5.76 8.22 13.33 23.79 33.97 46.98 60.43 78.52 97.02 120.11
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
LT Investment - Affiliate Companies 0.08 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Other Long-Term Investments 0.81 0.42 0.72 1.40 1.52 0.71 3.02 3.42 2.11 2.21
Intangible Assets 4.08 4.75 4.64 16.72 18.66 18.80 20.00 20.48 26.39 30.48 - 0 - 0 - 0 - 0 - 0
% 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%
Goodwill 3.32 3.76 3.78 13.35 14.55 14.75 15.02 15.37 20.29 22.79
Other Intangible Assets 0.76 0.99 0.85 3.37 4.11 4.05 4.98 5.11 6.10 7.69
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
Tangible Other Assets 0.97 1.95 2.70 4.13 5.58 11.55 14.78 18.71 34.55 46.13
Total Assets 56.34 67.01 86.02 131.31 162.65 225.25 321.20 420.55 462.68 527.85 135.62 135.62 135.62 135.62 135.62
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 - 0 - 0 - 0 - 0 - 0
% 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
Miscellaneous Current Liabilities 8.03 10.15 13.31 17.09 20.70 26.30 37.73 47.68 60.94 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 79.94 79.94 79.94 79.94 79.94
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
Long-Term Debt excl Lease Obligations 8.27 8.23 7.69 24.74 23.50 23.41 31.82 48.74 67.15 58.31
Capital and Operating Lease Obligations 4.22 5.95 7.52 13.18 16.29 39.79 52.57 67.65 72.97 77.30
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
Other Liabilities (excl. Deferred Income) 1.66 1.72 2.80 4.80 7.14 10.17 15.12 23.64 18.22 19.75
Deferred Income 0.24 0.50 1.00 1.40 2.00 1.90 0.00 2.90 5.70
Total Liabilities 45.60 53.62 66.74 103.60 119.10 163.19 227.79 282.30 316.63 325.98
Equity
Common Equity 10.74 13.38 19.29 27.71 43.55 62.06 93.40 138.25 146.04 201.88 199.85 196.14 194.12 190.41 188.39
Common Stock Par/Carry Value 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.11 0.11
Additional Paid-In Capital/Capital Surplus 11.14 13.39 17.19 21.39 26.79 33.66 42.87 55.54 75.07 99.03
Retained Earnings 1.95 2.55 4.92 8.64 19.63 31.22 52.55 85.92 83.19 113.62
Cumulative Translation Adjustment/Unrealized For. Exch. Gain -0.51 -0.72 -1.00
Unrealized Gain/Loss Marketable Securities 0.00 -0.00 0.02 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Other Appropriated Reserves -0.48 -1.04 -0.99 -0.18 -1.38 -4.49 -3.04
Treasury Stock -1.84 -1.84 -1.84 -1.84 -1.84 -1.84 -1.84 -1.84 -7.84 -7.84
Total Shareholders' Equity 10.74 13.38 19.29 27.71 43.55 62.06 93.40 138.25 146.04 201.88
Total Equity 10.74 13.38 19.29 27.71 43.55 62.06 93.40 138.25 146.04 201.88
Total Liabilities & Shareholders' Equity 56.34 67.01 86.02 131.31 162.65 225.25 321.20 420.55 462.68 527.85 440.85 437.14 435.12 431.41 429.39
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.
(305.23) (301.52) (299.50) (295.79) (293.77)
Net working capital ex cash -11.03 -14.94 -19.42 -23.83 -25.46 -32.49 -62.19 -61.07 -64.14 -60.90

FCFF

Net Income
Depreciation
Interest
OCF 0
∆NWC (Exclude cash & short term debt)
Capex (∆ Net Fixed asset + depreciation)
FCF 0

FCFE

Net Income
Depreciation
OCF(Equity) 0
∆NWC (Exclude cash & short term debt)
Capex (∆ Net Fixed asset + depreciation)
∆(Long term debt + short term debt)
FCF 0