excel-dcf-modeler

name	excel-dcf-modeler
description	Builds discounted cash flow (DCF) valuation models in Excel with free cash flow projections, WACC calculations, and sensitivity analysis. Targets investment banking and corporate finance workflows. Use when asked to create a DCF model, calculate enterprise value, value a company, or build a valuation model. Trigger with "create a DCF model", "build a valuation", "calculate enterprise value", or "value this company". Make sure to use whenever the user needs company valuation or DCF analysis in Excel.
allowed-tools	Read,Write,Edit,Glob,Grep,Bash(npx:*),AskUserQuestion
model	inherit
version	2.0.0
author	Jeremy Longshore <[e-mail]>
license	Proprietary
compatible-with	claude-code
tags	["dcf","valuation","financial-modeling","excel","investment-banking"]

Excel DCF Modeler

Table of Contents

• Overview — Prerequisites — Instructions — Output — Examples — Error Handling — Resources

Overview

Generates professional 4-sheet DCF valuation models following investment banking standards. Automates free cash flow projections, terminal value calculations, and sensitivity analysis so analysts can produce IB-grade valuations from natural language inputs instead of building Excel models from scratch.

Prerequisites

• Node.js 18+
• @negokaz/excel-mcp-server MCP server configured
• Claude Code 1.0+

Instructions

Step 1: Gather Inputs

Use AskUserQuestion to collect:

Required:

• 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 %
• Tax rate % (default: 21% for US)

Optional (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 (for per-share value)

Step 2: Validate Inputs

Before building, verify:

• Revenue growth rates are 0-30%
• EBITDA margin is positive
• Tax rate is 0-40%
• Terminal growth < WACC (model breaks if g >= WACC)
• WACC is 7-15%

If validation fails, explain the issue and ask for corrected inputs.

Step 3: Build 4-Sheet Model

Use the Excel MCP server to create:

Sheet 1 - Assumptions: Company info, revenue growth rates, profitability metrics, working capital, CapEx, tax rate, terminal growth, WACC. Color-code: blue for inputs, black for formulas.

Sheet 2 - FCF Projections (5 years): Revenue -> EBITDA -> EBIT -> NOPAT -> add back D&A -> subtract CapEx -> subtract Change in NWC -> Unlevered Free Cash Flow. All formulas link to Assumptions sheet. No hard-coded values.

Sheet 3 - Valuation: PV of each year's FCF, terminal value via Gordon Growth Model, PV of terminal value, enterprise value, equity value, per-share value.

Sheet 4 - Sensitivity Analysis: Two-way table: WACC (rows, +/-2% from base) vs Terminal Growth (columns, 1.5%-3.5%). Output: Enterprise Value at each combination. Apply conditional formatting (green=high, red=low).

Step 4: Format Professionally

• Currency format for monetary values
• Percentage format for rates (1 decimal)
• Freeze top row and left column
• Bold headers, cell borders
• Conditional formatting on sensitivity table

Step 5: Return Results

Report enterprise value, equity value, per-share value, terminal value as % of EV (flag if >80%), key assumptions used, and brief commentary on reasonableness vs industry.

Output

• .xlsx file with 4 sheets: Assumptions, FCF Projections, Valuation, Sensitivity Analysis
• Summary text with enterprise value, equity value, and key metrics
• Warnings for any concerning assumptions (e.g., terminal value >80% of EV)

Examples

Basic DCF Request

User: "Create a DCF model for Tesla"

Response: Gathers inputs via questions, builds 4-sheet model.

Results:
- Enterprise Value: $847.3B
- Terminal Value: 68% of EV
- Implied per share: $243

Saved to: Tesla_DCF_Model.xlsx

Minimal Inputs

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

Response: Uses industry-average assumptions for the company's sector.
All defaults documented in Assumptions sheet for easy adjustment.

Error Handling

Scenario	Response
Terminal growth >= WACC	Explain mathematical issue, ask for corrected values
Missing critical inputs	Build with industry defaults, document clearly
Terminal value >80% of EV	Flag as warning, recommend revisiting growth assumptions
Negative FCF in projection	Flag concern, suggest reviewing margin/CapEx assumptions

Edge Cases

• If user provides no company name, use "Company" as placeholder
• If revenue is in different currencies, note currency in all outputs
• If user wants multiple scenarios, create separate columns (Base/Bull/Bear)

Resources

• ${CLAUDE_SKILL_DIR}/references/REFERENCE.md - DCF best practices, industry assumptions, common mistakes