# 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.

- Topic: Finance & Accounting
- Framework: Few-Shot
- Level: Advanced
- Tags: Finance, Data & Analytics, Analyze, Fill-in Template
- Source: https://www.thinkwithprompt.com/prompts/build-a-budget-vs-actual-variance-analysis-in-excel

## Prompt

```text
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.
```

## 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

Free to copy and use. From ThinkWithPrompt (https://www.thinkwithprompt.com/), a library of 2,663 curated AI prompts.