The CFO needs a fresh 12-month forecast by Friday. You have a driver list (list price, expected volume, churn assumption, hire plan, supplier renewal) and a half-broken model from last cycle. The temptation is to rebuild the spreadsheet from scratch. Skip that. Use AI to draft the narrative and the line-item math first, then port to a workbook only after you trust the logic.
TL;DR
- Use AI to draft the forecast logic and quarterly story, not the final ledger. Recompute revenue and EBITDA yourself before anyone sees them.
- Best tool as of June 2026 depends on where your data lives: Claude in Excel (Sonnet 4.6, top finance-benchmark score) or ChatGPT for Excel (GPT-5.5, went GA May 5, 2026) both read your actual cells. For a quick chat-window draft, paste actuals into Claude or ChatGPT directly.
- Give the model named drivers, not goals. “Volume +4% MoM through month 9” works; “make us hit $15M” produces fiction.
- Demand a pressure-test list and one named sensitivity. A base case without a downside is half a forecast.
The task
Convert a list of business drivers into a 12-month P&L outlook: monthly revenue, gross margin, opex by category, EBITDA, and one paragraph per quarter on what’s changing and why. The forecast is the working draft; you will challenge it, not ship it.
When this is the right job for AI
- You can name the drivers explicitly (price per unit, units per month, churn %, headcount adds by month, supplier cost step-ups).
- You want a first pass you will rip apart, not a final number.
- You need the rationale paragraph more than the cell values. The cells are easy; the story is hard.
- The forecast is for internal planning, not external reporting. Audit-grade work belongs in your model, not a chat window.
Which tool and model (June 2026)
Two questions decide the tool: does the AI need to touch your live workbook, and how good is the model at finance reasoning?
| Tool | Model | Reads your live cells? | Plan to access | Best for |
|---|---|---|---|---|
| Claude in Excel | Sonnet 4.6 | Yes (Microsoft 365 add-in, GA May 7 2026) | Claude Pro $20 / Max / Team / Enterprise | Building and auditing the model in-place |
| ChatGPT for Excel | GPT-5.5 | Yes (Excel add-in, GA May 5 2026, US/CA/AU) | ChatGPT Plus $20 / Pro / Business | Excel users who live in the OpenAI ecosystem |
| Claude (chat) | Sonnet 4.6 / Opus 4.7 | No — paste tables | Free / Pro $20 / Max | Fast first-draft narrative + math |
| ChatGPT (chat) | GPT-5.5 | No — paste tables, or upload a file | Free / Plus $20 / Pro | Same, with file upload + Python data analysis |
| Gemini in Sheets | Gemini 3.1 Pro | Yes (Google Sheets) | Google AI Pro $19.99 | Teams already on Google Workspace |
A few facts worth knowing before you pick. Anthropic’s Claude Sonnet 4.6 scores 63.3% on its own Finance Agent v1.1 benchmark — ahead of Opus 4.6 at 60.1% — and costs one-fifth of Opus per token, so it is the sensible workhorse for FP&A drafting. All three flagship models (Sonnet 4.6, Opus 4.7, Gemini 3.1 Pro) carry a 1M-token context window, enough to hold a full year of monthly actuals plus your driver memo in one request. If you want the model to write formulas straight into cells and explain why an output changed, use the Excel or Sheets add-in; for a throwaway first draft, the chat window is faster.
What to feed the AI
- Last 6-12 months of actuals by line item (monthly, not quarterly)
- Each driver, with the assumption in plain language (“price holds at $89; volume grows 4% MoM through Q3 then flattens”)
- Headcount plan: who, when, fully-loaded cost
- Known step-ups: supplier renewal in month 7, office lease bump in month 4
- The single sensitivity you care about most (“what if churn doubles in Q3”)
- The two numbers you cannot get wrong (usually revenue and EBITDA)
Copy-ready prompt
You are drafting a 12-month P&L forecast from drivers.
Last 6 months actuals (monthly):
[paste table — revenue, COGS, opex by category]
Drivers for the next 12 months:
- Pricing: [e.g. list $89, no change Q1-Q2, +5% in Q3]
- Volume: [e.g. 1,200 units month 1, +4% MoM through month 9, flat after]
- Churn: [e.g. 3.8% monthly logo, 2.4% revenue]
- Hire plan: [list each role, start month, fully-loaded cost]
- Step-ups: [supplier B +12% in month 7, lease +$8k/mo from month 4]
- One sensitivity: [churn doubles in Q3]
Return:
1. Month-by-month table: revenue, COGS, gross margin %, opex by category, EBITDA
2. Quarterly narrative — 3-4 sentences per quarter on what is driving the move
3. Three numbers I should pressure-test before sharing — flag them [PRESSURE-TEST]
4. The sensitivity case as a second table, with the delta to base highlighted
5. The single line in the forecast most likely to be wrong, and why
Do not invent driver assumptions. If a number is not in my inputs, say "needs assumption" instead of guessing.Sample output structure
Q1 (months 1-3): Revenue lands at $1.08M / $1.13M / $1.18M as the 4% MoM volume tailwind compounds against flat pricing. Gross margin holds at 71%; no supplier change yet. EBITDA turns positive in month 3 once the SDR hires (m1, m2) ramp to quota.
Q2 (months 4-6): Lease step-up ($8k/mo) pulls opex up but volume keeps revenue growing. EBITDA $142k / $156k / $171k. [PRESSURE-TEST] the volume curve: month 5 assumes no seasonal dip, which is optimistic for our category.
Q3 (months 7-9): Supplier B renewal hits gross margin in month 7 (71% to 67%). The Q3 pricing bump (+5%) recovers ~3 points by month 9. Hire plan adds 2 AEs (m7) and 1 eng (m8), pushing opex up $58k/mo by month 9.
Q4 (months 10-12): Volume flattens per assumption. Margin stable at 70%. EBITDA $198k / $211k / $224k. The risk: if churn re-accelerates (per sensitivity), Q4 EBITDA drops to $134k / $128k / $119k.
How to refine the draft
- AI invents a driver. Call it out: “You assumed marketing scales 8% MoM. I never gave you that. Recompute with marketing flat.”
- The monthly table is too smooth. Real businesses have step changes. Ask: “Add the realistic month-to-month noise from the last 6 months of actuals; don’t draw a straight line.”
- Narrative reads like a finance textbook. Ask for the specific risk line, not the genre: “Name the one assumption that breaks the model, by month.”
- EBITDA looks too good. Usually means opex was undercounted. Re-feed the hire plan with fully-loaded cost (benefits, equipment, recruiting fee amortized).
Common mistakes
- Letting the model pick growth rates. Drivers must come from you, not the model’s training data on “typical SaaS.”
- One-table output with no quarterly narrative. The narrative is the deliverable; the table is supporting evidence.
- Skipping the pressure-test list. Every forecast has 2-3 lines that won’t survive scrutiny; surface them yourself before the CFO does.
- Treating the sensitivity as optional. The base case is one of three scenarios; without a downside you cannot answer “how bad could it get.”
- Porting straight to the model without re-deriving the math. Chat models still mis-handle monthly compounding often enough that you must recompute the volume curve in your workbook — even the Excel add-ins write formulas you should spot-check.
FAQ
- Which model should I actually use? For day-to-day FP&A drafting, Claude Sonnet 4.6 — it leads Anthropic’s own finance benchmark (63.3% on Finance Agent v1.1) and is cheap. Reach for Opus 4.7 or GPT-5.5 only for a complex multi-step model build. Gemini 3.1 Pro is the natural pick if your data already lives in Google Sheets.
- Should I trust the math? No. Trust the structure and the story. Recompute revenue and EBITDA in your spreadsheet. AI drafts the logic, not the ledger.
- Do the Excel add-ins change this? They help — Claude in Excel and ChatGPT for Excel both read your actual cells and trace how an assumption flows through the model, which removes a lot of copy-paste error. They still don’t absolve you of re-checking the two numbers that matter.
- What about working capital? Out of scope for the first draft. Add a separate prompt: “Given this P&L and 45-day DSO, draft monthly cash from operations.” Forecasting cash and P&L together in one shot makes both worse.
- My drivers aren’t stable yet. Wait or draft anyway? Draft anyway with explicit ranges. A forecast with an $850k-$1.2M revenue range in month 6 is more honest than a $1.05M point estimate built on guesswork.
For vendor specifics, see OpenAI’s ChatGPT for Excel announcement and Anthropic’s Claude for Excel help guide.
Related
- AI variance analysis — when actuals diverge from this forecast
- AI sensitivity analysis — deeper sensitivity workflow
- AI budget narrative — when the forecast becomes the budget story
- AI KPI weekly report — track the forecast vs actuals weekly
- AI exec summary — compress the forecast for the board
- AI business driver breakdown — sanity-check drivers before forecasting
- AI Excel cleanup — tidy the actuals before you feed them in