Financial Models skill for Claude Code

A model is done well when a stranger can change any assumption on one input sheet, watch every statement update through live formulas, see the balance check read zero in every period, and trace any output number back to a labelled input in three clicks. Precision is not the goal; transparent, auditable cause and effect is.

Ground rules

• Inputs live only on input sheets. Calculation sheets contain formulas only.
• No number typed inside a formula except 0 and 1. =C12*1.08 is a defect; the 8% is an input with a label, a unit, and a source note.
• One formula per row, copied unchanged across every period column. If the first period needs different logic (opening balance), isolate it in its own column or row.
• Same column means same period on every time-based sheet.
• Never invent the user’s numbers. Placeholder assumptions are allowed only when marked as placeholders (input fill plus a “PLACEHOLDER” note) and listed in the summary.
• Write formulas with openpyxl, never computed values pasted in. A model of values is a report, not a model.

Workflow

- [ ] 1. Pin down purpose, horizon, granularity, and model type
- [ ] 2. Collect actuals and assumptions with sources; mark placeholders
- [ ] 3. Lay out sheets and the shared time axis
- [ ] 4. Build drivers: revenue, headcount, operating costs
- [ ] 5. Build P&L, then balance sheet and cash flow (three-statement only)
- [ ] 6. Add checks, scenarios, and sensitivity
- [ ] 7. Lint, recalculate, stress test, and write the model notes

1. Scope questions

Ask before building; defaults in brackets:

Question	Why it matters
Purpose: fundraise, budget, runway, pricing decision?	Sets detail level and outputs
Horizon and granularity [36 months monthly, annual roll-up]	Fixes the time axis
Model type: SaaS revenue model or full three-statement?	Three-statement adds BS and CF
Billing: monthly, annual upfront, usage?	Annual prepay creates deferred revenue and cash timing gaps
Currency, fiscal year start, actuals available?	Actuals go left of forecast with a flag row

If the user only needs runway, build revenue, costs, and cash; skip the balance sheet.

2. Sheet layout

Sheet	Contents
Cover	Purpose, author, version, date, colour legend, scenario in use, placeholder list
Assumptions	Every input: label, value, unit, source/note; scenario columns
Revenue	Customer and MRR build per segment or plan
Headcount	One row per role or hire: start month, salary, burden %, department
Opex	Non-headcount costs by department, driven by headcount or revenue %
PL	Revenue, COGS, gross profit, opex, EBITDA, D&A, interest, tax, net income
BS, CF	Three-statement models only
Checks	Every check in one place with a master flag on Cover
Sensitivity	Scenario outputs and grids

Flow runs top to bottom and left to right: inputs, then drivers, then statements, then outputs.

3. Time axis

• Row 1 to 4 of every time sheet carries the same header block: period start date, period number, fiscal year, actual/forecast flag. Period 1 sits in the same column (for example E) on every sheet.
• Build dates from one input: E1 = Assumptions!start_date, F1 = EDATE(E1,1).
• Annual roll-ups use SUMIFS(row, year_row, year) for flows and the last month’s value for balances. Never mix monthly and annual columns in one block.
• Convert rates explicitly. Monthly churn from annual is 1-(1-annual)^(1/12), not annual/12. Monthly growth from annual is (1+annual)^(1/12)-1.

4. Colour and format conventions

Cell type	Font	Fill
Hardcoded input	Blue 0000FF	Light yellow FFF2CC optional
Formula on the same sheet	Black	none
Link to another sheet	Green 008000	none
Check cell	Black	Green when OK, red when broken (conditional format)

This is the common banking convention; state it on Cover. Number formats: currency with no decimals for money, 0.0% for rates, negative numbers in parentheses.

SaaS revenue build

Use an MRR waterfall per segment, all monthly:

Opening MRR            = previous closing MRR
+ New MRR              = new customers x starting ARPA
+ Expansion MRR        = opening MRR x monthly expansion rate
- Contraction MRR      = opening MRR x monthly contraction rate
- Churned MRR          = opening MRR x monthly revenue churn rate
= Closing MRR
Customers: opening + new - churned (logo churn applied to opening customers)
New customers          = leads x conversion, or S&M spend / blended CAC (pick one, say which)

Apply churn to opening balances, not closing ones, or new customers churn in the month they arrive.

Metric	Formula	Caveat to state in the notes
ARR	closing MRR x 12	Run-rate, not revenue recognised or cash
Gross revenue retention	(opening MRR - contraction - churn) / opening MRR, over 12 months on a cohort	Cannot exceed 100%
Net revenue retention	(opening + expansion - contraction - churn) / opening, cohort basis	Exclude new customers
CAC	fully loaded sales and marketing cost / new customers in the period	Say whether salaries and tools are included; lag spend if sales cycle is long
CAC payback (months)	CAC / (new ARPA x gross margin %)	Gross-margin basis, not revenue basis
LTV	ARPA x gross margin % / monthly revenue churn	1/churn assumes constant churn forever; cap lifetime (for example 5 years) and show the cap
LTV:CAC	LTV / CAC	3:1 is an industry heuristic, not a law; meaningless with under ~12 months of churn data

For early-stage companies with little churn history, flag that LTV is not yet measurable and lead with payback instead.

Three-statement linkage

• Net income from PL flows into retained earnings: RE = prior RE + net income - dividends.
• Cash flow uses the indirect method: net income + D&A - increase in receivables + increase in payables + increase in deferred revenue - capex + equity and debt raised.
• Closing cash on CF is the cash line on BS. Never plug cash.
• Receivables from days sales outstanding: revenue / days_in_period x DSO. Annual upfront billing books cash at invoice and recognises revenue monthly; the difference sits in deferred revenue.
• Avoid circularity. Compute interest on opening cash or opening debt, not on the average that depends on this period’s cash. If the user insists on average balances, add a circuit-breaker input and say so.

Checks

Put each check in Checks and roll them up into one master flag:

Balance check   = ROUND(total assets - total liabilities - total equity, 2) = 0   every period
Cash check      = CF closing cash - BS cash = 0                                   every period
Retained earn.  = BS RE - (prior RE + PL net income) = 0
Revenue tie     = PL revenue - Revenue sheet total = 0
Headcount tie   = Opex payroll - Headcount payroll total = 0
Runway flag     = first month where closing cash < 0 (shown on Cover)
Master          = IF(SUM(ABS(all check rows)) < 0.01, "OK", "ERROR")

Scenarios and sensitivity

• Scenarios: put Base, Upside, Downside columns side by side on Assumptions and a selector cell (scenario = 1, 2, 3). The live column is =INDEX(D5:F5, 1, scenario) or =CHOOSE(scenario, D5, E5, F5). Every calculation references the live column only.
• Sensitivity on closed-form metrics (LTV:CAC by churn x ARPA, payback by CAC x margin): build the grid with explicit formulas, one per cell, referencing row and column headers.
• Sensitivity on whole-model outputs (runway, month-24 cash): openpyxl cannot author a working Excel What-If Data Table. Either tell the user to add one in Excel (Data > What-If Analysis > Data Table) pointing at the labelled cells, or generate one workbook per input combination, recalculate each, and write the resulting values into a static grid clearly labelled “values from runs on <date>”.
• Show scenario outputs as a small table: ARR month 12/24/36, cash low point, runway months, breakeven month.

Building with openpyxl

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.workbook.defined_name import DefinedName

BLUE, GREEN = Font(color="0000FF"), Font(color="008000")
INPUT_FILL = PatternFill("solid", fgColor="FFF2CC")
MONTHS, FIRST = 36, 5                                     # period 1 lives in column E

wb = Workbook()
a = wb.active
a.title = "Assumptions"
a.append(["Input", "Value", "Unit", "Source / note"])
inputs = [("start_date", None, "date", "Model start"), ("start_mrr", 0, "USD", "PLACEHOLDER"),
          ("new_customers", 0, "per month", "PLACEHOLDER"), ("arpa", 0, "USD/month", "PLACEHOLDER"),
          ("monthly_churn", 0, "%", "PLACEHOLDER")]
for i, (name, val, unit, note) in enumerate(inputs, start=2):
    a.append([name, val, unit, note])
    a[f"B{i}"].font, a[f"B{i}"].fill = BLUE, INPUT_FILL
    wb.defined_names[name] = DefinedName(name, attr_text=f"Assumptions!$B${i}")

r = wb.create_sheet("Revenue")
for m in range(MONTHS):
    col = get_column_letter(FIRST + m)
    prev = get_column_letter(FIRST + m - 1)
    r[f"{col}1"] = "=start_date" if m == 0 else f"=EDATE({prev}1,1)"
    r[f"{col}6"] = "=start_mrr" if m == 0 else f"={prev}10"      # opening MRR
    r[f"{col}7"] = "=new_customers*arpa"                           # new MRR
    r[f"{col}8"] = f"=-{col}6*monthly_churn"                       # churned MRR
    r[f"{col}10"] = f"=SUM({col}6:{col}8)"                         # closing MRR
    r[f"{col}1"].number_format = "mmm yy"
r.freeze_panes = "E2"
wb.save("model.xlsx")

Defined names make formulas readable (=new_customers*arpa) and survive row inserts. Put labels in columns A to D and leave the time axis starting at column E everywhere. For file mechanics (styles, validation, charts, recalculation), see the xlsx-spreadsheets skill.

Gotchas

• Revenue, bookings, billings, and cash are four different numbers. Annual prepay makes cash lead revenue; enterprise net-60 terms make it lag.
• Headcount costs need a burden rate (payroll tax, benefits, equipment) on top of salary, and start in the hire month, not month 1.
• Growth applied to a growing base compounds silently; 10% monthly is roughly 3.1x a year. Show the implied annual figure next to any monthly growth input.
• Churn of 0% (or very small) makes uncapped LTV explode. Cap it.
• IFERROR around whole formulas hides broken links. Use it only where a known divide-by-zero is expected (for example ratios in month 1).
• Avoid OFFSET and INDIRECT: volatile, slow, and invisible to precedent tracing.
• Signs: pick one convention (costs negative or costs positive with subtraction in totals) and keep it everywhere; mixed signs are the most common cause of a broken balance check.
• Ten-decimal outputs imply precision the inputs do not have. Round displays, not formulas.

Verification

1. Lint the structure. The bundled script reads the .xlsx directly (stdlib only):

   python3 scripts/model_lint.py model.xlsx --allow 0,1,12 --input-sheets "Assumptions,Cover"

   It reports numbers typed inside formulas (HARDCODE), links to other workbooks (EXTERNAL), typed constants on calculation sheets (CONSTANT), and rows whose formulas are not one copied pattern (PATTERN). Exit code 1 on any HARDCODE, EXTERNAL, or CONSTANT finding. Fix each, or move the number to Assumptions.

2. Recalculate (openpyxl writes formulas without values) and confirm no error cells and that every check reads zero; the xlsx-spreadsheets skill covers recalculation.

3. Hand-check one forecast month end to end: recompute closing MRR and cash with a calculator or Python from the inputs and compare.

4. Stress test: set growth to 0, churn to 10% a month, CAC doubled. The model must still balance, show no errors, and flag the runway month.

5. Flip the scenario selector through every value and confirm outputs change and checks stay at zero.

Output contract

Deliver the workbook plus these notes in chat or on Cover:

## Model notes: <company> <model type> v<n> (<date>)
- Purpose and horizon: <36 months monthly from 2027-01, annual roll-up>
- Sheets: Cover, Assumptions, Revenue, Headcount, Opex, PL, BS, CF, Checks, Sensitivity
- Key outputs (Base): ARR M12/M24/M36, cash low point and month, runway, breakeven month
- Placeholders still to replace: <list with cell names>
- Method choices: churn on opening MRR; interest on opening cash; LTV capped at 60 months
- Checks: all zero across all periods and scenarios (recalculated with <tool>) / not recalculated
- Known limits: <no cohort data, CAC excludes founder time, tax simplified>