Write a Variance Analysis Prompt That Won't Invent the Drivers

The monthly close ends and the real work starts: explaining why every line came in over or under plan. Most FP&A teams write that commentary by hand, variance by variance, and it eats an afternoon. So the obvious move is to paste the budget-vs-actual table into ChatGPT and ask for the narrative. It works, mostly. The problem is what happens on the lines where you don't actually know the driver yet. The model fills them in anyway, confidently, with a reason it made up. A good variance analysis prompt stops exactly that.

The fix is one rule: if a driver isn't stated in the inputs, the model writes Not stated and moves on. No inference, no plausible-sounding cause, no "likely due to higher marketing spend" when nobody checked. That single guard is the difference between commentary you can put in a board pack and commentary you have to re-verify line by line.

Why the ranking guides skip the one rule that matters

Search "variance analysis in Excel with ChatGPT" and most results teach formulas. The Bricks guide (thebricks.com) walks through dollar and percentage calculations, conditional formatting, and charts, then offers a throwaway "write a two-sentence summary" at the end. It's a spreadsheet tutorial. It never gives you a narrative framework, and it says nothing about missing data.

The prompt-focused pieces get closer but still miss it. The AI+ Medium post (medium.com) ships an actual prompt ("for each variance over 15%, explain the possible cause, suggest a follow-up question, rank impact"). It's a decent prompt. But "explain the possible cause" is an invitation to hallucinate, and the post explicitly has no missing-driver guard and no audit-trail discipline.

That's the gap. Plenty of guides will help you compute a variance or summarize one loosely. None give you a prompt that writes the FP&A narrative and refuses to invent the part you didn't supply.

What you can do with this prompt

• Turn a budget-vs-actual table into one tight narrative per material variance
• Cap the commentary to variances over a threshold you set, not every rounding difference
• Report only the drivers you supply, with Not stated on the rest
• Produce board-ready language without re-typing the same phrasing every month
• Generate a closing list of every unexplained variance as a follow-up queue
• Keep the output auditable so it can feed a board pack or forecast revision

Anatomy of the variance analysis prompt

Three parts: the table and drivers you paste, the threshold and output contract, and the guard.

Variables
  {{variance_table}}   → metric, budget, actual, variance $, variance %
  {{known_drivers}}    → "Marketing: timing of Q2 campaign; Headcount: 2 late hires"
  {{threshold_pct}}    → only narrate variances at or above this, e.g. 10
  {{reporting_period}} → e.g. "May 2026"

Prompt
  Role: FP&A analyst writing monthly variance commentary
  Task: for each variance >= threshold, write 2-3 sentences:
        the figure, the direction, and the STATED driver
  Rule: if the driver for a line is not in {{known_drivers}},
        write "Not stated" as the driver — do NOT infer a cause
  Rule: do NOT recalculate any variance; use the numbers as given

Output contract
  - One block per qualifying variance, ordered by variance % descending
  - Each block: Line | Variance | Driver | One-line impact
  - Closing section: "Unexplained variances" listing every "Not stated"

The two rules are load-bearing. Drop the first and the model guesses drivers. Drop the second and it "helpfully" recomputes a variance and gets it wrong on a sign or a base.

The opinionated part: never let the model do the math

FP&A people reach for ChatGPT to save time, then trust it with arithmetic it shouldn't touch. Temperature won't save you here. A model at temperature 0 will still confidently subtract the wrong way on a favorable-vs-unfavorable variance because "actual minus budget" flips meaning between revenue and cost. Compute the variances in the sheet where the signs are already right, paste them, and tell the model explicitly not to recalculate. Let it write. Don't let it count.

Step-by-step usage

1. Gather inputs

Export the variance table you already build at close: line item, budget, actual, variance in dollars and percent. The numbers should be final and signed correctly before they leave the sheet.

2. Fill variables

Paste the table into {{variance_table}}. Write the drivers you actually know into {{known_drivers}}, in plain language. Set {{threshold_pct}} so the commentary covers what's material and ignores noise. Leave out drivers you haven't diagnosed; the guard will surface them.

3. Run the prompt

Paste into ChatGPT or Claude. Claude honors the "do not recalculate" instruction more literally; GPT-4o sometimes needs that rule restated on the last line, because it defaults to being helpful with numbers. Both respect a ## Output format heading well.

4. Post-process

Jump to the "Unexplained variances" section first. That list is your real to-do: each line is a variance you owe the business an explanation for. Better to see it now than to have a board member ask.

5. Iterate

Add the drivers as you learn them and rerun. Same table plus richer {{known_drivers}} gives the same structure with fewer Not stated lines. The repeatability is the point; you're building a monthly factory, not a one-off.

Prompt-craft patterns that hold

Driver lookup, not driver invention. Frame the task as a lookup against {{known_drivers}}, not as analysis. "Match each variance to its stated driver; if none exists, write Not stated" produces honest output. "Explain the cause of each variance" produces fiction for the lines you can't explain.

For each variance at or above {{threshold_pct}}%:
- find its driver in {{known_drivers}}
- if no driver is listed, the driver is "Not stated"
- write 2-3 sentences using ONLY that driver
Do not propose causes that are not in {{known_drivers}}.

Threshold as a refusal boundary. Telling the model to skip variances under the threshold keeps the commentary about what matters. A 0.4% variance with a Not stated driver is noise, and a narrative that dutifully writes a paragraph about it just buries the real signal.

Order by magnitude, not by row. "Order blocks by variance percent descending" puts the biggest miss at the top where a reviewer reads first. Spreadsheet row order rarely matches importance.

Variables you'll set

Getting started

1. Finalize the variance table in your sheet, signs correct.
2. Write down only the drivers you've actually confirmed.
3. Set a materiality threshold so you're not narrating noise.
4. Paste everything in with both rules: no inference, no recalculation.
5. Run it in Claude or ChatGPT, then read the Unexplained variances list first.
6. Backfill drivers, rerun, and the Not stated count drops each cycle.
7. For the packaged version with the guard already wired in, use the Variance Analysis Narrative Prompt Pack.

This commentary often feeds straight into a board update, so the two jobs share a spine. If you assemble the quarterly board deck, the Board Pack Production Prompt Pack reuses the same highlights-and-misses discipline at the deck level.

Get the variance narrative honest and the rest of the reporting stack gets easier. The same data drives next quarter's targets, so see the OKR drafting prompt for turning results into objectives, and the board deck narrative prompt for rolling the commentary up into a board-ready story.