Excel: 7 Functions for a Self-Updating Forecast

A financial forecast that applies the same formula to all lines of an income statement produces clean figures, but rarely credible ones. Sales follow a growth rate, purchases depend on revenue, certain expenses are fixed in the budget, and an exceptional month can skew an entire trend. This article details seven Excel functions — from FORECAST.LINEAR to LET — to build a line-by-line forecast that recalculates automatically as soon as a new month of actuals arrives. It is aimed at financial controllers and managers of financial models working under Excel for Microsoft 365.

Why a single forecast formula is never enough

In a real income statement, each line follows different logic:

• online sales grow at a known monthly rate;
• wholesale sales follow a historical linear trend;
• product purchases and inbound freight vary in proportion to revenue or purchases;
• salaries and rents are fixed by the budget;
• some items like software subscriptions or insurance need smoothing to avoid being distorted by a one-time spike.

Applying uniform linear extrapolation to all these lines amounts to ignoring what you already know about the business. The right approach is to choose the calculation method account by account, while keeping formulas that refresh automatically as actuals replace forecasts.

Preparing a maintainable forecast workbook

Before entering the first formula, it's worth structuring the workbook so the work remains readable and auditable:

• add three working columns: Method, Rate/Ratio, and Notes, which document the logic applied to each line;
• group these columns via the Data > Group tab to easily hide them — management doesn't need to see intermediate calculations;
• maintain a status line (Actual / Forecast) across the full width of the table: this is what will dynamically drive the filters used in the formulas.

This organization allows almost all the formulas that follow to continue working without modification when July changes from "Forecast" to "Actual" status.

Modeling growth and trend with FORECAST.LINEAR

Monthly compounded growth

For an account that grows by a fixed percentage each month — typically online sales at +3% monthly — simple multiplication is enough:

1=$G4*(1+$L$4)

Where $G4 corresponds to actual sales for the previous month and $L$4 is the growth rate entered in the working column, locked so the formula can be copied to the right without shifting.

Linear trend adjusted to actual months only

For an account that follows its historical trajectory — wholesale sales, service and installation revenue — FORECAST.LINEAR combined with FILTER lets you account only for months marked "Actual", never reintegrating a forecast into trend calculation:

1=FORECAST.LINEAR(N$4,
2    FILTER($B10:$M10,$B$3:$M$3="Actual"),
3    FILTER($B$4:$M$4,$B$3:$M$3="Actual"))

The first FILTER returns the actual values for the account (known Y values), the second returns the corresponding month numbers (known X values). Since both FILTER statements reference the same status row, as soon as one month shifts from "Actual" to something else (or vice versa), both arrays recalculate in sync — the formula never needs to be touched.

Building forecasts by ratio: sales, purchases, and margin

Some items follow neither fixed growth nor their own trend, but a historical ratio to another item. Shipping fees, for example, are calculated as a percentage of total actual sales:

1=SUM(FILTER($B9:$M9,$B$3:$M$3="Actual"))
2 /SUM(FILTER($B10:$M10+$B11:$M11,$B$3:$M$3="Actual"))

Once calculated and converted to a percentage, this historical ratio is then applied to forecast sales to get the projected amount:

1=($H10+$H11)*$M$9

The same principle applies to inbound freight and import duties, calculated this time as a ratio to purchases rather than sales. Finer variants exist depending on the account: merchant fees also include service revenue in the calculation base, while marketing only relates to online sales, since the company only advertises its online store. In both cases, simply duplicate an existing formula and adjust the scope of sales included in the FILTER.

Pulling budget with locked XLOOKUP

Some items — salaries, pension contributions, rents, depreciation — are not forecast: they are taken directly from the budget. XLOOKUP works fine, provided you properly manage mixed references so the formula behaves correctly both when copied down (changing accounts) and across (changing months):

1=XLOOKUP($A4,$A$2:$A$50,G$2:G$50)

• $A4 references the account number for the current row: the column is locked, the row remains relative to adapt when copying down;
• $A$2:$A$50 references the list of budgeted accounts: completely locked, it never moves;
• G$2:G$50 references the column for July in the budget: only the row is locked, allowing the column letter to automatically slide to August, September, etc. when pulling the formula to the right.

If your source range is converted to a structured Excel table (Ctrl+T), a locked structured reference to the account column allows the same behavior with a more readable formula for someone auditing the workbook.

Smoothing volatile accounts with LET, TAKE, and outlier exclusion

Three-month moving average

For an account like software subscriptions, where some contracts start and others end, a rolling average over the last three actual months smooths noise without freezing an obsolete amount. LET makes the formula readable by naming each intermediate step, and TAKE isolates the last three columns of the filtered array:

1=LET(
2    actualValues, FILTER($B12:$M12,$B$3:$M$3="Actual"),
3    AVERAGE(TAKE(actualValues,,-3))
4)

With each new monthly close, FILTER retrieves one additional actual month and TAKE automatically slides the calculation window to the most recent three months.

Excluding an isolated outlier value

An account like insurance may have an isolated spike (an annual premium paid in January, for example) that would skew a simple average. By multiplying two logical tests inside FILTER, you exclude the problematic month without affecting the rest of the series:

1=AVERAGE(FILTER($B14:$M14,($B$3:$M$3="Actual")*($B$4:$M$4<>1)))

The same pattern applies to travel and entertainment expenses (excluding month 5) and repairs and maintenance (excluding month 6): only the excluded value changes in the second logical test.

For a quarterly-cycle item like professional fees, the logic is different: you simply reuse the amount from the same month of the previous quarter, pulling the formula across three columns at once to reproduce the spike pattern observed each quarter.

Strengthening and simplifying formulas with defined names

Almost all formulas above repeat the same check: $B$3:$M$3="Actual". Rather than retyping it each time, define it once as a name via the Formulas > Name Manager tab:

1Status = ($Sheet1!$B$3:$M$3="Actual")

Once this name is created, each formula can replace the complete logical test with Status, which significantly shortens entry and reduces typo risk on such a frequently used range. A targeted Find/Replace on the exact string allows propagating the change across all existing formulas in one operation.

Implementation: a selection engine with LET and SWITCH

Once methods are validated account by account, it's possible to go further by centralizing logic in a single formula that reads the Method column and automatically applies the correct calculation. This avoids maintaining nine different formula families across the sheet.

Requirements: Excel for Microsoft 365 (dynamic array functions FILTER, LET, TAKE, and native SWITCH). Minimal access required: write access to the workbook only — protect the worksheet (Review > Protect Sheet) once the model is finalized to prevent accidental formula modification by end users.

1=LET(
2    method, $L4,
3    rateRatio, $M$4,
4    currentMonth, N$4,
5    statusActual, Status,
6    onlineSales, FILTER($B10:$M10,statusActual),
7    wholesaleSales, FILTER($B11:$M11,statusActual),
8    actualMonths, FILTER($B$4:$M$4,statusActual),
9    SWITCH(method,
10        "Growth", $G4*(1+rateRatio),
11        "Linear", FORECAST.LINEAR(currentMonth,onlineSales,actualMonths),
12        "SalesRatio", (onlineSales+wholesaleSales)*rateRatio,
13        "Budget", XLOOKUP($A4,$A$2:$A$50,G$2:G$50),
14        "Average3M", AVERAGE(TAKE(onlineSales,,-3)),
15        "AverageNoOutlier", AVERAGE(FILTER($B10:$M10,statusActual*(actualMonths<>1))),
16        "Unknown method"
17    )
18)

Verification: after deploying the formula across all rows, compare its results to those from the individual formulas built earlier using the audit tool (Ctrl+\`` to toggle formula display, or Formulas > Show Formulas). Both approaches should produce identical values; if they differ, the issue almost always comes from a misspelled method name in the **Method** column, which SWITCH` then surfaces as "Unknown method" rather than a silent wrong number.

Troubleshooting: common errors when building the forecast

• #CALC! with FILTER: no element matches the criterion, usually because the status cell contains extra whitespace or different case than "Actual". Add a third if_empty argument to FILTER to prevent the error from propagating.
• #VALUE! with FORECAST.LINEAR: the known_ys and known_xs arrays returned by the two FILTER statements don't have the same size, often because the two source ranges don't cover exactly the same columns. Check that the values row and months row have the same width.
• #N/A with XLOOKUP: the account number in the current row doesn't exist in the budget table (account renamed or added afterward). Add the if_not_found argument to XLOOKUP rather than letting the error bubble up into the total.
• #NAME? after creating a defined name: the name already exists with a different scope (sheet vs. workbook). Check the Scope field in the Name Manager.
• Inconsistent results after Find/Replace: a formula used the searched string in a different context. Always test on a workbook copy before a massive replacement.

Going further: FORECAST.ETS and the automatic forecast sheet

If your history spans several years with a recurring seasonal pattern, Excel offers a complementary approach: the Forecast Sheet tool and the FORECAST.ETS function, which automatically generate a projection with confidence intervals and fit quality statistics. This approach works better for long, seasonal series than for accounts driven by explicit business rules like those covered here.

Key takeaways

• There is no universal forecast formula: each account deserves a method aligned with its actual behavior (growth, trend, ratio, budget, or smoothed average).
• FILTER coupled with an Actual/Forecast status row is key to having all formulas recalculate automatically without manual intervention at each close.
• LET and TAKE make rolling average calculations readable that would be incomprehensible in classic nested formula form.
• Naming repetitive logical tests via the Name Manager speeds up entry, but degrades auditability — reserve this for workbooks you maintain alone.
• For long, seasonal history, prefer FORECAST.ETS and the Forecast Sheet over account-by-account manual rules.

If your monthly reporting still relies on copy-pasting the old forecast followed by manual adjustments, the next close is the right time to migrate at least one tab to this FILTER- and status-line-based structure: reliability gains are measurable by the second refresh month.