playbooks

home / skills / jst-well-dan / skill-box / excel-dcf-modeler

excel-dcf-modeler skill

/business-analyst/excel-dcf-modeler

This skill helps build professional discounted cash flow models in Excel, guiding valuation from assumptions to sensitivity analysis and enterprise value.

npx playbooks add skill jst-well-dan/skill-box --skill excel-dcf-modeler

Review the files below or copy the command above to add this skill to your agents.

Files (6)
SKILL.md
2.2 KB
---
name: excel-dcf-modeler
description: |
  Build discounted cash flow (DCF) valuation models in Excel. Use when creating
  DCF models, calculating enterprise value, or valuing companies.
  Trigger with phrases like 'excel dcf', 'build dcf model', 'calculate enterprise value'.
allowed-tools: Read, Write, Edit, Grep, Glob, Bash(cmd:*)
version: 1.0.0
author: Jeremy Longshore <[email protected]>
license: MIT
---

# Excel DCF Modeler

## Overview

Creates professional DCF valuation models following investment banking standards with WACC calculations and sensitivity analysis.

## Prerequisites

- Excel or compatible spreadsheet software
- Historical financial data for target company
- Industry comparables for WACC estimation

## Instructions

1. Create assumptions sheet with revenue growth, margins, WACC, and terminal growth rate
2. Build free cash flow projections (5-year forecast)
3. Calculate terminal value using Gordon Growth Model
4. Discount cash flows and terminal value to present value
5. Sum to get enterprise value, subtract net debt for equity value
6. Add sensitivity tables for key assumptions

## Output

- Complete 4-sheet DCF model with assumptions, projections, valuation, and sensitivity
- Enterprise value and equity value per share
- Sensitivity analysis on WACC and terminal growth rate

## Error Handling

| Error | Cause | Solution |
|-------|-------|----------|
| #DIV/0! in terminal value | WACC equals terminal growth | Terminal growth must be less than WACC |
| Negative FCF | High CapEx or WC needs | Review assumptions, may need different model |
| Unrealistic EV | Extreme growth assumptions | Benchmark against industry comparables |

## Examples

**Example: Value a SaaS Company**
Request: "Create a DCF model for a $50M ARR SaaS company growing 30%"
Result: 4-sheet model with 5-year projections, 12% WACC, 3% terminal growth, sensitivity tables

**Example: M&A Valuation**
Request: "DCF analysis for acquisition target"
Result: Model with synergy adjustments, scenario analysis, and per-share valuation

## Resources

- [Damodaran Online DCF Resources](https://pages.stern.nyu.edu/~adamodar/)
- [WSO DCF Modeling Guide](https://www.wallstreetoasis.com/)
- `{baseDir}/references/dcf-formulas.md` for Excel formula templates

Overview

This skill builds professional Excel discounted cash flow (DCF) valuation models that follow investment banking conventions. It produces a multi-sheet workbook with assumptions, five-year financial projections, terminal value, discounted cash flows, and sensitivity analysis. Use it to estimate enterprise and equity value and to produce per-share valuations for transactions or internal analysis.

How this skill works

The skill guides you through creating an assumptions sheet (revenue growth, margins, WACC, terminal growth), then generates five-year free cash flow projections and computes terminal value via the Gordon Growth Model. It discounts projected cash flows and terminal value to present value, sums to enterprise value, and subtracts net debt to derive equity value and per-share metrics. The model also builds sensitivity tables on WACC and terminal growth and flags common errors like WACC ≤ terminal growth or negative FCF.

When to use it

  • Valuing a company for investment, M&A, or internal planning
  • Creating a live Excel DCF template for repeatable valuations
  • Calculating enterprise value and equity value per share
  • Stress-testing outcomes with sensitivity or scenario analysis
  • Benchmarking assumptions against industry comparables

Best practices

  • Start with a clear assumptions sheet and document sources for historical inputs
  • Ensure terminal growth is materially less than WACC to avoid invalid terminal value
  • Use five-year forecasts for core projections and justify long-term growth rates
  • Cross-check WACC against industry comparable companies and adjust capital structure appropriately
  • Include sensitivity tables for WACC and terminal growth and review outputs for unrealistic values

Example use cases

  • Build a DCF for a $50M ARR SaaS company with 5-year revenue and margin ramp
  • Prepare an acquisition valuation with synergy adjustments and scenario variants
  • Generate per-share valuations for board materials or investor updates
  • Create a reusable Excel template for rolling valuations across portfolio companies

FAQ

What if terminal growth equals or exceeds WACC?

Terminal growth must be lower than WACC; otherwise the Gordon Growth formula produces division by zero or negative terminal value. Lower the terminal growth assumption or reassess WACC inputs.

How do I handle negative free cash flow in early years?

Negative FCF can be valid for high-growth or capex-heavy businesses. Review CapEx and working capital drivers, extend the forecast if needed, and ensure the model reflects realistic recovery assumptions.

How are sensitivity tables built?

The model creates two-way sensitivity tables that vary WACC and terminal growth (or other key assumptions) to show impacts on enterprise and equity value for quick scenario comparison.