Build a budget vs. actual variance analysis in Excel
Sets up a budget vs. actual sheet with correct variance formulas, favorable/unfavorable logic, materiality flags, and a YTD view.
The prompt
Help me build a budget vs. actual (or forecast vs. actual, or this year vs. last year) variance analysis in Excel.
What I'm comparing: [budget vs. actual / forecast vs. actual / current vs. prior year]
Line items: [PASTE - e.g. revenue lines, cost categories, headcount, departments]
Numbers: [PASTE budget and actual by line and month, or a sample]
Period: [e.g. "September 2026 and YTD"]
Materiality threshold for comments: [e.g. "over $10k and over 5%"]
Who will read it: [e.g. "department heads", "CFO"]
Build:
1. Layout: Line | Budget | Actual | Variance $ | Variance % | Fav/Unfav | Comment, for the month and YTD side by side, with subtotals that add up.
2. Sign logic: for revenue, actual above budget is favorable; for costs, below budget is favorable. Give one formula approach that handles both (e.g. a +1/-1 "type" column), so the Fav/Unfav label is never wrong.
3. Formulas: variance %, protected from zero or blank budgets (show "n/a" instead of #DIV/0!), and a materiality flag that only lights up lines needing explanation.
4. Conditional formatting for material adverse variances.
5. Checks: totals tie to the source, budget totals tie to the approved budget, and months without actuals are excluded from YTD.
6. Questions to ask each budget owner for every flagged line: timing or permanent, price or volume, one-off or recurring.
Return the layout, all formulas, the checks, and a short summary of the biggest variances using only my numbers. Do not invent reasons for variances - leave [ADD REASON] for owners to fill in.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Finance & Accounting prompts
What to fill in
- PASTE budget and actual by line and month, or a sample
- e.g. "September 2026 and YTD"
- e.g. "over $10k and over 5%"
- e.g. "department heads", "CFO"
- ADD REASON
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Prepare a Good Faith Estimate for a self-pay patientBuilds a No Surprises Act Good Faith Estimate for uninsured or self-pay patients with required elements, timing, and a plain-language cover note.
- Review a pre-bill before it goes to the clientA billing partner's pre-bill review that catches write-down risks, guideline violations, and errors, and estimates realization before invoices go out.
- Explain manufacturing cost variances in my monthly resultsA breakdown of standard cost variances (material, labor, overhead absorption) into what drove them, with commentary operations and finance both accept.
- Set up tracking for restricted gifts and grantsA system to record donor restrictions, track spending against them, and release net assets correctly at month end.