Rolling budget in Excel: extend and reconcile the plan

A rolling budget in Excel keeps a fixed number of future months visible while preserving completed months as actual results. Close the latest period, add a new month at the far end, and save the revised estimates under a new version. Keep the approved annual budget intact so a better estimate does not erase the original spending commitment.

Start with versioned rows

Use the existing annual business budget template if you need a driver-based starting budget. This workflow adds the monthly roll and the controls around revisions. The distinction between an approved commitment and an updated expectation is covered in budget vs forecast.

Create a table named Budget with these columns:

Use one row per version, month, and cost center for this simplified workbook. If you need account-level detail, add Account to both tables and to every comparison. Do not mix detailed rows with subtotal rows.

Create a separate Actuals table with Month, CostCenter, and Amount. These amounts come from the closed general ledger, aggregated to the same level as the budget. Keep revenue and expense comparisons separate because the meaning of a favorable variance differs.

Microsoft's structured-reference guidance explains that table references adjust as rows are added or removed. That makes named tables useful when the planning horizon grows. Covirage is not affiliated with Microsoft.

Close October before extending the horizon

Consider a US distributor's expense budget. A three-month horizon keeps the illustration small; apply the same process to your full planning horizon. All amounts below are whole USD, with no rounding.

The original view covers October through December 2026:

October closes with these actual expenses:

The $2,000 total is unfavorable for expenses. Reconcile $62,000 to the closed ledger before publishing it. A late journal should trigger a documented actuals refresh, rather than silently changing the budget baseline.

Now remove October from the forward horizon and append January 2027. Keep October actuals available in the historical report; removing a period from the forward view does not mean deleting its rows.

Add the new month, then identify revisions

V1 retains November and December estimates and adds January. V2 records the cost centers' revised assumptions:

Store the two versions as separate rows in Budget. The display above places them side by side for review. Record an explanation and owner for every changed estimate before approving V2.

Build the monthly Excel report

On a report sheet, put Month in A, CostCenter in B, V1 in C, V2 in D, and Change in E. Enter actual month-start dates in A. To advance the next monthly date:

=DATE(YEAR(A2),MONTH(A2)+1,1)

For each month and cost-center combination, retrieve the version amounts:

C2 = SUMIFS(Budget[Amount],Budget[Version],"V1",Budget[Month],A2,Budget[CostCenter],B2)
D2 = SUMIFS(Budget[Amount],Budget[Version],"V2",Budget[Month],A2,Budget[CostCenter],B2)
E2 = D2-C2

These labels identify the destination cells; enter the expression after the label. Microsoft documents SUMIFS as summing values that meet multiple criteria. Here those criteria prevent periods, departments, and versions from being mixed.

For a combined historical-and-forward view, put the last closed month in H1 and the selected version in H2:

=IF(A2<=$H$1,
 SUMIFS(Actuals[Amount],Actuals[Month],A2,Actuals[CostCenter],B2),
 SUMIFS(Budget[Amount],Budget[Version],$H$2,Budget[Month],A2,Budget[CostCenter],B2))

Use a month-start date for H1 too. Label the output Actual or Budget so readers can see the boundary. Check that expected rows exist: SUMIFS returns zero for an absent match, which does not prove that spending is genuinely zero.

Reconcile the change over matching periods

The original horizon totals $189,000. Removing October's $60,000 and adding January's V1 estimate of $69,000 produces $198,000. That $9,000 change comes entirely from the horizon.

The overlapping November–December periods total $129,000 in V1 and $130,500 in V2, a $1,500 revision. January rises from $69,000 to $70,500, adding another $1,500. Together those changes explain V2's $3,000 increase to $201,000.

Check the same difference by cost center: Operations moves from $132,000 to $135,000; Sales stays at $66,000. The departmental changes must sum to the monthly changes. For performance reporting, use the separate budget vs actual template rather than treating revisions as actual spending variances.

Where it goes wrong

• Overwriting published versions. You lose the evidence needed to explain why the outlook changed. Copy approved estimates into a new version before editing.
• Comparing different horizons. A new January and a removed October can change the total without any revision to overlapping months.
• Duplicating cost-center rows. SUMIFS adds every match. Validate the version–month–cost-center key before accepting a total.
• Mixing actuals and estimates in one unlabeled column. A cutoff and clear status prevent users from reading future estimates as posted results.

Compare your own budget versions in Covirage

Bring your versioned budget workbook and matching actual expense rows to Covirage. Covirage maps the columns; its tools compute and check the comparisons, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See FP&A reporting for the finance reporting context.