Budget Tracker

---
name: budget-tracker
description: Use when building a budget vs actual tracker — departmental, monthly, with variance analysis. Triggers on "budget tracker", "budget vs actual", "BvA", "departmental budget xlsx".
---

# Budget Tracker

Build a budget tracker that the department head actually opens every month. The reason most trackers gather dust: the data load is painful, the variance column is buried, and there's no clear "is this fine or not" signal. Fix all three.

## Required inputs

1. **Budget categories** — flat list (Marketing) or hierarchical (Marketing > Paid > LinkedIn). Be explicit about depth.
2. **Monthly budget by category** — the plan. Often loaded from the annual planning model.
3. **Cost centers / sub-teams** — if rolling up multiple
4. **Fiscal year boundaries** — calendar year or off-cycle (e.g., Feb-Jan)
5. **Source of actuals** — CSV from accounting, manual entry, GL pull. Affects the import tab design.
6. **Variance threshold** — what counts as "needs explanation"? Default: ±10% AND >$5k absolute.

## Approach

Use **openpyxl** for the workbook, with conditional formatting for the variance column (red fill on overspend > threshold, amber for warning, green for under-budget). Conditional formatting via openpyxl is a bit fiddly — apply rules to whole ranges, not cell-by-cell.

For the actuals import, design the **Actuals_Import** tab as a paste target — the user pastes the CSV and SUMIFS pulls into the main view. Don't try to parse arbitrary CSVs in the model.

## Workbook structure

1. **Cover** — fiscal year, owner, last refresh date, color key
2. **Categories** — the chart of accounts for this tracker. Category code, display name, parent category, owner. Named range `CategoryList`.
3. **Budget** — categories down, months across. Annual total column on the right. The plan, locked once approved.
4. **Actuals_Import** — a paste-target tab. Columns: Date, Category Code, Amount, Vendor, Memo. SUMIFS aggregates into the main view.
5. **BvA Monthly** — the main view. For each category × month: Budget | Actual | Variance ($) | Variance (%) | Flag. Conditional format the Flag column.
6. **YTD Summary** — categories × YTD Budget, YTD Actual, YTD Variance, % of annual budget consumed, run-rate vs plan.
7. **Forecast** — actuals through current month + budget for remaining months OR a rolled-forward projection. One row per category. The "where will we land" view.
8. **Rollforward** — opening balance + commits + actuals + remaining = closing. For project-based budgets where line items consume.
9. **Notes** — a free-text variance commentary tab. Each row: month, category, variance, explanation, owner, action.

## Formula principles

- **SUMIFS for the actuals pull.** `=SUMIFS(Actuals_Import[Amount], Actuals_Import[Category Code], $A5, Actuals_Import[Date], ">="&MonthStart, Actuals_Import[Date], "<="&MonthEnd)`. This is the engine.
- **EOMONTH** for month-end boundaries — `=EOMONTH(MonthDate, 0)`.
- **Variance % handles zero budget**: `=IFERROR((Actual-Budget)/Budget, IF(Actual=0, 0, "n/a"))`. Avoid #DIV/0!.
- **Flag formula**: `=IF(ABS(Variance)<Threshold_Abs, "OK", IF(Variance>0, "OVER", "UNDER"))`. Then conditional format the cell color off the flag text.
- **YTD as SUMIFS up to current month** — uses a `Current_Month` named cell on Cover so the whole tab updates with one input.
- **XLOOKUP for category metadata** (parent category, owner) — joins on category code.

## Common pitfalls

- **Hardcoding actuals into the BvA tab.** Now next month's load overwrites this month's review. Always pull via SUMIFS from Actuals_Import.
- **No category code, only display names.** "Marketing - Paid - LinkedIn" gets mistyped as "Marketing-Paid-LinkedIn" and rolls to a different bucket. Always have a stable code.
- **Merged cells in the budget tab.** Breaks SUMIFS lookups silently.
- **No threshold logic.** A 0.5% variance shouldn't be flagged red. Make threshold configurable on Cover.
- **Forecast as "annual budget minus YTD actuals".** Wrong if you're tracking accruals or have known commits. Build forecast as YTD actuals + future-month plan + known commits.
- **Variance column without a sign convention.** Pick one and document on Cover: usually + = overspend, - = underspend (cost lens). Revenue lens is the opposite — be explicit.

## Common formulas to include

- Burn rate (last 3 months avg): `=AVERAGE(OFFSET(Current_Month_Cell, 0, -2, 1, 3))`
- Run-rate full year: `=YTD_Actual / Months_Elapsed * 12`
- % of annual budget consumed: `=YTD_Actual / Annual_Budget`

## Output

Generate the .xlsx. Pre-populate the Budget tab from inputs. Leave Actuals_Import empty with a header row and a one-line instruction in row 1: "Paste CSV with columns: Date | Category Code | Amount | Vendor | Memo. Do not edit the BvA tab directly."

Print a summary: total annual budget, top 3 largest line items, recommended monthly review cadence (week 1 of the next month), and a reminder to lock the Budget tab once the year starts to prevent silent re-baselining.