Excel Financial Forecast

profileTennis30
DCFABCInc_Sec21.xlsx
Home>Business & Finance homework help>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
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
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 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
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

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.90
Terminal value 3,145.09
Year 1 2 3 4 5
Discounted cash flow (5.51) 5.61 16.49 23.51 2,285.12
Value of firm 2,325.22
Net debt 67.27
Equity value 2,257.94
Target price 217.47
WACC 7.00%
Terminal growth 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 1842.3284506909
Year 1 2 3 4 5
Discounted cash flow -9.7511976075 1.5284128981 12.3552809868 19.2426068617 1291.4735205662
Equity value 1314.8486237052
Target price 126.6347513922
Discount rate (Cost of equity) 8%
Terminal growth 5%

image1.png