Build a Budget vs Actual Variance Analysis That Separates Timing from Overspend

Get a variance table with dollar and percent variances, a materiality filter, timing vs permanent labels, short explanations with next actions, and Excel formulas.

You getVariance table, material variance explanations and summary

Prompt

Prompt
Act as an FP&A analyst who prepares monthly budget vs actual reports for department heads.

I will paste my budget and actual figures right after this prompt (line items, budget, actual, any notes). Before analyzing, ask me 3 short numbered questions in one message and wait for my answers: 1) the period covered and whether figures are monthly or year-to-date, 2) my materiality threshold (default: at least 10% and $1,000), 3) any known events such as delayed invoices, prepayments or hiring changes.

Rules:
- Dollar variance = Actual minus Budget; percent variance = dollar variance divided by Budget, rounded to one decimal.
- Label each line Favorable or Unfavorable, treating revenue and expense lines in opposite directions.
- Explain only lines that breach the threshold; group the rest as "Within tolerance".
- Classify each material variance as Timing (should reverse in a later period), Permanent (real overspend or shortfall) or Unclear, and state the evidence for the label.
- Never invent causes; if my notes don't explain a variance, mark it Unclear and say what to check.

Output format:
1. Variance table: Line item | Budget | Actual | $ Variance | % Variance | F/U | Material? | Type
2. Material variance explanations (one short paragraph each, ending with a next action)
3. Summary: net variance, timing vs permanent totals, top 3 risks
4. Excel formulas to rebuild the table

End by listing your assumptions and any missing data that would change a classification.

Example output

Example for: a 10-person marketing department, May monthly figures, threshold of 10% and $1,000.

1. Variance Table

Line itemBudgetActual$ Variance% VarianceF/UMaterial?Type
Salaries84,00086,4002,4002.9%UNoWithin tolerance
Contractors12,00019,5007,50062.5%UYesPermanent
Paid ads25,00018,200-6,800-27.2%FYesTiming
Software6,5006,9004006.2%UNoWithin tolerance
Events8,00014,6006,60082.5%UYesTiming
Travel4,0004,75075018.8%UNoWithin tolerance
Training2,5000-2,500-100.0%FYesUnclear
Total142,000150,3508,3505.9%U

2. Material Variance Explanations

Contractors (+$7,500, U, Permanent): Your notes say two freelance designers were hired for the rebrand, which was not in the budget. This spend will not reverse. Next action: confirm whether the rebrand continues in June and request a budget reallocation.

Paid ads (-$6,800, F, Timing): The summer campaign launch moved from May 20 to June 3. Spend should catch up in June. Next action: expect June ads to run about $6,800 over budget; flag it now so it isn't read as overspend.

Events (+$6,600, U, Timing): A trade show deposit for September was paid in May. Next action: ask finance whether to book it as a prepaid expense so it lands in September.

Training (-$2,500, F, Unclear): No notes explain the zero spend. Next action: check whether the course was cancelled (permanent saving) or postponed (timing).

3. Summary

  • Net variance: $8,350 unfavorable (5.9%)
  • Timing, net: -$200 (ads -6,800, events +6,600)
  • Permanent: +$7,500 (contractors)
  • Unclear: -$2,500 (training)
  • Within tolerance: +$3,550

Top 3 risks:

  1. Contractor spend continues and repeats the overrun monthly.
  2. June looks overspent when the delayed ad spend lands.
  3. Travel is 18.8% over; small in dollars but worth watching if the trend continues.

4. Excel Formulas (row 2, Budget in B, Actual in C)

  • $ Variance (D2): =C2-B2
  • % Variance (E2): =IF(B2=0,"n/a",D2/B2)
  • F/U for expenses (F2): =IF(D2>0,"U","F")
  • Material? (G2): =IF(AND(ABS(D2)>=1000,ABS(E2)>=0.1),"Yes","No")

Assumptions and missing data: All lines treated as expenses. Event classification assumes the deposit can be reclassed as prepaid; if finance books it in May, it becomes Permanent for this period. Training status is unknown.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • A two-part materiality threshold keeps the report focused on variances big in both dollars and percent, not noise.
  • Forcing Timing, Permanent or Unclear labels with evidence stops delayed spend from being reported as overspend.
  • Banning invented causes and supplying Excel formulas makes the analysis checkable and easy to rebuild.

Use at month-end or quarter-end before a budget review. Have your line items, budget and actual figures, plus any notes on unusual spend, ready to paste into ChatGPT, Claude or Gemini.

Common edits

  • Change the default threshold, e.g. 5% and $5,000 for a larger budget.
  • Add a Year-to-date column pair so monthly and YTD variances appear side by side.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets