Spreadsheet: 840766d1692971170-discounted-cash-flow-what-if-analysis-showing-incorrect-values-correct-dcf
Sheet: Model

Simple Discounted Cash Flow Model
 |  | DCF Analysis |  |  |  |  |  |  |  |  |  |  |  | Assumptions
 |  | Year |  |  |  |  |  | 1 | 2 | 3 | 4 | 5
 |  |  |  |  |  |  |  | 2022 | 2023 | 2024 | 2025 | 2026 |  | Corporate Tax Rate |  | 0.4
 |  | Revenue |  |  |  |  |  |  |  |  |  |  |  | Discount Rate (WACC) (k) |  | 0.085
 |  | % Growth |  |  |  |  |  |  |  |  |  |  |  | Terminal Multiple |  | 12
 |  | EBITDA |  |  |  |  |  |  |  |  |  |  |  |  |  | 2022
 |  | % Margin |  |  |  |  |  |  |  |  |  | 0.42 |  | Revenues |  | 13600
 |  |  |  |  |  |  |  |  |  |  |  |  |  | YOY Growth |  | 0.05
 |  | Cash Taxes Calculation |  |  |  |  |  |  |  |  |  |  |  | EBITDA |  | 5400
 |  |  |  |  |  |  |  |  |  |  |  |  |  | % Margin (assume increasing to 42%)
 |  | Less: Depreciation and Amortization |  |  |  |  |  |  |  |  |  |  |  | Depreciation/Amort % Revenue |  | 0.007
 |  | EBIT |  |  |  |  |  |  |  |  |  |  |  | NWC as % of Revenue |  | 0.05
 |  | Cash Taxes |  |  |  |  |  |  |  |  |  |  |  | Capex |  | 0.005
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Cash |  | 9200
 |  | Capital Expenditures |  |  |  |  |  |  |  |  |  |  |  | Debt |  | 50
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Shares Outstanding |  | 1000
 |  | Net Working Capital
 |  | Change in NWC
 |  | Less: Cash Taxes
 |  | Less: Changes in W/C
 |  | Less: Capital Expenditures
 |  | Total Unlevered Free Cash Flow
 |  | Total Discounted Unlevered Free Cash Flow
 |  | PV of Unlevered FCF
 |  |  |  |  |  | Implied Enterprise / Equity Valuation from DCF Analysis
 |  |  |  |  |  | Forward (2026E) EBITDA
 |  |  |  |  |  | Terminal Multiple
 |  |  |  |  |  | Terminal Value
 |  |  |  |  |  | Present Value of Terminal Value
 |  |  |  |  |  | PV of Unlevered FCF
 |  |  |  |  |  | Plus: PV of Terminal Value
 |  |  |  |  |  | Implied TEV / Present Value
 |  |  |  |  |  | Plus: Cash
 |  |  |  |  |  | Less: Debt
 |  |  |  |  |  | Implied Equity Value
 |  |  |  |  |  | Shares Outstanding
 |  |  |  |  |  | Implied Share Price Valuation
 |  |  |  |  |  | Sensitivities
 |  |  |  |  |  | Enterprise Value ($M) |  |  |  |  |  |  |  | Step-ups
 |  |  |  |  |  |  |  | Terminal Multiple |  |  |  |  |  | Row | 1
 |  |  |  |  |  | EV |  |  |  |  |  |  |  | Column | 0.005
 |  |  |  |  |  |  |  |  | 58069.94895074973 | 58069.94895074973 | 62906.13582254729 | 72578.50956614241
 |  |  |  |  |  |  |  | 64250.31276523224 | 59300.06989858602 | 59300.06989858602 | 64250.31276523224 | 74150.7984985247
 |  |  |  |  | WACC |  |  | 64250.31276523224 | 59300.06989858602 | 59300.06989858602 | 64250.31276523224 | 74150.7984985247
 |  |  |  |  |  |  |  | 62906.13582254729 | 58069.94895074973 | 58069.94895074973 | 62906.13582254729 | 72578.50956614241
 |  |  |  |  |  |  |  | 60323.90003284501 | 55706.50847973888 | 55706.50847973888 | 60323.90003284501 | 69558.68313905728
 |  |  |  |  |  | Implied Share Price
 |  |  |  |  |  |  |  | Terminal Multiple
 |  |  |  |  |  | SP
 |  |  |  |  |  |  |  |  | 67.21994895074974 | 67.21994895074974 | 72.05613582254728 | 81.7285095661424
 |  |  |  |  |  |  |  | 73.40031276523224 | 68.45006989858602 | 68.45006989858602 | 73.40031276523224 | 83.3007984985247
 |  |  |  |  |  | WACC |  | 73.40031276523224 | 68.45006989858602 | 68.45006989858602 | 73.40031276523224 | 83.3007984985247
 |  |  |  |  |  |  |  | 72.05613582254728 | 67.21994895074974 | 67.21994895074974 | 72.05613582254728 | 81.7285095661424
 |  |  |  |  |  |  |  | 69.47390003284501 | 64.85650847973888 | 64.85650847973888 | 69.47390003284501 | 78.70868313905727