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 Excel only after you trust the logic.
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 that 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.
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% → 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
- 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 last 6 months 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 AI’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 just one of three scenarios; without sensitivity you cannot answer “how bad could it get”
- Porting straight to the model without re-deriving the math. AI gets monthly compounding wrong about 1 in 5 times; recompute the volume curve in Excel
FAQ
- Should I trust the math? No. Trust the structure and the story. Recompute revenue and EBITDA in your spreadsheet. AI is for drafting the logic, not the ledger.
- 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 are not stable yet. Wait or draft anyway? Draft anyway with explicit ranges. A forecast with a $850k-$1.2M revenue range in month 6 is more honest than a $1.05M point estimate built on guesswork.
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