How to Build Dynamic Rolling Forecasts with OFFSET, EDATE, and SCAN in Microsoft Excel

Traditional corporate budgeting models rely on rigid, calendar-bound annual targets. However, dynamic market conditions, supply chain shifts, and fluctuating revenue velocity render fixed 12-month annual budgets obsolete within months. Financial planning and analysis (FP&A) teams increasingly operate on rolling forecasts—continuous 12-to-18-month projections that drop past months as actual financial data matures, automatically extending the forecast horizon forward in time.

Historically, building rolling models in Microsoft Excel required complex, brittle VBA macros or volatile formulas prone to workbook corruption. Leveraging modern Microsoft 365 dynamic array primitives—specifically EDATE, SEQUENCE, and accumulator helper functions like SCAN paired with LAMBDA—enables financial analysts to construct automated, self-healing rolling forecast models that expand and roll forward deterministically.

Architectural Overview: The Mechanics of Rolling Forecast Models

A resilient rolling forecast architecture separates raw historical actuals, budget baselines, and forward projection vectors into distinct calculation layers:

Model Component	Formulation Engine	Operational Role
Dynamic Timeline Horizon	EDATE & SEQUENCE	Generates an automated horizontal vector of monthly date boundaries starting from an anchor date.
Data Splicing Switch	IF, XLOOKUP, ISBLANK	Evaluates whether an accounting period has “Actuals” booked; switches dynamically to forecast math when missing.
Compound Accumulator	SCAN & LAMBDA	Maintains running cash balances, compound monthly growth rates, or amortised expenditures without iterative calculation circularities.
Non-Volatile Windowing	INDEX & CHOOSECOLS	Slices multi-month rolling averages (e.g. trailing 3-month run-rate) without triggering the performance penalties of volatile OFFSET.

Constructing the Dynamic Date Header Array

Rather than manually typing or dragging column dates across rows, generate a spillable timeline that automatically recalculates whenever the model anchor date updates.

Step 1: Establish Anchor Parameters

Define designated input cells on your assumptions sheet:

• Anchor_Date (e.g. cell C4): 2026-10-01 (First month of the rolling cycle).
• Horizon_Months (e.g. cell C5): 12 (Duration of the rolling window).

Step 2: Generate the Dynamic Monthly Sequence

In the timeline header cell (e.g. E8), enter the spill formula:

# Generates an array of 12 consecutive month start dates
=EDATE(Anchor_Date, SEQUENCE(1, Horizon_Months, 0, 1))

How this evaluates: SEQUENCE(1, 12, 0, 1) generates an in-memory row array {0, 1, 2, 3 ... 11}. EDATE offsets the anchor date by each integer step, spilling twelve consecutive month dates horizontally across the sheet.

Splicing Actuals with Projections Using Non-Volatile Range Lookups

A common mistake in legacy financial modelling is using OFFSET to fetch the last known actual. Because OFFSET is a volatile function, Excel must recalculate every dependent cell across the entire workbook on every single worksheet interaction, causing severe recalculation lag. Modern models leverage INDEX and XLOOKUP.

Step 1: Structure the Actuals Table

Ensure historical accounting actuals are stored in a structured table named tbl_Actuals with columns [Period] and [Revenue].

Step 2: Construct the Unified Rolling Row Formula

Enter the following formula across the date header timeline to dynamically splice booked revenue with forward run-rate projections:

=LET(
    CurrentPeriod, E8#,
    ActualValue, XLOOKUP(CurrentPeriod, tbl_Actuals[Period], tbl_Actuals[Revenue], ""),
    TrailingAverage, AVERAGE(CHOOSECOLS(tbl_Actuals[Revenue], SEQUENCE(3, 1, ROWS(tbl_Actuals[Revenue]) - 2, 1))),
    GrowthRate, 0.025,
    IF(
        ActualValue <> "",
        ActualValue,
        TrailingAverage * (1 + GrowthRate)
    )
)

When an accounting period closes and new entries are appended to tbl_Actuals, the formula detects the presence of the booked actual, automatically converting that month from a projected estimate into hard historical fact and rolling the projection further down the timeline.

Accumulating Balances and Compounding Trends with SCAN and LAMBDA

Calculating compound interest, multi-month revenue growth trajectories, or cumulative cash balance depletion requires an accumulator function. The SCAN dynamic array function eliminates the need for chained drag-down formulas that break upon column insertion.

Formulation: Calculating Cumulative Cash Burn with SCAN

Given an initial cash balance in cell C12 and a horizontal array of projected net monthly operating cashflows spilling across E15#, calculate the running cash reserves:

# Cumulative running cash balance formula
=SCAN(
    C12,
    E15#,
    LAMBDA(CurrentReserve, MonthlyCashFlow, CurrentReserve + MonthlyCashFlow)
)

Anatomy of the SCAN Execution Engine

• C12: The initial starting accumulator value (initial cash treasury).
• E15#: The array of monthly cashflow values being traversed.
• LAMBDA(CurrentReserve, MonthlyCashFlow, ...): For every monthly element, the function takes the running total from the preceding iteration and adds the current period’s cashflow, spilling the exact trajectory horizontally across all 12 periods.

Smoothing Forecast Volatility with Trailing Run-Rates

To avoid erratic forecasting spikes caused by one-off anomalous months, construct a dynamic moving average that updates automatically without hardcoded column offsets.

=LET(
    PeriodsArray, E8#,
    CurrentColIndex, COLUMN(E8#) - COLUMN(E8) + 1,
    MAP(CurrentColIndex, LAMBDA(idx,
        IF(
            idx <= 3,
            AVERAGE(TAKE(tbl_Actuals[Revenue], -3)),
            AVERAGE(CHOOSECOLS(tbl_Actuals[Revenue], SEQUENCE(3, 1, idx - 3, 1)))
        )
    ))
)

This approach combines MAP and TAKE to ensure that moving baselines always look backwards by exactly 3 cycles relative to the active column index.

Troubleshooting Dynamic Rolling Forecasts in Excel

Consult this diagnostic matrix to resolve array collisions and calculation errors:

Symptom	Underlying Root Cause	Remediation Procedure
Formula returns #SPILL! error across header or forecast rows.	Adjacent cells within the intended expansion path contain existing data, formatting artifacts, or whitespace.	Clear all cells to the right and below the originating formula cell until the dynamic array has sufficient unobstructed canvas.
Recalculation lag makes workbook unresponsive during data entry.	Overuse of volatile functions like OFFSET, INDIRECT, or TODAY() inside large array blocks.	Replace OFFSET with non-volatile alternatives (INDEX, TAKE, DROP, CHOOSECOLS). Use static anchor date input cells rather than dynamic TODAY() calls inside calculation loops.
SCAN returns #VALUE! on the third iteration.	One of the input cells in the traversed cashflow array contains text strings, spaces, or an unhandled calculation error.	Wrap underlying revenue rows in N() or IFERROR(..., 0) to guarantee purely numeric vectors enter the lambda accumulator.
Dates generated by EDATE display as raw five-digit numbers (e.g. 46318).	Excel dynamic array spill destinations inherited the “General” number format rather than Date formatting.	Select the spill range using E8# and apply the custom date format mmm-yy or yyyy-mm via Ctrl + 1.

By replacing legacy macro automation and volatile formulas with modern dynamic array architecture, FP&A teams achieve automated, resilient rolling forecasts that adapt instantaneously to changing commercial realities.