Rolling Forecasts in Excel Beyond the Annual Budget

An annual budget is a fixed statement about a year that has not happened yet. It is approved before the year opens, and from the first day of trading the actual year diverges from it. Most SME finance teams notice this in the second half, when the remaining months have to absorb a variance that is already booked and the full-year number stops behaving like a plan.

A rolling forecast answers the same question with a window that moves. Each month the closed month is replaced by actuals, the forecast extends by one month at the far end, and the business always holds twelve months of forward view. The annual budget stays as the fixed reference, so the board reads performance against the plan and the movement in the expectation from the same grid.

The formulas are modest, and the work sits in the discipline around them: an owner named against each driver, a short re-forecast session between the close and the pack, and a lock on the closed periods so the forecast cannot be edited backwards into history.

When the Annual Budget Goes Stale

A budget is written to authorise spend and to set a target. Those purposes give it a long life, and they also explain why it stops working as a forecast. Its assumptions were formed during the planning round, months before the year began, and the year then delivers a price increase in February, a customer renewing on different terms in April and wage growth above the assumption. None of that invalidates the budget as a target, and all of it invalidates the budget as a description of the year ahead.

The practical problem shows up in the full-year number. Once three months are booked, that figure is built from three months of evidence and nine months of assumption, and the nine months carry every planning error the first three revealed. The board cannot tell which part of the number is measurement and which part is leftover intention.

Choosing the Horizon and the Update Cadence

Three horizons cover most SMEs, and the choice follows from how long the business takes to convert a decision into revenue.

Twelve months rolling is the standard for a trading business. The window always contains four quarters of revenue and removes the year-end cliff where a model has no forward view on the first day of the new financial year. The compromise is that a March close produces a window ending in March of the following year, so the window spans two financial years and cannot be summed straight into the full-year number.

A rest-of-year horizon suits a board and a bank that work to the financial year. The window shortens as the year runs, and by November it carries almost no forward view, so teams on this option usually keep a separate twelve-month view for funding decisions.

An eighteen-month horizon suits capital-heavy businesses and long sales cycles, such as a construction or equipment business converting a pipeline over nine to twelve months.

The cadence matters more than the horizon. A monthly re-forecast after the close replaces the closed month, imports the actuals, and asks each owner to confirm or change their assumption the same day, so the restatement finishes before the pack is drafted. A quarterly re-forecast with a monthly cash overlay restates the profit and loss drivers quarterly and rolls cash monthly, because receipts, GST and payroll move on their own timetable.

The illustrative grid below shows the same model at three close dates in 2026. A marks a closed month driven by actuals, F marks a live forecast month, and O marks a month outside the current window.

Each column carries exactly twelve forecast months, and every closed month to its left holds an actual.

The Roll-Forward Mechanics in Excel

The example uses four sheets. Actuals receives the monthly ledger export, Drivers holds editable assumptions, DriverCalc calculates forecast values, and Report carries the month grid. Keep the same month columns and model-line rows on Actuals, DriverCalc and Report for the direct cell references below to work.

The grid is one column per month. Row 4 holds the month-end date, so every test in the model is a date comparison, and row 5 holds a status for each month derived from two inputs. C2 is the last closed month, read straight from the actuals import, and P2 is the end of the forecast window twelve months later.

Status row (row 5, copied across the complete month grid)
C5: =IF(C4<=$C$2,"Actual",IF(C4<=$P$2,"Forecast","Outside"))

Last closed month, taken from the header row of the actuals import
C2: =EOMONTH(MAX(Actuals!$4:$4),0)

End of the forecast window
P2: =EOMONTH($C$2,12)

Grid value: actuals for closed months, driver calculation for open months
C12: =IF(C$5="Actual",Actuals!C12,IF(C$5="Forecast",DriverCalc!C12,""))

Set C2 from the latest reconciled close. Keep prefilled future headers out of the import date range used by MAX. The MAX formula assumes the import contains only closed-month date headers. Use Microsoft’s EOMONTH function for P2 so a September close ends at the following September month-end. Extend all four sheets through at least P2, including dates, inputs, formulas, validation and summary ranges. A fixed C:R grid eventually runs out of months; the import alone cannot extend it. Check that COUNTIF over the complete status row returns twelve Forecast months before issuing the pack.

Lock report formulas and closed driver inputs, then protect their sheets. Unlock only the open forecast input cells and update those permissions at each close. Add the validation below to Drivers as an entry aid, with its month headers and close controls linked to Report. Microsoft explains that copying or filling can bypass validation messages, so validation alone cannot lock periods. Run the two report checks on a separate control sheet or control rows, referencing the Report cells shown below.

Custom data validation on Drivers input cells, starting at C12
=AND(C$4>EOMONTH($C$2,0),C$4<=$P$2)

Two guards that catch what the lock is meant to prevent
Typed value in a closed month:  =IF(AND(Report!C$4<=Report!$C$2,NOT(ISFORMULA(Report!C12))),"Typed value in a closed month","")
Value beyond the window:        =IF(AND(Report!C$4>Report!$P$2,Report!C12<>""),"Value outside the window","")

A closed month carrying a typed forecast cannot be audited, and every comparison built on it inherits the same doubt.

Rolling the cash forecast with the same mechanics

The cash forecast uses the same grid, status row and lock with a different driver set, because revenue drivers say nothing about the timing of receipts. The cash sheet carries debtor days, creditor days, the GST position, PAYG withheld, the applicable super guarantee assumptions and debt service.

Each month rolls in three steps. Opening cash plus receipts less payments gives closing cash, and that balance becomes the next month's opening figure. Debtors roll as opening debtors plus invoiced revenue less collections.

Apply the same protection and close controls to the cash sheet, with reconciled bank movements in closed months and driver-driven figures in open ones.

Reconciling Actuals and Reporting the Full Year

The actuals import is the only route by which a closed month receives a value. One sheet per source works well, with the ledger export giving account codes down the rows and month-end dates across the header. A mapping table beside it matches each account code to a model line, and the month grid reads the import through SUMIFS on the code, so each close adds a reconciled actuals column before the forecast is rolled and reviewed.

The example assumes a January to December financial year. Two totals come out of the same grid and both belong in the pack. The full-year number adds the actual months and the forecast months inside the financial year. The twelve-month view adds every month inside the window. At a March close the two totals share the nine months from April to December and differ by the three months at each end, so they are one range read with two status filters.

Year-to-date actual, January to the close
=SUMIFS($C$12:$N$12,$C$5:$N$5,"Actual")

Remaining forecast inside the financial year
=SUMIFS($C$12:$N$12,$C$5:$N$5,"Forecast")

Full-year total
=SUMIFS($C$12:$N$12,$C$5:$N$5,"Actual")+SUMIFS($C$12:$N$12,$C$5:$N$5,"Forecast")

YTD actual reconciliation, using defined names for the same period and model line
=YTDActualTotal-LedgerYTDActualTotal

Forecast walk reconciliation, using defined names for the same financial year
=BudgetTotal+SUM(WalkMovements)-CurrentFullYearForecast

Here C:N covers January to December of a calendar financial year. Change that range for a different financial year. For the forward twelve-month view, sum Forecast statuses across the complete extended grid, including the next year. Both reconciliation checks should return zero. The first checks booked actuals against the ledger; the second checks that explained movements bridge budget to the current actual-plus-forecast total. Future forecasts cannot reconcile to actual ledger balances. Retain each issued forecast so actuals can also be compared with the expectation recorded before the period closed.

Keep an unmapped check, totalling every imported balance with no model line against it, and hold it at zero before the pack is drafted, because a mapping table drifts as the chart of accounts changes.

Re-Forecasting at Driver Level

Driver-level restatement is what makes a monthly cadence affordable. Four hundred ledger lines with no driver layer force a re-forecast of four hundred numbers, which becomes a data entry exercise with no owner and no reasoning attached. A driver layer reduces the restatement to the twenty or thirty assumptions that move the month.

The list depends on the business model. A services business needs billable headcount, utilisation, the rate card and churn on the recurring base. A product business needs units by channel, average selling price, freight and cost of goods per unit. Any business with staff needs headcount by month, the fully loaded cost per role and the wage growth assumption.

The arithmetic of a driver change is easy to follow. A contract forecast to begin in September slips to January, which removes four months of revenue from the financial year. At 120,000 a month in contribution, full-year EBITDA falls by 480,000, and one cell carries the change. Pulling two hires forward by a quarter behaves the same way: two roles at 9,000 a month fully loaded, three months early, adds 54,000 of cost to the year.

Every re-forecast should leave a log, one row per changed driver recording the date, the driver, the old value, the new value, the owner and the reason. Each logged change is already a line in the forecast walk.

Keeping the Budget and Building a Forecast Walk

Report two variances using the same reconciled actuals and separate saved comparison scenarios. Actual against budget measures performance against the authorised plan, which is the figure that speaks to incentives and to any covenant expressed against the plan. Actual against the forecast saved before the period closed measures forecast accuracy. Comparing actuals with a refreshed forecast that already includes those actuals would conceal the error. A business behind budget and in line with forecast carries a different problem from one behind budget and also behind forecast.

The illustrative forecast walk starts at the budget for the year, lists each driver movement since the budget was set, and finishes at the current forecast. Build it from the driver-change log, one line per movement, with a running total.

Split the walk into two blocks, one for the months that have closed and one for the months to come. The split distinguishes booked performance from revised expectations. A profit walk does not explain bank movements; use a separate cash reconciliation for that question.

Driver Ownership and the Re-Forecast Meeting

Each driver carries a name. Revenue volume sits with the sales lead, price with the commercial lead, churn with whoever owns the customer relationship, and headcount and wage growth with operations and finance. Owners submit a change note before the session stating the assumption now, the assumption last month, the reason for the difference and the evidence behind it.

The meeting is short and sits between the close and the pack. Owners bring their changes, and each change is accepted into the model or returned for evidence. A sixty to ninety minute session can be a useful target, and confirming that a driver has not changed counts as a contribution, because an unchanged assumption is a decision the model depends on.

Retain the issued pack and its assumptions as the month’s fixed snapshot.

Pitfalls That Break a Rolling Forecast

• Rebuilding the model every month. If the roll means re-creating sheets and re-linking formulas, it slips in the first busy month. The roll should be a data event driven by the import, with the status row doing the reclassification.
• Re-forecasting at ledger-account level. Four hundred lines with no owner produce a restatement nobody can explain.
• No lock on closed periods. A grid that accepts a typed value in a closed month stops being a record, and the next month's variance is measured against an edited forecast.
• The forecast becoming the new budget. This happens one line at a time, usually to remove an inconvenient variance.
• A twelve-month roll that never ties to the full-year number. The window and the financial year are different ranges, so report both totals, reconcile their actual components to the ledger and reconcile forecast changes through the walk.
• Restating everything every month. Rent, insurance and the annual audit fee change once a year, and restating them adds noise to the walk.

Related reading

Use budget and forecast models in Excel to structure the fixed plan and changing expectation. Our guide to variance analysis explains performance comparisons, while cash flow forecasting for growing SMEs covers the receipts and payments behind the cash view.