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
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
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 like this
Build a 13-Week Cash Flow Forecast in Excel with Low-Balance Alerts
You get a 13-week cash flow sheet layout with committed and expected cash split apart, exact running-balance formulas, and red flags for weeks below your minimum.
Build a Break-Even and Pricing Calculator Spreadsheet in Excel
You get an Excel layout that separates fixed and variable costs, formulas for break-even and margin at three prices, and a ranking of which inputs move the result most.
Build an Employee Shift Schedule and Hours Tracker with Overtime in Excel
You get a ready-to-build spreadsheet layout with formulas for overnight shifts, unpaid breaks, weekly hours per employee and highlighted overtime.
Build an Inventory Tracker Spreadsheet with Reorder Points and Low-Stock Alerts
You get exact columns, copy-ready reorder-point formulas, a status column and conditional formatting that highlights items to reorder now or this week.
Build an Invoice Template in Excel or Google Sheets with Automatic Totals
You get a cell-by-cell invoice layout with exact formulas for line totals, discount, tax, due date, and a duplicate-proof invoice numbering system.
Build a Power Query Workflow That Combines and Cleans Monthly Excel Files
You get numbered Power Query steps with paste-ready M code, a full query, and a flag on every step that breaks when column names change.