Financial Model Builder

---
name: financial-model-builder
description: Use when building a multi-tab financial model in Excel — SaaS, e-commerce, or services. Triggers on "build a financial model", "SaaS model", "3-statement model", "5-year projection", "financial model xlsx".
---

# Financial Model Builder

Build a driver-based financial model that holds up to scrutiny. The reason most models fail in due diligence isn't the math — it's that assumptions are buried in formulas, the same number is hardcoded in five places, and changing pricing breaks half the workbook. Drivers in their own tab. Formulas reference them. Never hardcode in the P&L.

## Required inputs

1. **Business model** — SaaS / e-comm / services / marketplace (each has different revenue mechanics)
2. **Time horizon** — usually 36 monthly periods, with annual rollups
3. **Pricing** — plan tiers, ARPU, AOV, take rate, hourly rate — whatever maps to your model
4. **Volume drivers** — new customers/month, sessions, conversion, sales rep capacity
5. **Retention** — monthly churn % (SaaS), repeat rate (e-comm), renewal rate (services)
6. **Cost structure** — COGS as % of revenue or per-unit, fixed opex categories, headcount plan
7. **Headcount** — current team + planned hires by month, by role, with fully-loaded cost
8. **Starting cash** and any planned raises

If the user gives you a single revenue number ("we'll do $5M next year"), push back. A model needs the build, not the answer.

## Approach

Use **openpyxl** for .xlsx with formulas — it preserves cells as live formulas, not values. **xlsxwriter** is faster but write-only and can't be reopened to edit. For Google Sheets, use **gspread** + the Sheets API; named ranges are a separate API call.

Never compute the model in pandas and dump values. The user needs to change a driver and see the workbook recompute. That's the whole point.

## Workbook structure

Eight tabs, in this order:

1. **Cover** — model name, version, date, contact, color legend (blue = input, black = formula, green = link)
2. **Assumptions** — every driver in named ranges. Pricing, churn, conversion, rep capacity, opex categories, payroll rates. ONE place. ALL inputs blue.
3. **Revenue** — monthly build. Customers acquired, churned, ending. Revenue per cohort (or per channel). Roll up to top-line MRR/GMV/billings.
4. **COGS** — hosting, payment processing, fulfillment, contractor pass-through. Tied to revenue or volume from the Revenue tab.
5. **Headcount** — one row per planned role. Start month, end month (if any), fully-loaded cost from Assumptions. SUMIFS to roll into payroll.
6. **Opex** — non-payroll categories: marketing (often a % of revenue or CAC × new customers), tools, rent, professional services. Reference Assumptions.
7. **P&L** — monthly summary. Revenue, COGS, gross profit, opex by category, EBITDA. NO hardcoded numbers. Every cell is a formula referencing the build tabs.
8. **Cash flow** — opening cash, +EBITDA (proxy), -capex, +/- working capital, +financing. Closing cash row. This is the line investors look at first.

Optional: **Outputs** tab with the 6 charts and KPIs you'd actually present (ARR, gross margin %, burn, runway months, CAC payback, LTV/CAC).

## Formula principles

- **Named ranges for every driver.** `Churn_Monthly`, `ARPU_Pro`, `CAC_Blended`. Formulas read like English: `=New_Customers * ARPU_Pro`.
- **One formula per row, dragged across columns.** If row 14 has 36 different formulas, you have 36 bugs waiting.
- **SUMIFS over SUMIF** — handles multi-criteria headcount rollups cleanly.
- **EOMONTH** for period ends, never typed dates.
- **IFERROR around division** — `=IFERROR(Revenue/Customers, 0)` — divide-by-zero in month 0 breaks downstream charts.
- **XLOOKUP over VLOOKUP** for any modern Excel. Defaults to exact match, doesn't break on column insert.

## Common pitfalls

- **Hardcoded values in P&L formulas** — `=Revenue*0.4` for COGS. The 0.4 belongs in Assumptions as `COGS_Pct`. Always.
- **Circular references for interest on cash** — fine if intentional, but enable iterative calc and document it. Otherwise, a hidden circ kills the model.
- **Merged cells** — break sorting, break filling, break named ranges. Use "Center Across Selection" instead.
- **Rolling churn applied to the wrong base** — apply to start-of-period customers, not end. Off-by-one compounds.
- **Headcount cost without ramp** — a senior hire isn't 100% productive in month 1. Optional ramp factor in Assumptions.
- **No version cell** — when the CFO emails back "your numbers don't match mine," you need to know which model they're looking at.

## Output

Generate the .xlsx and print a summary: starting MRR, ending MRR, ending headcount, peak burn, runway months, and the 3 driver assumptions the model is most sensitive to (so the user knows where to stress-test). If the user wants a Google Sheets version, mirror the structure via gspread and recreate named ranges via batch update.