Playbook: Build a Budget Variance Dashboard

Finance publishes a budget at the start of the quarter. By week three, nobody knows where actual spend stands relative to plan — not the department heads, not the CFO, and not the FP&A analyst who built the budget in the first place. The ERP has the actuals. The budget lives in a spreadsheet. Comparing them requires exporting both, pasting into a third spreadsheet, and writing formulas that break every time someone adds a row.

The quarterly variance report takes two days to compile. By the time it circulates, the numbers are a week old. Decisions that should be made in week three get made in week six.

This playbook walks you through building a variance dashboard — a sandcastle that shows budget versus actual by department and cost center, updated automatically, with drill-downs and alerts.

What you will build

  • A Budget Variance sandcastle with three views: a summary dashboard, a department drill-down, and an alert log.
  • Collections for budget_lines (the plan), actuals (the spend), and alerts (triggered variances).
  • A linked subagent with a custom tool that pulls monthly actuals from your ERP.
  • A scheduled sync that refreshes actuals weekly and flags any department that exceeds its threshold.

What you need before you start

  • An Assist workspace with access to the AI chat.
  • The budget data. You probably have this as a spreadsheet. You will paste it into the sandcastle as seed data — collection documents, one row per budget line.
  • Read API access to your ERP's general ledger or spend reporting endpoint, so the subagent can pull actuals. If your ERP doesn't have an API, any system that can export monthly spend by cost center will work.
  • A published custom tool that pulls actuals. If you don't have one, build it in /tools/new following the shared toolbox playbook.

Step 1: Prepare the budget data

Structure the budget as a flat list. Each line should have: department, cost_center, category (e.g., "Software", "Travel", "Contractors"), q1_budget, q2_budget, q3_budget, q4_budget, and annual_budget. If you have monthly granularity, even better: use jan_budget through dec_budget.

You will paste this into the AI prompt so it can seed the collection. If the list is long (100+ lines), paste the first 10 as examples and upload the rest through the data browser after scaffolding.

Step 2: Describe the app

"Build me a sandcastle called 'Budget Variance'. It tracks budget versus actual spend by department and cost center.

Collections:

  • budget_lines: department, cost_center, category, monthly budgets (jan through dec), annual_budget.
  • actuals: department, cost_center, category, month (YYYY-MM), amount, source (which system the number came from), pulled_at (when it was last refreshed).
  • alerts: department, cost_center, month, budget_amount, actual_amount, variance_pct, severity (warning/critical), created_at, acknowledged (boolean).

Views:

  1. Summary dashboard — one card per department showing: YTD budget, YTD actual, variance ($ and %), a small sparkline of monthly spend vs budget, and a color indicator (green <5% over, yellow 5-15%, red >15%). Sort by worst variance first.
  2. Department drill-down — click a department card to see a table of every cost center in that department. Columns: cost center, category, month budget, month actual, month variance, YTD budget, YTD actual, YTD variance. Filterable by category. Include a bar chart comparing budget vs actual by category.
  3. Alert log — a chronological list of variance alerts. Each row shows department, cost center, month, variance %, severity, and an acknowledge button.

Seed budget_lines with these 10 rows: [paste your budget data here]. Seed actuals with 3 months of realistic example data. Generate 5 example alerts."

Step 3: Review, scaffold, iterate

Review the plan. Confirm the variance formula matches your expectation (usually (actual - budget) / budget * 100). Scaffold. Preview.

Common refinements:

"The sparklines are too small. Make them twice as tall and add axis labels for the months."

"Add a 'notes' field to budget_lines so FP&A can annotate exceptions (like 'includes one-time license renewal')."

"On the summary dashboard, add a total row at the bottom that sums all departments."

Step 4: Load the real budget

If you seeded with just 10 example lines, now load the full budget. Open the sandcastle's Data page, go to the budget_lines collection, and create documents for each line. Or export your spreadsheet as JSON and use the AI to bulk-insert:

"Insert these budget lines into the budget_lines collection: [paste JSON array]."

The dashboard updates to show the real numbers (with placeholder actuals until the sync runs).

Step 5: Wire the actuals sync

Add your ERP actuals tool to the linked subagent's whitelist (see Adding tools to a subagent). Then schedule the sync:

"Every Monday at 6 AM Eastern, for every department in the budget_lines collection:

  1. Call erp_monthly_spend with the department and the current month.
  2. Upsert the results into the actuals collection (match on department + cost_center + month).
  3. For each cost center where actual exceeds budget by more than 15%, insert an alert with severity 'critical'. For 5-15%, insert with severity 'warning'. Skip if an unacknowledged alert already exists for the same cost_center and month.
  4. Post to #finance-ops: 'Budget variance refreshed. X departments over threshold. Worst: [department] at +Y%.'

Call the trigger 'Weekly Variance Sync'."

Step 6: Let finance use it

Grant Execute access to the finance team and department heads through Workspace > Permissions > Resources. Each department head can drill into their department; the FP&A analyst can see everything and acknowledge alerts.

Tell the team: "The variance dashboard updates every Monday. If you see something wrong, post in #finance-ops and tag @fpa."

Step 7: Add a month-end close view

As the team starts using the dashboard, they will want a month-end summary. Iterate:

"Add a fourth view: 'Month-End Summary'. It shows, for a selectable month, every department's total budget, total actual, and variance. Include a pie chart of spend by category across all departments. Add an 'Export' button that copies the table as tab-separated text so they can paste it into their close document."

What you built

You have a budget variance dashboard that:

  • Shows every department head where they stand, in real time, without waiting for FP&A to compile a report.
  • Alerts finance when spend exceeds threshold — before the quarter is over, not after.
  • Updates itself weekly from the ERP, so the numbers are never more than a week old.
  • Gives FP&A a single place to acknowledge, annotate, and track budget exceptions.
  • Runs in your workspace — no spreadsheet versioning, no export/import, no "which column is the right one."

What used to be a two-day quarterly fire drill is now a Monday-morning glance.

Where to go from here

  • Forecast. Add a forecasts collection where FP&A enters revised estimates. Show forecast versus budget versus actual on the same chart.
  • Granularity. If your ERP supports daily or weekly pulls, increase the sync frequency. The dashboard resolution matches the data resolution.
  • Approvals. When a department exceeds its budget, the subagent can automatically create a budget exception request in the Purchase Approval Tracker.
  • Multi-entity. If you have multiple subsidiaries or business units, add an entity dimension and a consolidated view.

Related guides