Claude Built a 24-Month Financial Model for a $40M CPA Firm. Three Things It Found.
A driver-based forecast, three scenarios, and the patterns the model surfaced that weren't in the management report. Try the live demo.
This is the first episode of AI for Finance — a series where I build real AI applications against specific finance use cases, using a consistent mock firm so the story compounds across episodes.
Episode 1 is the financial model builder. The brief: take 36 months of operational data from a fictional $40M CPA firm — Marwick & Hale — and produce a 24-month driver-based forecast with three scenarios, an Excel deliverable, and an interactive dashboard. Using Claude.
The output is a working demo you can explore right now.
Try the live demo →What the model does
The inputs are five CSVs: a P&L by service line, headcount, client revenue, AR aging, and a chart of accounts. Nothing exotic. The kind of data any firm running decent accounting software could export in an afternoon.
From that, the model builds a quarterly driver-based forecast. Revenue by service line is projected from historical growth rates. Advisory margin is driven by utilisation — the model tracks how many of the 37 billable FTEs are actually billing. The forecast runs three scenarios: base (utilisation recovers modestly to 68% by end-2027), upside (strong pipeline, utilisation hits 76%), and downside (utilisation stays flat, Advisory keeps bleeding).
The gap between upside and downside by end-2027: $2.6M of EBITDA. That is what hangs on one operational metric.
What it found
The more interesting part of the build is what the insights engine surfaced. These are patterns baked into the underlying data — not obvious from a summary P&L — that the model had to look across three service lines and 36 months to detect.
1. Advisory margin compressing — but not because of pricing
Advisory gross margin dropped from 51.8% to 44.2% over 36 months. That is a 7.6-point compression while revenue grew. The first-pass explanation would be pricing pressure or scope creep. The model checks the realisation rate: 91.1%, steady throughout. Not pricing.
The actual driver is utilisation. The firm hired aggressively — 22 to 37.5 Advisory FTEs in 36 months, a 70% headcount increase — but the client pipeline did not keep pace. Utilisation fell from 77.8% to 59.3%. Every idle Advisory FTE costs roughly $167K/year in unrecovered labour.
The base case assumes utilisation recovers to 68% by end-2027. If it does not — if Advisory keeps operating at 59% — the firm loses approximately $500K of annual EBITDA compared to the base case forecast.
2. One audit client is now 11% of audit revenue
Ridgeline Manufacturing grew from 4% of audit revenue in year one to 11% in year three. That makes it 5% of total firm revenue. The top three audit clients together are 41% of audit revenue.
This does not show up as a risk in the standard P&L because revenue is growing. It shows up when you track client concentration over time. If Ridgeline churns or reduces scope, audit revenue drops approximately $1.98M. That is not modelled in the downside scenario — it sits entirely outside the forecast as a tail risk.
3. Advisory DSO is 68 days — hidden by Tax collections
Advisory invoices sit unpaid for 68 days on average. Audit is 43 days. Tax is 37 days. 53% of Advisory AR is 60+ days overdue.
The firm-level DSO looks fine because Tax clients pay fast. The Advisory problem is invisible in aggregate. Fixing Advisory collections could release approximately $256K in cash. As Advisory grows in the upside scenario, the drag scales proportionally — it becomes a more significant problem at higher revenue, not a smaller one.
The format
The output ships in two formats. The interactive dashboard is the one linked above — three tabs, six charts, scenario toggles that update everything live. You can turn scenarios on and off and watch the revenue forecast and EBITDA charts respond in real time.
There is also an Excel file: eight tabs, including an Assumptions tab with yellow editable cells that drive the three Forecast tabs via formula references. The kind of model you hand to a CFO who wants to run their own sensitivities.
What this is actually demonstrating
The point of this series is not that AI can build a spreadsheet. Analysts have been doing that for years.
The point is the surface area. A good analyst working a week can build a solid three-scenario model. Claude can do the same model, then run an insights pass across 36 months of data looking for patterns the model did not set out to find — concentration risk, DSO divergence by service line, utilisation-versus-margin correlation — and surface them as structured findings before anyone thought to ask the question.
That is a different kind of leverage. Not faster spreadsheets. A second set of eyes that does not get tired and does not anchor to the assumptions the team already holds.
The AI for Finance series runs this pattern across multiple use cases. Episode 2 builds an interactive board dashboard on the same dataset. Episode 3 drafts the board pack. Each episode compounds on the prior one — the data layer is built once, and the AI applications that run on top of it become more valuable as the stack grows.
Temi Abayomi is the CEO of Lead Phoenix AI and a fractional Chief AI Officer for mid-market professional services and project-based businesses.