DCF Model Builder

Overview

This skill creates institutional-quality DCF models for equity valuation following investment banking standards. Each analysis produces a detailed Excel model (with sensitivity analysis included at the bottom of the DCF sheet).

Tools

• Default to using all of the information provided by the user and MCP servers available for data sourcing.

Critical Constraints - Read These First

These constraints apply throughout all DCF model building. Review before starting:

Environment: Office JS vs Python/openpyxl:

• If running inside Excel (Office Add-in / Office JS environment): Use Office JS directly — do NOT use Python/openpyxl. Write formulas via range.formulas = [["=D19*(1+$B$8)"]]. No separate recalc step needed; Excel calculates natively. Use range.format.* for styling. The same formulas-over-hardcodes rule applies: set .formulas, never .values for derived cells.
• If generating a standalone .xlsx file (no live Excel session): Use Python/openpyxl as described below, then run recalc.py before delivery.
• The rest of this skill uses openpyxl examples — translate to Office JS API calls when in that environment, but all principles (formula strings, cell comments, section checkpoints, sensitivity table loops) apply identically.

⚠️ Office JS merged cell pitfall: When building section headers with merged cells, do NOT call .merge() then set .values on the merged range — Office JS still reports the range's original dimensions and will throw InvalidArgument: The number of rows or columns in the input array doesn't match the size or dimensions of the range. Instead, write the value to the top-left cell alone, then merge and format the full range:

// WRONG — throws InvalidArgument:
const hdr = ws.getRange("A7:H7");
hdr.merge();
hdr.values = [["MARKET DATA & KEY INPUTS"]];  // 1×1 array vs 1×8 range → fails

// CORRECT — value first on single cell, then merge + format the range:
ws.getRange("A7").values = [["MARKET DATA & KEY INPUTS"]];
const hdr = ws.getRange("A7:H7");
hdr.merge();
hdr.format.fill.color = "#1F4E79";
hdr.format.font.bold = true;
hdr.format.font.color = "#FFFFFF";

This applies to every merged section header in the DCF (market data, scenario blocks, cash flow projection, terminal value, valuation summary, sensitivity tables).

Formulas Over Hardcodes (NON-NEGOTIABLE):

• Every projection, margin, discount factor, PV, and sensitivity cell MUST be a live Excel formula — never a value computed in Python and written as a number
• When using openpyxl: ws["D20"] = "=D19*(1+$B$8)" is correct; ws["D20"] = calculated_revenue is WRONG
• The only hardcoded numbers permitted are: (1) raw historical inputs, (2) assumption drivers (growth rates, WACC inputs, terminal g), (3) current market data (share price, debt balance)
• If you catch yourself computing something in Python and writing the result — STOP. The model must flex when the user changes an assumption.

Verify Step-by-Step With the User (DO NOT build end-to-end):

• After data retrieval → show the user the raw inputs block (revenue, margins, shares, net debt) and confirm before projecting
• After revenue projections → show the projected top line and growth rates, confirm before building margin build
• After FCF build → show the full FCF schedule, confirm logic before computing WACC
• After WACC → show the calculation and inputs, confirm before discounting
• After terminal value + PV → show the equity bridge (EV → equity value → per share), confirm before sensitivity tables
• Catch errors at each stage — a wrong margin assumption discovered after sensitivity tables are built means rebuilding everything downstream

Sensitivity Tables:

• Use an ODD number of rows and columns (standard: 5×5, sometimes 7×7) — this guarantees a true center cell

…