Organize information

Design a budget-versus-actual tracker in Excel

Compare approved budget, actuals, commitments, and forecast with clear variance signs

8 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Budget scope and decisionBudget and actual sourcesVariance and forecast rules

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Design an Excel budget-versus-actual tracker.

Scope, period, owners, categories, currency, and decision:
[scope]

Approved budget, transaction, commitment, and refresh sources:
[sources]

Variance, timing, accrual, and forecast rules:
[rules]

Keep approved budget versions separate from actual transactions and commitments. Define stable category keys, period mapping, variance signs, and treatment of credits, accruals, tax, and unclassified spend. Return source and summary table schemas, lookup and SUMIFS logic, actual-versus-budget and forecast-versus-budget formulas, flags, owner views, and reconciliation checks. Do not treat unspent budget as savings before timing and commitments are considered.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Budget scope and decision: Q3 2026 marketing operating spend in GBP by CostCenter, Category, and Owner. Monthly review decides corrective action.
Budget and actual sources: Budget_v2 approved 30 Jun with monthly rows; Actuals export refreshed weekly with TransactionID, PostingDate, CostCenter, CategoryCode, AmountGBP; Commitments table contains approved purchase orders not yet posted.
Variance and forecast rules: For expenses, positive variance = Actual−Budget and positive is unfavorable. Credits are negative actuals. Forecast=Actual+Commitments+owner estimate for remaining spend. Unmapped CategoryCode must be Review. Never alter Budget_v2.

Example output

Use immutable Budget, append-only Actuals, Commitments, CategoryMap, and Summary tables. Stable key = MonthStart|CostCenter|Category. Map CategoryCode through CategoryMap and keep unmapped transactions in Review while still reconciling their amount.

Summary columns: Month, CostCenter, Category, Owner, Budget, Actual, Commitments, OwnerRemainingEstimate, Forecast, ActualVariance, ActualVariancePct, ForecastVariance, ForecastVariancePct, Status, Comment. Populate Actual with SUMIFS on PostingDate month bounds plus mapped dimensions; use the same dimensions for commitments. Forecast = Actual+Commitments+OwnerRemainingEstimate. Variance = Actual−Budget; percentage returns N/A when Budget is zero.

Status examples: Review for unmapped data; Forecast over budget above an agreed materiality threshold; Timing if actual is low but commitments explain the gap; On track otherwise. Do not automatically call negative actual variance savings. Reconcile Actuals total, including Review, to the finance export; check TransactionID uniqueness; reconcile Summary budget to Budget_v2; show unclassified amounts separately; lock formula and approved-budget cells; record refresh date and budget version.

Why this works

1
Separate source tables prevent refreshed actuals from overwriting approved budget history.
2
Commitments and timing distinguish genuine forecast risk from temporary underspend.