Generador de Modelos DCF para Analistas Financieros

Here is the step-by-step DCF model build, structured for an MBA graduate, with the rigor expected in a McKinsey engagement. --- **STEP 1: FRAMING & INPUTS (The "Clean Room")** - **Objective:** Define the valuation date, currency, and the exact scope of the business (e.g., consolidated vs. core operations). - **Inputs:** Gather the last 3 years of historical financials (Income Statement, Balance Sheet, Cash Flow Statement). Segment revenue by product/service line and geography. - **Key Inputs for your company:** Based on your description, I will use the following placeholders. You must fill these in: - **Revenue:** `[REVENUE_Y1]`, `[REVENUE_Y2]`, `[REVENUE_Y3]` - **EBITDA Margin:** `[EBITDA_MARGIN_Y1]`, `[EBITDA_MARGIN_Y2]`, `[EBITDA_MARGIN_Y3]` - **Capex Intensity:** `[CAPEX_AS_PCT_OF_REVENUE]` (e.g., 5% for software, 15% for manufacturing) - **Industry:** `[INDUSTRY]` (e.g., SaaS, Industrials, Consumer Retail) --- **STEP 2: REVENUE PROJECTIONS (The "Top Line")** - **Methodology:** Build a bottom-up forecast, not a single growth rate. Drive revenue by volume and price, or by customer count and ARPU (Average Revenue Per User). - **Formula:** `Revenue (Year N) = Revenue (Year N-1) * (1 + Growth Rate N)` - **Forecast Horizon:** 5 years (Years 1-5). Do not go beyond 10 years. - **McKinsey Rule:** The growth rate in Year 5 must be below the long-term GDP growth rate of the country of operation. --- **STEP 3: OPERATING MARGINS & EBIT (The "Core")** - **Project EBITDA Margin:** Do not assume a linear improvement. Model a path to a "steady-state" margin by Year 5. - **Formula:** `EBITDA (Year N) = Revenue (Year N) * EBITDA Margin (Year N)` - **D&A (Depreciation & Amortization):** Project D&A as a percentage of revenue or as a percentage of the prior year's Net PP&E (Property, Plant & Equipment) plus new capex. - **Formula:** `EBIT (Year N) = EBITDA (Year N) - D&A (Year N)` --- **STEP 4: FREE CASH FLOW (FCF) BRIDGE (The "Engine")** - **Taxes:** Apply a normalized cash tax rate. Use the statutory rate (e.g., 21% in the US) unless you have specific NOL (Net Operating Loss) carryforwards. - **Capex:** Use your specific `[CAPEX_GROWTH_PCT_OF_REVENUE]` assumption. - **Working Capital (WC):** Model the change in Net Working Capital (NWC) as a percentage of the change in revenue. - **Formula:** `Change in NWC = (Revenue N - Revenue N-1) * (NWC as % of Revenue)` - **Note:** A positive change in NWC is a cash outflow (use negative sign). - **The Core FCF Formula:** `FCF (Year N) = EBIT (Year N) * (1 - Tax Rate) + D&A (Year N) - Capex (Year N) - Change in NWC (Year N)` --- **STEP 5: WACC CALCULATION (The "Discount Rate")** - **Formula:** `WACC = (E/V) * Cost of Equity + (D/V) * Cost of Debt * (1 - Tax Rate)` - **Cost of Equity (via CAPM):** `Cost of Equity = Risk-Free Rate + Beta * Equity Risk Premium` - **Risk-Free Rate:** Use the 10-year US Treasury yield (e.g., 4.0%). - **Beta:** Use a levered beta from a comparable public company set, then relever it to your target capital structure. - `Unlevered Beta = Levered Beta / (1 + (1 - Tax Rate) * (Debt/Equity))` - `Relevered Beta = Unlevered Beta * (1 + (1 - Tax Rate) * (Target Debt/Equity))` - **Equity Risk Premium (ERP):** Use a standard range of 4.5% - 5.5%. - **Cost of Debt:** `Pre-Tax Cost of Debt = Risk-Free Rate + Credit Spread` (based on the company's credit rating). - **Capital Structure Weights:** Use the *target* capital structure (market values), not the current book values. --- **STEP 6: TERMINAL VALUE (The "Long Tail")** - **Method 1: Gordon Growth Model (Perpetuity Growth)** - **Formula:** `Terminal Value (TV) = FCF (Year 5) * (1 + Terminal Growth Rate) / (WACC - Terminal Growth Rate)` - **Terminal Growth Rate:** Use the long-term inflation rate (e.g., 2.0% - 2.5%). Never exceed the GDP growth rate. - **Method 2: Exit Multiple Method** - **Formula:** `Terminal Value (Year 5) = EBITDA (Year 5) * Exit Multiple` - **Exit Multiple:** Use the current median EV/EBITDA multiple of the public comps, but apply a discount (e.g., 10-20%) to reflect the cyclicality or lack of liquidity. - **McKinsey Rule:** Always cross-check both methods. If they diverge by more than 20%, your assumptions are inconsistent. --- **STEP 7: ENTERPRISE VALUE & EQUITY VALUE (The "Bridge")** - **Enterprise Value (EV):** `EV = PV of FCF (Years 1-5) + PV of Terminal Value` - Discount each year's FCF and the Terminal Value back to today using the WACC. - **Equity Value:** `Equity Value = EV - Net Debt + Cash - Non-Controlling Interests - Pension Deficits` - **Per Share Value:** `Equity Value / Diluted Shares Outstanding` --- **STEP 8: SENSITIVITY TABLE (The "What-If")** - **Build a 2-Dimensional Data Table:** - **Rows:** WACC (e.g., 8% to 12% in 0.5% increments). - **Columns:** Terminal Growth Rate (e.g., 1.5% to 3.5% in 0.5% increments). - **Output:** Implied Equity Value per Share. - **Interpretation:** The table shows the range of outcomes. The "base case" should sit in the middle of the table, not at the edge. --- **STEP 9: COMMON MISTAKES & HOW TO AVOID THEM** 1. **Circularity in WACC:** Do not use the WACC to calculate the capital structure weights. Use a target weight. 2. **Mismatched Cash Flows:** Ensure FCF is unlevered (pre-interest) and discounted at WACC. If you use levered FCF, discount at Cost of Equity. 3. **Working Capital Sign Error:** A *decrease* in NWC (e.g., collecting receivables faster) is a *source* of cash (add it back). An *increase* is a use of cash (subtract it). 4. **Terminal Value Dominance:** If the Terminal Value is > 80% of EV, your forecast horizon is too short or your growth rate is too high. 5. **Ignoring the Balance Sheet:** Ensure your model is internally consistent. The balance sheet must balance each year (Assets = Liabilities + Equity). --- **STEP 10: SANITY CHECKS (The "Reality Check")** - **Public Comps:** Compare your implied EV/EBITDA multiple to the current median of the public comps. If your model implies a 15x multiple while comps trade at 8x, your assumptions are too aggressive. - **Precedent Transactions:** Compare your implied multiple to the average multiple paid in recent M&A deals in the industry. M&A deals typically carry a 20-30% premium to public comps. - **Reverse DCF:** Solve for the implied growth rate or margin that the current market price is assuming. If the market is pricing in 20% growth and you only see 5% potential, you have a strong buy signal. --- **STEP 11: STRESS-TESTING (The "Killer Assumptions")** - **The "Blow-Up" Assumptions:** - **Revenue Growth:** A 1% change in the Year 1-3 growth rate can swing the valuation by 10-15%. - **EBITDA Margin:** A 100bps change in the terminal margin is often the single largest driver of value. - **WACC:** A 0.5% change in WACC can change the valuation by 5-10%. - **Stress Test:** Run a "Bear Case" (e.g., revenue growth halved, margin down 200bps) and a "Bull Case" (growth doubled, margin up 200bps). If the Bear Case is still above the current stock price, the stock is a clear buy. --- **STEP 12: THE MCKINSEY PARTNER PRESSURE TEST (The "Red Team")** - **The "So What?" Test:** The Partner will ask: "What is the single most important driver of value in this model?" You must answer instantly (e.g., "It's the terminal margin, not the growth rate"). - **The "Sensitivity" Challenge:** The Partner will point to a cell in the sensitivity table and ask: "What is the probability of this scenario occurring?" You must have a qualitative answer, not just a number. - **The "Reverse" Test:** The Partner will ask: "What would the WACC have to be for the equity value to be zero?" This tests the robustness of your downside. - **The "Story" Test:** The Partner will ask: "Tell me the story of this company in 3 sentences." If the model is not intuitive, it is wrong. The model must reflect the business reality (e.g., "This is a high-growth, low-margin business that will generate cash only after Year 3 when capex normalizes"). --- **FINAL OUTPUT FORMAT** Present the model in a single Excel workbook with the following tabs: 1. **Assumptions** (All inputs in blue font). 2. **Income Statement** (5 years historical + 5 years projected). 3. **Cash Flow** (FCF bridge). 4. **WACC** (Full calculation). 5. **Valuation** (EV to Equity bridge). 6. **Sensitivity** (Data table). 7. **Comps** (Public market data). --- **To complete the model, please provide the specific data for your company:** - **Business Description:** (e.g., "A B2B SaaS company with 90% recurring revenue") - **Industry:** (e.g., "Enterprise Software") - **Last 3 Years Revenue:** (e.g., $100M, $120M, $150M) - **Last 3 Years EBITDA Margins:** (e.g., 15%, 18%, 22%) - **Capex Intensity:** (e.g., 3% of revenue, or $5M per year)