BIWS - Mid-Year-Stub-Discount-Simple-DCF

.xlsx

School

Pepperdine University *

*We aren’t endorsed by this school

Course

668

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

2

Uploaded by AmbassadorSharkMaster998

Report
0.0% 0.5% 1.0% 1.5% 2.0% 2.5% 3.0% 3.5% 4.0% Chart Title
Simple DCF Analysis - Mid-Year Convention and Stub Periods ($ in Millions Except Per Share and Per Unit Data) Discounted Cash Flow (DCF) Analysis - Assumptions and Output: Use Mid-Year Convention in Calculations? 1 Terminal Value - Multiples Method: Terminal Value - Perpetuity Growth Method: Valuation Date: Apr 30 Stub Period: 0.671 Baseline Terminal EBITDA Multiple: 8.0 x Baseline Terminal FCF Growth Rate: 1.2% % Cash Flow Generated in Year 1 to Date: 33.3% Baseline Terminal Value: $ 16,638.9 Baseline Terminal Value: $ 15,878.4 Cash Flow to Subtract in Projected Year 1: $ 100.1 Terminal Value Discount Period: 9.671 Terminal Value Discount Period: 9.171 Effective Tax Rate: 25.0% Years in Projection Period: 10 (+) PV of Terminal Value: 6,619.2 (+) PV of Terminal Value: 6,625.0 (+) Sum of PV of Free Cash Flows: 5,286.9 (+) Sum of PV of Free Cash Flows: 5,286.9 Risk-Free Rate: 1.80% Implied Enterprise Value: $ 11,906.1 Implied Enterprise Value: $ 11,911.9 Equity Risk Premium: 5.20% Median Unlevered Beta for Comparables: 1.50 Company - Debt / Total Capital Ratio: 20.0% Company - Debt / Equity Ratio: 25.0% cost debt 3.8% 0.7500% Re-Levered Beta: 1.78 weight debt 20.0% Cost of Equity: 11.06% cost equity 11.06% 8.8500% weight equity 80.0% Pre-Tax Cost of Debt: 5.00% 1.78125 0.110625 1.891875 Discount Rate (WACC): 10.00% Projected: Unlevered Free Cash Flow Projections: Units: Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Revenue: $ M $ 1,176.9 $ 1,553.3 $ 1,998.0 $ 2,554.3 $ 3,174.8 $ 3,853.2 $ 4,323.1 $ 4,567.8 $ 4,701.2 $ 4,814.5 Operating Income (EBIT): $ M 447.2 590.2 779.2 996.2 1,269.9 1,541.3 1,729.2 1,827.1 1,880.5 1,925.8 (-) Taxes, Excluding Effect of Interest: $ M (111.8) (147.6) (194.8) (249.0) (317.5) (385.3) (432.3) (456.8) (470.1) (481.5) Net Operating Profit After Tax (NOPAT): $ M 335.4 442.7 584.4 747.1 952.4 1,156.0 1,296.9 1,370.3 1,410.4 1,444.4 (+) Depreciation & Amortization: $ M 42.4 54.4 67.9 84.3 101.6 123.3 138.3 146.2 150.4 154.1 (+/-) Changes in Operating Assets & Liabilities: $ M (24.7) (29.0) (32.0) (37.8) (39.1) (40.7) (28.2) (14.7) (8.0) (6.8) (-) Capital Expenditures: $ M (53.0) (68.3) (85.9) (107.3) (133.3) (161.8) (181.6) (191.8) (197.5) (202.2) Annual Unlevered Free Cash Flow: $ M $ 300.2 $ 399.7 $ 534.4 $ 686.3 $ 881.6 $ 1,076.7 $ 1,225.5 $ 1,310.0 $ 1,355.3 $ 1,389.4 Unlevered Free Cash Flow for Remainder of Year: $ M 200.1 399.7 534.4 686.3 881.6 1,076.7 1,225.5 1,310.0 1,355.3 1,389.4 Standard Discount Period: # 0.671 1.671 2.671 3.671 4.671 5.671 6.671 7.671 8.671 9.671 Present Value of Unlevered FCF (Standard): $ M 187.7 340.9 414.3 483.7 564.8 627.1 648.9 630.6 593.1 552.7 Mid-Year Discount Period: # 0.336 1.171 2.171 3.171 4.171 5.171 6.171 7.171 8.171 9.171 Present Value of Unlevered FCF (Mid-Year): $ M 193.8 357.5 434.5 507.3 592.4 657.7 680.6 661.3 622.0 579.7 Annual Free Cash Flow Growth Rate: % 33.7% 33.2% 33.7% 28.4% 28.5% 22.1% 13.8% 6.9% 3.5% 2.5% Annual EBITDA: $ M $ 489.6 $ 644.6 $ 847.2 $ 1,080.5 $ 1,371.5 $ 1,664.6 $ 1,867.6 $ 1,973.3 $ 2,030.9 $ 2,079.9 Annual EBITDA Growth Rate: % 31.4% 31.7% 31.4% 27.5% 26.9% 21.4% 12.2% 5.7% 2.9% 2.4% Annual EBITDA Margin: % 41.6% 41.5% 42.4% 42.3% 43.2% 43.2% 43.2% 43.2% 43.2% 43.2%
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help