Draft a P&L Variance Memo from Monthly Numbers

Tier: Core · Hands-on time: ~35 minutes · Tools: ChatGPT, Excel, Claude

Variance commentary is the most formulaic document in finance — and the most dangerous to get wrong, because it goes to people who can read a P&L. The formula is exactly what models are good at; the danger is exactly why the recipe keeps the accounting judgment and every reconciliation with you.

When to use — and when not to

• Monthly close commentaryyou owe a variance memo against budget or forecast, line by line, with a materiality threshold.
• Quarter-end and reforecast packsthe same drill at higher stakes, where the memo becomes the narrative section of a board pack.
• Cost-centre reviewsa department head asking "why is my spend over?" and needing the answer decomposed by account.

And when not to:

• The trial balance hasn't closed. Commentary on moving numbers is rework by definition. Wait for close, then draft.
• A variance smells like an error, not an event. If a line looks misposted or a accrual looks doubled, that is an investigation for the accounting team — a memo that "explains" a booking error institutionalises it.
• The memo will state causes you cannot verify. The model can propose that travel is over because of the sales offsite; only you can confirm it. Never ship an unverified cause in a finance document.

The recipe

1. 1.

   Build a clean extract in Excel (7 minutes). One table: account or line name, actual, budget, variance (absolute), variance (%). Add a column with your materiality threshold flag (e.g. =ABS(D2)>10000 or >5%). Add a short "known events" list — the offsite, the delayed hire, the one-off credit — because the model cannot know your month. Copy the table as text.

2. 2.

   Generate the memo draft with netting and materiality rules. Paste with this prompt:

   Copy

   You are a management accountant drafting monthly P&L variance
   commentary. Below: an actuals-vs-budget table for [MONTH], the
   materiality threshold, and known events. Draft a memo with EXACTLY
   this structure:
   
   1. Summary (3 sentences max): overall result vs budget, the one or
      two variances that drive it, and whether they are one-off or
      run-rate.
   2. Material favourable variances: one bullet each — line, amount,
      %, cause (only from known events or an obvious data pattern;
      otherwise write "cause TBC with [owner]").
   3. Material unfavourable variances: same format.
   4. Offsets and netting: where variances offset (e.g. timing between
      lines), state the pair and the NET effect once — do not report
      both sides as separate stories.
   5. One-offs vs run-rate: classify each material variance; state the
      full-year impact of any run-rate item (variance × remaining
      months) labelled as an estimate.
   
   Rules:
   - Only variances above threshold get commentary. List sub-threshold
     ones in a single line: "All other variances individually below
     [threshold]."
   - Use the sign convention: favourable = better than budget. Never
     flip it.
   - Recompute every % from the absolutes. No invented causes.
   
   THRESHOLD: [e.g. ±$10k or ±5%]
   KNOWN EVENTS: [LIST]
   TABLE: [PASTE]
   

   A good draft looks like this:

   Copy

   Summary: March opex finished $42k (4.1%) favourable to budget,
   driven by the delayed marketing-manager hire ($28k, run-rate until
   backfilled) and lower contractor spend ($19k, one-off). Travel was
   $12k unfavourable due to the sales offsite (one-off, was budgeted
   in April — timing).
   
   Material unfavourable variances
   - Travel & entertainment: $12k / 34% over. Sales offsite held in
     March vs April budget phasing. Offsets against April; net FY
     impact nil (see Offsets).
   ...
   One-offs vs run-rate
   - Salaries favourable $28k — RUN-RATE until hire closes; est. FY
     impact $84k favourable if start slips to July (estimate).
   

3. 3.

   Reconcile every figure to the workbook (10 minutes). Tick each amount and percentage against Excel — a fresh column recomputing the model's percentages catches transpositions in seconds. Check the sign convention on every line; favourable/unfavourable flips are the classic model error and the most embarrassing one. Confirm the sub-threshold lines really are sub-threshold.

4. 4.

   Convert every "cause TBC" into a fact or a question. The draft should contain explicit cause TBC with [owner] markers wherever the events list didn't explain a variance. Chase those with the budget holder before the memo ships — or leave them in deliberately as the memo's open items. What must never happen: the model's plausible guess shipping as a confirmed cause.

5. 5.

   Tighten and format for the pack. Same conversation: "Compress to fit one page. Keep all figures. Summary first, then a table: line, variance $, variance %, cause, one-off/run-rate." Paste into your memo template and read it once as the CFO would — every sentence should answer "so what should we do or watch?"

Make it yours

• ChatGPT with the workbook attached: upload the extract and have data analysis recompute variances and flag materiality itself before drafting — your reconciliation then checks its table against the workbook once, instead of line by line.
• Claude for multi-entity or long packs: paste several cost centres at once and ask for per-centre commentary plus a consolidated summary that nets cross-centre timing differences — the netting discipline in the prompt matters even more here.
• Excel Copilot in place: where your tenant has it, ask Copilot to summarise variances directly in the workbook for a fast first pass, then run the step-2 prompt on the result for memo structure. Keep the reconciliation step regardless of which tool drafted.

Quality check

• Every amount and % ties to the workbookrecomputed, not assumed.
• Sign convention is consistentfavourable means better than budget on every line, including costs.
• Offsetting variances are netted once, not narrated twice as separate good and bad news.
• Each material variance is classified one-off or run-rate, with run-rate items carrying a labelled full-year estimate.
• No unverified causesevery explanation is either a known event, a checked fact, or an explicit open item with an owner.