luvv to helpDiscover the Best Free Online Tools
Topic 2 of 8

Budget Tracking Models

Learn Budget Tracking Models for free with explanations, exercises, and a quick test (for Marketing Analyst).

Published: December 22, 2025 | Updated: December 22, 2025

Table of Contents

Why this matters

Marketing analysts are expected to keep spend on target, react to pacing issues, and explain budget variances. Strong budget tracking models help you:

  • See Plan vs Actual vs Forecast by channel, campaign, and month.
  • Catch under/overspend early using pacing and burn-rate checks.
  • Reforecast EOM spend credibly and roll forward carryover to next months.
  • Support media planning conversations with clear, audit-ready numbers.

Concept explained simply

A budget tracking model is a spreadsheet that compares what you planned to spend with what you actually spent, and predicts where you will land by the end of the period.

  • Plan (Budget): what you intended to spend.
  • Actual: what has been spent so far.
  • Variance: difference between Actual and Budget.
  • Pacing: whether spend-to-date is ahead or behind schedule.
  • Forecast: your expected final spend given current trajectory.

Mental model: think of the budget as a monthly bucket. Each day you pour spend into the bucket. Pacing tells you if you’re filling the bucket too fast or too slow; forecast tells you how full it’ll be by month-end.

Key components of a solid model
  • Dimensions: Month, Channel, Campaign (optional), Currency (optional).
  • Measures: Budget, Actual-to-date, Remaining Budget, Variance, Pacing %, Forecast EOM, Variance to Forecast.
  • Helpers: Days passed, Days in month, Daily budget, Daily spend, Remaining days.
  • Data flows: Budgets (static table), Actuals (import or paste), Calculations (formulas), Output (summary view).

Core model structure (step-by-step)

  1. Create reference calendar: a table with Month (e.g., 2025-03), StartOfMonth, EndOfMonth, DaysInMonth = DAY(EOMONTH(StartOfMonth, 0)).
  2. Budget table: columns [Month, Channel, Campaign, Budget]. One row per combination.
  3. Actuals table: raw daily or transaction-level data with [Date, Channel, Campaign, Spend].
  4. Aggregate actuals: MonthlyActual = SUMIFS(Spend, Channel, thisChannel, Campaign, thisCampaign, Date, ">="&StartOfMonth, Date, "<="&EndOfMonth).
  5. Pacing helpers: DaysPassed = TODAY() - EOMONTH(TODAY(), -1); RemainingDays = DAY(EOMONTH(TODAY(),0)) - DaysPassed.
  6. Daily targets: DailyBudget = Budget / DaysInMonth; DailySpend = IF(DaysPassed>0, ActualToDate/DaysPassed, 0).
  7. Pacing %: Pacing = IF(DailyBudget>0, DailySpend / DailyBudget, 0).
  8. Forecast EOM: Forecast = ActualToDate + (DailySpend * RemainingDays).
  9. Variances: Var$ = ActualToDate - ProportionalBudget; Var% = IF(ProportionalBudget>0, Var$/ProportionalBudget, 0). ProportionalBudget = Budget * (DaysPassed/DaysInMonth).
  10. Summary view: Pivot or summary table showing Plan, Actual, Pacing, Forecast, Variance by Month and Channel.

Worked examples

Example 1 — Monthly pacing by channel

Assume March has 31 days. Budget for Meta = 31,000. Today is March 10 (DaysPassed=10, RemainingDays=21). Actual-to-date = 8,000.

  • DailyBudget = 31,000 / 31 = 1,000
  • DailySpend = 8,000 / 10 = 800
  • Pacing = 800 / 1,000 = 0.80 (80%) → underpacing
  • ProportionalBudget = 31,000 * (10/31) = 10,000
  • Variance-to-date = 8,000 - 10,000 = -2,000
  • Forecast EOM = 8,000 + (800 * 21) = 24,800

Meta is likely to underspend unless daily spend increases.

Example 2 — SUMIFS aggregation

Compute ActualToDate for Google Ads in March:

=SUMIFS(Actuals!$D:$D, Actuals!$A:$A, ">="&StartOfMonth, Actuals!$A:$A, "<="&TODAY(), Actuals!$B:$B, "Google Ads")

Where Actuals!A is Date, B is Channel, D is Spend.

Example 3 — Carryover logic

If Forecast EOM is 24,800 vs Budget 31,000, Carryover = 31,000 - 24,800 = 6,200. Add this to April’s budget if business rules allow.

Carryover = MAX(0, Budget - Forecast)

Templates you can recreate quickly

Budget table

Columns: Month | Channel | Campaign | Budget

Ensure Month is a true date (e.g., first day of month) to simplify time functions.

Summary view

Columns: Month | Channel | Budget | ActualToDate | ProportionalBudget | Pacing% | ForecastEOM | VarToBudget$ | VarToBudget%

Quality checks and self-audit

  • Totals tie-out: SUM(Budget) vs SUM of channel budgets equals planned total.
  • No double counting: Actuals aggregation should use correct date bounds and channel filters.
  • Time logic: DaysPassed never exceeds DaysInMonth; handle month boundaries.
  • Division safety: IFERROR or IF checks prevent divide-by-zero.
  • Currency: If multi-currency, convert Actuals before aggregation using a fixed rate table for the month.

Common mistakes and how to fix

  • Using calendar month-to-date actuals with a quarter budget. Fix: align grain (month-to-month).
  • Pacing vs variance confusion. Fix: Pacing compares daily rates; variance-to-date compares Actual to ProportionalBudget.
  • Forecast assuming linear spend on launch days. Fix: cap DailySpend using last 3–5 days average or business rules.
  • Incorrect date criteria in SUMIFS. Fix: use ">="&StartOfMonth and "<="&min(TODAY(), EndOfMonth).
  • Hidden filters in pivots. Fix: reset filters and verify totals against raw data.

Exercises

Build these in your spreadsheet. You can check yourself against the solutions below each exercise entry.

  1. Exercise 1 — Monthly budget tracker with pacing
    • Create Budget and Actuals tables.
    • Compute ActualToDate, ProportionalBudget, Pacing, and ForecastEOM by Channel for the current month.
    • Highlight channels under 90% pacing in amber and over 110% in red.
  2. Exercise 2 — Reforecast scenario with carryover
    • Using your tracker, estimate carryover for underspending channels.
    • Add carryover to next month’s budget; recompute next month pacing assuming similar daily rate.
  • [Checklist] My date filters correctly limit spend to Month-To-Date.
  • [Checklist] Pacing and Forecast change when I adjust Today’s date cell.
  • [Checklist] Totals reconcile to raw Actuals and Budget tables.

Practical projects

  • Q-quarter dashboard: Build a 3-month view by channel with plan, actual, forecast, and carryover rollups.
  • Campaign-level drilldown: Extend the model to campaign rows, then aggregate to channel with SUMIFS.
  • What-if control: Add a slider/input for target pacing (e.g., 1.05) and show the daily spend needed to hit budget.

Who this is for

  • Marketing analysts who manage or report on paid media budgets.
  • Performance marketers needing a reliable spreadsheet tracker.
  • Junior analysts preparing for stakeholder reviews.

Prerequisites

  • Comfort with spreadsheets: SUMIFS, IF, IFERROR, EOMONTH, DAY.
  • Basic understanding of channels (e.g., search, social) and monthly budgeting.

Learning path

  • Before this: Data cleaning, date functions, PivotTables/SUMIFS.
  • This lesson: Build Plan vs Actual vs Forecast with pacing.
  • Next: ROI/CPA targets, scenario planning, and multi-currency consolidation.

Mini challenge

You are on Day 12 of a 30-day month. Budget for TikTok is 24,000. Actual-to-date is 9,000.

  • Compute DailyBudget, DailySpend, Pacing, Forecast EOM, and Carryover.
  • Decide: increase daily spend or accept carryover? State your rule of thumb.

Next steps

  • Make a copy of your model for next month and test roll-forward formulas.
  • Add simple conditional formatting for risk flags.
  • Prepare a one-slide summary that explains variance drivers in plain language.

Quick Test

This test is available to everyone; only logged-in users get saved progress.

Practice Exercises

2 exercises to complete

Instructions

  1. Create two sheets: Budget and Actuals. Budget columns: Month (first day of month), Channel, Budget. Actuals columns: Date, Channel, Spend.
  2. On a Summary sheet, reference a cell TodayDate to simulate the current date.
  3. For each Channel+Month row, compute:
    • ActualToDate: SUMIFS over Actuals between StartOfMonth and min(TodayDate, EndOfMonth).
    • DaysPassed, DaysInMonth, RemainingDays.
    • DailyBudget, DailySpend, Pacing.
    • ForecastEOM = ActualToDate + DailySpend * RemainingDays.
  4. Apply conditional formatting: Pacing < 0.9 = amber; Pacing > 1.1 = red.
Expected Output
A summary table by Channel for the current month with correct Plan, ActualToDate, Pacing%, and ForecastEOM that change when TodayDate changes.

Budget Tracking Models — Quick Test

Test your knowledge with 8 questions. Pass with 70% or higher.

8 questions70% to pass
Cleaning Data In SheetsCampaign Reporting Templates

Have questions about Budget Tracking Models?

AI Assistant

Ask questions about this tool