Paste your budget and actuals into ChatGPT and get back a formula-driven variance report — dollar and percentage variances, favourable/unfavourable labels, threshold flags, and a blank commentary column ready for your team.
ROLE You are a senior financial analyst preparing a monthly budget variance report in Excel. OBJECTIVE Take my budget and actual figures and produce a variance analysis report with dollar and percentage variances, favourable/unfavourable labels, threshold-based flags, and a blank commentary column. DATA Reporting period: [e.g. January–June 2025 / Q2 2025 / Full Year 2025] Budget vs Actual (columns: Line Item, Type [Revenue or Expense], Budget, Actual): [Paste your data here] VARIANCE RULES - Revenue lines: Variance $ = Actual − Budget. Positive = Favourable. - Expense lines: Variance $ = Budget − Actual. Positive = Favourable. - Variance % = Variance $ ÷ Budget, formatted as percentage. THRESHOLD RULES Flag any line where: |Variance %| > [5]% OR |Variance $| > [$5,000] Use "⚠ Review" for flagged items, "—" for others. CONSTRAINTS - Add subtotal rows for: Gross Profit (Revenue − COGS), Operating Income, Net Income. Calculate variances on subtotals too. - Apply conditional formatting: green fill for favourable, red fill for unfavourable. - Include a blank Commentary column after the Flag column — do NOT fill it in. - Do NOT invent budget or actual figures. - Do NOT fabricate explanations for variances. - Do NOT change my line-item names or reorder them. OUTPUT FORMAT - Tab-separated table ready to paste into Excel. - Formulas written explicitly (e.g. =C2-B2) so I can verify the logic.
Replace all bracketed sections with your real data, then paste the prompt into ChatGPT.
My variance report is flagging too many lines to be useful — I want to tighten the threshold. Here is my current variance data (columns: Line Item, Type, Budget, Actual, Variance $, Variance %): [Paste your variance data here] Current threshold: flag if |Variance %| > [5]% OR |Variance $| > [$5,000] New threshold: flag only if |Variance %| > [10]% AND |Variance $| > [$10,000] Recalculate the Flag column using the new AND-based threshold and tell me how many fewer lines are now flagged.
Switch OR to AND when a report is flagging so many lines that nothing stands out — the goal is 3-6 flagged items per report.
I need to compare year-to-date actuals against the YTD portion of my annual budget — not the full-year figure. Reporting period: YTD through [month, e.g. June 2025] of [6] months out of a 12-month annual budget. Annual budget and YTD actuals (columns: Line Item, Type, Annual Budget, YTD Actual): [Paste your data here] Please prorate each Annual Budget figure to a YTD Budget by multiplying by [6]/12 (or the correct fraction for non-linear items I flag), then calculate variance using YTD Actual against YTD Budget — not the annual figure. Flag any line where I should use a non-linear prorating method (e.g. seasonal revenue, annual insurance premiums).
Comparing six months of actuals against a full-year budget makes every expense line look artificially favourable.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line Item | Type | Budget | Actual | Var £ | Var % | Flag |
| 2 | Revenue — UK | Revenue | £420,000 | £389,500 | ? | ? | ? |
| 3 | Revenue — EU | Revenue | £180,000 | £201,400 | ? | ? | ? |
| 4 | COGS | Expense | £210,000 | £228,300 | ? | ? | ? |
| 5 | Payroll | Expense | £145,000 | £152,400 | ? | ? | ? |
| 6 | Marketing | Expense | £48,000 | £39,200 | ? | ? | ? |
| 7 | T&E | Expense | £12,000 | £18,750 | ? | ? | ? |
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Line Item | Budget | Actual | Var £ | Var % | F/U | Flag | Commentary |
| 2 | Revenue — UK | £420,000 | £389,500 | −£30,500 | −7.3% | U | ⚠ Review | |
| 3 | Revenue — EU | £180,000 | £201,400 | +£21,400 | +11.9% | F | ⚠ Review | |
| 4 | COGS | £210,000 | £228,300 | −£18,300 | −8.7% | U | ⚠ Review | |
| 5 | Gross Profit | £390,000 | £362,600 | −£27,400 | −7.0% | U | ⚠ Review | |
| 6 | Payroll | £145,000 | £152,400 | −£7,400 | −5.1% | U | ⚠ Review | |
| 7 | Marketing | £48,000 | £39,200 | +£8,800 | +18.3% | F | ⚠ Review | |
| 8 | T&E | £12,000 | £18,750 | −£6,750 | −56.3% | U | ⚠ Review |
Var £: =IF(Type="Revenue",Actual-Budget,Budget-Actual) Var %: =Var£/Budget Flag: =IF(OR(ABS(Var%)>5%,ABS(Var£)>5000),"⚠ Review","—") Marketing is correctly flagged because 18.3% > 5%. ✓
| A | E | F | G | |
|---|---|---|---|---|
| 2 | Revenue — UK | −£30,500 | −7.3% | ⚠ Review |
| 3 | Revenue — EU | +£21,400 | +11.9% | ⚠ Review |
| 4 | COGS | −£18,300 | −8.7% | ⚠ Review |
| 5 | Payroll | −£7,400 | −5.1% | ⚠ Review |
| 6 | Marketing | +£8,800 | +18.3% | ⚠ Review |
| 7 | T&E | −£6,750 | −56.3% | ⚠ Review |
| 8 | Software | −£320 | −4.2% | — |
| A | E | F | G | |
|---|---|---|---|---|
| 2 | Revenue — UK | −£30,500 | −7.3% | — |
| 3 | Revenue — EU | +£21,400 | +11.9% | ⚠ Review |
| 4 | COGS | −£18,300 | −8.7% | — |
| 5 | Payroll | −£7,400 | −5.1% | — |
| 6 | Marketing | +£8,800 | +18.3% | — |
| 7 | T&E | −£6,750 | −56.3% | — |
| 8 | Software | −£320 | −4.2% | — |
Old rule: >5% OR >£5,000 Flagged: 6 of 7 New rule: >10% AND >£10,000 Flagged: 1 of 7 Only Revenue — EU breaches BOTH: 11.9% and £21,400. ✓
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Line Item | Annual Budget | YTD Actual | Var £ | Var % | Interpretation |
| 2 | Revenue | £600,000 | £289,500 | −£310,500 | −51.8% | Misleading |
| 3 | Payroll | £290,000 | £152,400 | +£137,600 | +47.4% | Misleading |
| 4 | Marketing | £96,000 | £39,200 | +£56,800 | +59.2% | Misleading |
| 5 | T&E | £24,000 | £18,750 | +£5,250 | +21.9% | Misleading |
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Line Item | Annual Budget | YTD Budget | YTD Actual | Var £ | Var % | Flag |
| 2 | Revenue | £600,000 | £300,000 | £289,500 | −£10,500 | −3.5% | ⚠ |
| 3 | Payroll | £290,000 | £145,000 | £152,400 | −£7,400 | −5.1% | ⚠ |
| 4 | Marketing | £96,000 | £48,000 | £39,200 | +£8,800 | +18.3% | ⚠ |
| 5 | T&E | £24,000 | £12,000 | £18,750 | −£6,750 | −56.3% | ⚠ |
YTD Budget = Annual Budget × 6/12 Revenue: £600,000 × 6/12 = £300,000 £289,500 − £300,000 = −£10,500 (−3.5%) T&E: £24,000 × 6/12 = £12,000 £12,000 − £18,750 = −£6,750 (−56.3%) ✓ Use a non-linear budget profile for seasonal items instead of simple proration.
This is what makes the variance direction work. Revenue: Actual − Budget (positive = good). Expense: Budget − Actual (positive = good). Without the Type column, ChatGPT has to guess.
Comparing six months of actuals against a full-year budget is the most common mistake in variance analysis. If you're reporting Q2 YTD, provide the Q2 YTD budget — not the annual figure.
Pick one: percentage-only (flag if >10%), dollar-only (flag if >$50k), or combined. Most mid-size companies use combined with OR logic: flag if >5% or >$5,000.
"Marketing" in budget and "Marketing Costs" in actuals creates a mismatch. ChatGPT matches rows by exact text. Standardise before pasting.
The numbers come from formulas. The explanations come from your team — the people who know why a line went 30% over. ChatGPT can draft placeholder commentary, but your team should own the real story.