Building a Monthly Budget Model in Excel

Step 1: the grid shape

Lay out Department down the rows — Sales, Marketing, Admin, Logistics, Payroll — and Month across the columns, matching the financial year. This shape is standard for budget models because it’s exactly how PivotTables and INDEX/MATCH lookups expect to retrieve a value later.

Step 2: pull the budget with mixed-reference SUMIFS

Budget figures usually come from a finance-approved source file — the job in Excel is to structure them cleanly, not to invent the numbers:

=SUMIFS(BudgetActual[BudgetAmount],BudgetActual[Department],$A2,BudgetActual[Month],B$1)

The mixed references are what make this formula copyable across the whole grid without manual adjustment: $A2 locks the column but lets the row float, so every row always reads its own department; B$1 locks the row but lets the column float, so every column always reads its own month. Build the formula once in the top-left cell, then copy it across the entire grid.

Step 3: fixed vs rolling budgets

One design decision worth calling out explicitly: should each month’s budget be a fixed number, or roll forward with a formula like “last year plus 5%”? Both are legitimate. Most SME budgets work as a fixed planned input per month, decided once at the start of the year rather than recalculated dynamically. The grid shape still works for zero-based or rolling budgeting — only the formula feeding each cell changes.

Step 4: Total row and Total column as a reconciliation check

=SUM(B2:M2)   ' Total row
=SUM(B2:B6)   ' Total column

Add both so the grid shows total budget per department and total budget per month at a glance — a useful sanity check that the numbers actually add up to what leadership approved.

A worked example

A finance team approving an annual budget once, broken down by department and month, uses exactly this grid as the reference structure every later variance report compares actuals against — which is precisely why getting the shape and the mixed references right here matters: everything downstream depends on it.

Mistakes that break the grid

• —Using a fully relative or fully absolute reference instead of the correct mixed reference — this breaks the copy-across-the-grid pattern, since every cell needs its own row and column to float independently.
• —Confusing budget (planned) with actual (what happened). This grid is only the budget half — actuals and variance are a separate layer built on top.
• —Skipping the Total row/column and losing the one easy check that the grid reconciles with the approved annual figure.