Build a Budget Variance Analysis Report in Excel with Claude AI
Progress1 of 4
1
How the report works
2
What Claude needs from you
3
Prompt, FAQ & related
4
Quick quiz
Section 01
A budget without variance analysis is a wish list. This report turns it into accountability.
Every finance team has a budget. Most also have actuals. The question leadership keeps asking is simple: where are we off plan, by how much, and does it matter? Answering that question manually — calculating variances, applying the right direction for revenue vs expenses, flagging material items, adding commentary — takes hours of spreadsheet work every month.
Claude does the mechanical part in minutes. You paste budget and actuals, tell it your threshold rules, and get back a variance report with dollar and percentage variances, favourable/unfavourable labels, conditional flags, and a commentary column ready for your team to fill in.
The Pipeline: Budget + Actuals → Flagged Variance Report
Budget & Actuals
Variance formulas
Threshold check
Flagged report
Three Types of Variance — Three Different Questions
Revenue Variance
Actual − Budget
Positive = Favourable
Did we earn more than planned?
Expense Variance
Budget − Actual
Positive = Favourable
Did we spend less than planned?
Margin Variance
Actual margin − Budget margin
Shows efficiency, not just volume
Is our profitability improving?
The Output: What Claude Produces
A
B
C
D
E
F
1
Line Item
Budget
Actual
Var $
Var %
Flag
2
Revenue
500,000
525,000
25,000
5.0%
—
3
COGS
200,000
215,000
-15,000
-7.5%
⚠ Review
4
Gross Profit
300,000
310,000
10,000
3.3%
—
5
Payroll
180,000
178,500
1,500
0.8%
—
6
Marketing
40,000
52,000
-12,000
-30.0%
⚠ Review
7
Rent & Utilities
30,000
30,200
-200
-0.7%
—
8
Net Income
50,000
49,300
-700
-1.4%
—
COGS and Marketing are flagged (exceed 5% OR $5,000 threshold). Revenue variance is favourable but below flag threshold. Rent is unfavourable but immaterial. The flag column is formula-driven — not manually coloured.
Key insight: The report doesn't just calculate gaps — it tells you which gaps matter. Rent is $200 over budget. Marketing is $12,000 over. Both are unfavourable. Only one needs a conversation with leadership. The threshold flag makes that distinction automatic.
What Claude Needs From You
Claude needs three things: your budget numbers, your actual numbers, and the rules for what counts as material. Without the first two, there's nothing to compare. Without the third, every variance gets flagged — and a report where everything is flagged is the same as a report where nothing is.
Input Data Layout
A
B
C
D
1
Line Item
Type
Budget
Actual
2
Revenue
Revenue
500,000
525,000
3
COGS
Expense
200,000
215,000
4
Payroll
Expense
180,000
178,500
5
Marketing
Expense
40,000
52,000
01
Label each line as Revenue or Expense
This is what makes the variance direction work. Revenue: Actual − Budget (positive = good). Expense: Budget − Actual (positive = good). Without the Type column, Claude has to guess — and guessing means your COGS variance might show the wrong sign.
02
Match the reporting period exactly
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. Tell Claude the period: January–June 2025.
03
Define your flag threshold
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. This catches both large absolute swings and proportionally big moves on small line items.
04
Use consistent line-item names
"Marketing" in budget and "Marketing Costs" in actuals creates a mismatch. Claude matches rows by exact text. Standardise before pasting.
Period mismatch trap: Comparing six months of actuals against an annual budget will show every expense line as "favourable" and every revenue line as "unfavourable" — because you're only halfway through. Always compare like-for-like periods.
Pro tip: Leave the Commentary column blank in Claude's output. The numbers come from formulas. The explanations come from your team — the people who know that Marketing went 30% over because leadership approved an unbudgeted campaign in May. Claude can draft placeholder commentary, but your team replaces it with the real story. This is part of the month-end close process.
The Claude Prompt for Variance Analysis
Prompt — paste into Claude
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.
The Report Surfaces the Question. Your Team Answers It.
Claude can tell you that Marketing is $12,000 over budget. It cannot tell you why. Was it a strategic decision to accelerate a campaign? A vendor billing error? An uncontrolled cost overrun? The variance report exists to focus the conversation on the items that matter — the ones above threshold — and skip the ones that don't.
If you're comparing against a rolling forecast instead of a static annual budget, the rolling forecast course shows how to build the comparison base. If the variances need to feed into a broader performance view, the financial KPI dashboard turns the key variance metrics into a trackable, monthly dashboard with RAG status flags.
Threshold Logic: Which Rule Fits Your Business
A
B
C
1
Rule
Best For
Risk
2
% only (>10%)
Small companies — every line matters
Tiny lines swing wildly on % (e.g. $50 on $200 = 25%)
3
$ only (>$50k)
Large companies with big line items
Misses proportionally large swings on mid-size lines
4
% AND $ (both must breach)
Conservative — surfaces only very material items
May miss important but smaller variances
5
% OR $ (either breaches)
Most mid-size companies — good balance
More items flagged, but covers both large and proportional swings
The prompt defaults to OR logic. Change to AND if your report is flagging too many lines. The goal is 3–6 flagged items per report — enough to focus the meeting, few enough to discuss each one.
Frequently Asked Questions
Should variance be Budget minus Actual or Actual minus Budget?
It depends on the line type. Revenue: Actual − Budget (positive = favourable). Expenses: Budget − Actual (positive = favourable). The Type column in your input tells Claude which direction to use.
What threshold should trigger a flag?
A common rule: flag anything exceeding 5% or $5,000. The right threshold depends on materiality for your business. The prompt lets you set both.
Can Claude write the variance commentary?
Claude can draft placeholder commentary based on the numbers. But meaningful explanations require business context — was the overspend approved? Was it an error? Your team fills that in.
How do I handle a partial-year report?
Compare YTD actuals against the YTD budget — not the annual budget. Tell Claude the period so it labels correctly and can prorate if needed.
What's the difference between favourable and unfavourable?
Favourable improves the bottom line (more revenue or less spending). Unfavourable hurts it (less revenue or more spending). The label depends on the line type, not just the sign.