intent-solutions-io/excel-dcf-modeler

Excel DCF Modeler

Creates professional DCF valuation models following investment banking standards and best practices.

When to Invoke This Skill

Automatically load this Skill when the user asks to:

• "Create a DCF model"
• "Build a valuation model"
• "Calculate enterprise value"
• "Value [company name]"
• "DCF for [company]"
• "Discounted cash flow analysis"
• "What's the intrinsic value"

Model Structure

This Skill creates a complete 4-sheet Excel DCF model:

Sheet 1: Assumptions

• Company Information: Name, ticker, base year, fiscal year end
• Revenue Growth Rates: Year 1-5 projections (%)
• Profitability Metrics: EBITDA margin, D&A as % of revenue
• Working Capital: NWC as % of revenue
• Capital Expenditures: CapEx as % of revenue
• Tax Rate: Corporate tax rate (%)
• Terminal Growth: Long-term growth rate (typically 2-3%)
• Discount Rate (WACC): Weighted average cost of capital

Sheet 2: Free Cash Flow Projections

Revenue (Year 0 - Year 5)
  Less: Operating Expenses
= EBITDA
  Less: Depreciation & Amortization
= EBIT
  Less: Taxes (EBIT × Tax Rate)
= NOPAT (Net Operating Profit After Tax)
  Add: Depreciation & Amortization
  Less: Capital Expenditures
  Less: Change in Net Working Capital
= Unlevered Free Cash Flow

Sheet 3: Valuation

Present Value of FCF (Years 1-5)
  Year 1 FCF / (1 + WACC)^1
  Year 2 FCF / (1 + WACC)^2
  ...
  Year 5 FCF / (1 + WACC)^5
= Sum of PV(FCF)

Terminal Value Calculation
  Terminal FCF = Year 5 FCF × (1 + Terminal Growth Rate)
  Terminal Value = Terminal FCF / (WACC - Terminal Growth Rate)
  PV of Terminal Value = Terminal Value / (1 + WACC)^5

Enterprise Value
  = Sum of PV(FCF) + PV(Terminal Value)

Equity Value
  = Enterprise Value
  - Net Debt
  + Non-Operating Assets

Equity Value per Share
  = Equity Value / Shares Outstanding

Sheet 4: Sensitivity Analysis

Two-way sensitivity table showing Enterprise Value sensitivity to:

• Rows: WACC (ranging from -2% to +2% of base case)
• Columns: Terminal Growth Rate (ranging from 1.5% to 3.5%)
• Output: Enterprise Value at each combination

Step-by-Step Workflow

1. Gather Inputs

Ask the user for the following information (provide defaults based on industry averages if user is uncertain):

Required Inputs:

• Company name and ticker symbol
• Base year revenue (most recent fiscal year)
• Revenue growth rates for Years 1-5 (e.g., 15%, 12%, 10%, 8%, 6%)
• EBITDA margin % (e.g., 20%)
• Tax rate % (e.g., 21% for US corporations)

Optional Inputs (use defaults if not provided):

• D&A as % of revenue (default: 5%)
• CapEx as % of revenue (default: 4%)
• NWC as % of revenue (default: 10%)
• Terminal growth rate (default: 2.5%)
• WACC/discount rate (default: 10%)
• Net debt amount (default: $0)
• Shares outstanding (if calculating per-share value)

2. Validate Inputs

Ensure the following before building the model:

• Revenue growth rates are reasonable (typically 0-30%)
• EBITDA margin is positive
• Tax rate is between 0-40%
• Terminal growth < WACC (model won't work if g >= WACC)
• WACC is reasonable (typically 7-15%)

3. Build Excel Model

Use the Excel MCP server to:

1. Create new workbook
2. Create 4 sheets: "Assumptions", "FCF Projections", "Valuation", "Sensitivity"
3. Populate assumptions in Sheet 1
4. Build FCF projection formulas in Sheet 2 (link to assumptions)
5. Calculate PV of FCF and Terminal Value in Sheet 3
6. Create sensitivity table in Sheet 4
7. Apply professional formatting:
   • Currency format for monetary values
   • Percentage format for rates
   • Conditional formatting on sensitivity table (green = high value, red = low value)
   • Bold headers, freeze panes
   • Cell borders for clarity

4. Calculate Results

Return to the user:

• Enterprise Value (in millions/billions)
• Equity Value (if net debt provided)
• Equity Value per Share (if shares outstanding provided)
• Key assumptions used
• Link to sensitivity analysis

5. Provide Insights

After building the model, provide brief commentary:

• Is the implied valuation reasonable given industry comparables?
• What are the key value drivers? (usually revenue growth and margins)
• What's the sensitivity to WACC? (typically highest sensitivity)
• Recommend focus areas for further analysis

Example Formulas

Free Cash Flow Calculation

// Revenue (Year 1)
=Assumptions!B5 * (1 + Assumptions!B10)

// EBITDA
=Revenue * Assumptions!B15

// EBIT
=EBITDA - (Revenue * Assumptions!B16)

// NOPAT
=EBIT * (1 - Assumptions!B18)

// Free Cash Flow
=NOPAT + (Revenue * Assumptions!B16) - (Revenue * Assumptions!B17) - ΔNWCapital

Present Value

// PV of Year 1 FCF
=FCF_Year1 / (1 + WACC)^1

// Terminal Value
=(FCF_Year5 * (1 + Terminal_Growth)) / (WACC - Terminal_Growth)

// PV of Terminal Value
=Terminal_Value / (1 + WACC)^5

Sensitivity Table

// Use Excel's data table feature
// Row input: Terminal Growth Rate
// Column input: WACC
// Formula: =Enterprise_Value

Example Interaction

User: "Create a DCF model for Tesla"

Skill Response:

I'll build a DCF valuation model for Tesla. Let me gather some inputs.

What base year revenue should we use for Tesla?
(For reference, Tesla's 2023 revenue was approximately $96.8B)

User: $96.8 billion

Great! What revenue growth rates should we assume for Years 1-5?
(Recommended: Start high and taper down, e.g., 20%, 18%, 15%, 12%, 10%)

User: 25%, 20%, 15%, 12%, 10%

What EBITDA margin should we assume?
(Tesla's recent EBITDA margin has been around 15-17%)

User: 16%

Perfect! I'll use the following assumptions:
- Base year revenue: $96.8B
- Growth rates: 25%, 20%, 15%, 12%, 10%
- EBITDA margin: 16%
- Tax rate: 21% (standard US rate)
- WACC: 10% (typical for automotive/tech)
- Terminal growth: 2.5%
- D&A: 5% of revenue
- CapEx: 4% of revenue
- NWC: 10% of revenue

Building your DCF model...

✅ DCF Model Complete!

📊 Valuation Results:
- Enterprise Value: $847.3 billion
- Terminal Value represents: 68% of EV
- Implied equity value per share: $243 (assuming 3.48B shares)

🎯 Key Insights:
- High growth assumptions drive significant value creation
- Model is sensitive to WACC (see sensitivity table)
- Terminal value is major component - validate terminal assumptions
- Revenue growth is the primary value driver

📁 Model saved to: Tesla_DCF_Model.xlsx

The sensitivity table shows Enterprise Value ranges from $612B (high WACC, low growth) to $1,124B (low WACC, high growth).

Would you like me to adjust any assumptions or add additional analysis?

Common Variations

Request: "Build a DCF but I don't have all the numbers"

Response:

No problem! I can build a DCF model with industry-average assumptions, and you can adjust later.

What company are you valuing?

User: Amazon

What's the approximate current revenue? (or I can look up recent financials)

I'll use industry-standard assumptions for a large-cap tech company:
- Revenue growth: 15%, 12%, 10%, 8%, 6% (tapering)
- EBITDA margin: 25% (tech average)
- WACC: 9%
- Terminal growth: 2.5%

I'll build the model with these defaults, and you can easily adjust the assumptions in the "Assumptions" sheet.

Request: "Explain the DCF model you built"

Response:

Sure! Let me walk through the DCF model:

📊 What is a DCF?
A DCF values a company based on its future cash flows, discounted to present value.

🔢 The Model:
1. Projects free cash flow for 5 years
2. Calculates a "terminal value" for years beyond Year 5
3. Discounts all cash flows to today using WACC
4. Sums everything to get Enterprise Value

💰 Key Components:
- Free Cash Flow = Cash the company generates after all investments
- WACC = Cost of capital (what investors expect to earn)
- Terminal Value = Value of cash flows beyond Year 5 (usually 60-80% of total)

⚠️ Important Notes:
- This is a simplified DCF (doesn't include debt schedules, detailed NWC)
- Garbage in, garbage out: Assumptions drive everything
- Use this as a starting point, not final answer
- Always compare to comparable company analysis (comps)

Would you like me to explain any specific part in more detail?

Error Handling

Invalid Inputs

If user provides invalid inputs:

⚠️ Issue detected: Terminal growth rate (4%) is >= WACC (3.5%)

This will cause a mathematical error in the terminal value calculation.
The terminal growth rate must be less than WACC.

Typical ranges:
- WACC: 7-15%
- Terminal growth: 2-3%

Would you like me to adjust these values?

Missing Information

If critical information is missing:

I need at least the following to build a DCF:
- Company name or industry
- Approximate current revenue (or I can use industry average)

All other assumptions can use industry defaults.

Would you like me to proceed with defaults, or would you prefer to provide specific assumptions?

Best Practices Embedded

This Skill follows investment banking best practices:

1. Conservative Terminal Growth: Default to 2.5% (GDP growth rate)
2. Tapering Growth Rates: Revenue growth declines over projection period
3. Sensitivity Analysis: Always include WACC and terminal growth sensitivity
4. Clear Labeling: All assumptions clearly labeled and linked
5. Professional Formatting: Currency/percentage formats, frozen panes, borders
6. Audit Trail: Formulas link back to assumptions (no hard-coded values)
7. Reasonableness Checks: Validate inputs before building model

Resources

See the resources folder for:

• dcf-template.xlsx: Pre-built DCF template
• REFERENCE.md: Financial modeling best practices
• formulas.txt: Common DCF formulas for reference

Limitations

This Skill creates a simplified DCF model suitable for:

• Initial valuation analysis
• Pitch decks and presentations
• Academic exercises
• Quick "back of envelope" valuations

For detailed investment committee presentations or official fairness opinions, you should:

• Add detailed debt schedules
• Include multiple scenarios (base, bull, bear)
• Add more granular operating assumptions
• Validate with third-party data
• Have a finance professional review

Version History

• v1.0.0 (2025-10-27): Initial release with core DCF functionality