How to use the Excel TREND function to forecast months of future revenue

Businesses looking to predict future earnings over upcoming months do not necessarily need complex statistical software or manual regression formulas. With modern dynamic arrays in Excel for Microsoft 365 and Excel 2021 or later, users can enter the Excel TREND function into a single cell to project months of future revenue.

According to MakeUseOf writer Ada, anyone who already has revenue figures covering a sufficient, continuous period has all the data required to use the function.

The TREND function relies on linear regression to determine how an outcome or metric relates to the factors influencing it. In plain terms, it fits a straight line to existing data using the equation y = mx + b.

In that formula, y is the dependent variable (such as revenue, prices, scores, or expenses), and x is the independent variable (such as time, advertising spend, study hours, or distance traveled). Meanwhile, m represents the slope, and b represents the intercept.

After establishing that relationship, Excel can estimate new dependent variable values - like future revenue - from new independent variable values, such as upcoming months. For instance, if a worksheet holds 24 months of sales data with the timeline in A2:A25 and corresponding revenue in B2:B25, a user can forecast the next twelve months listed in A26:A37 by entering this formula in cell B26:

=TREND(B2:B25,A2:A25,A26:A37)

In Excel for Microsoft 365 or Excel 2021 or later, pressing Enter makes the formula automatically spill twelve forecast figures into the cells below, as long as those cells are empty. On older Excel versions without dynamic arrays, users may need to select all twelve output cells first, enter the formula, and confirm it with Ctrl+Shift+Enter.

The function’s arguments follow a specific structure:

Argument	Description
Known_y's	The required first argument containing existing revenue figures.
Known_x's	The optional second argument holding corresponding timeline values, such as months. If omitted, Excel assumes sequential periods using the array {1, 2, 3, …}.
New_x's	The optional third argument containing independent variable values for which Excel should predict new revenue (such as A26:A37). If omitted, Excel calculates the trend using the known_x's values.
Const	The final argument accepting TRUE or FALSE to decide whether Excel calculates the intercept normally or forces it to zero. Leaving it out lets Excel calculate the intercept from existing data.

While getting TREND to work is straightforward, Ada notes that several easy-to-miss mistakes can throw results off:

• Swapping the argument order: TREND requires known_y’s (historical revenue) first and known_x’s (the timeline) second. Reversing them makes Excel treat the timeline as the dependent variable and revenue as the independent variable, producing unrealistic results. Users familiar with FORECAST.LINEAR can easily make this mistake because that function uses a different argument order.
• Incomplete or inconsistent data: Because TREND does not automatically skip missing observations, historical revenue ranges must be checked and cleaned for blanks, errors, and missing periods before forecasting. Leaving messy data uncleaned could return common Excel errors or base the forecast on inaccurate sales history.
• Mismatched timeline sequences: The timeline needs to reflect the actual sequence of months. Treating sales from January, February, March, and May as four consecutive months ignores the missing April data and distorts the forecast (unless the business makes no sales in April). Using actual Excel dates or consistent period numbers ensures the function accounts for proper intervals.
• Non-contiguous cell ranges: Passing individual cells like B2, B4, and B12 instead of a continuous range like B2:B12 can cause issues when intended as a single argument. Historical revenue, timelines, and future periods should use continuous ranges.
• Assuming numbers are automatically reliable: Users should plot historical sales and projected figures on a chart and compare them against a linear trendline. If the chart's displayed equation yields slightly different values, check the decimal precision - Excel computes TREND with its underlying precision, whereas chart trendline equations may round the displayed slope and intercept.

The TREND function works best when historical revenue follows a reasonably consistent upward or downward path. If sales spike during festive periods, dip in slow seasons, or fluctuate monthly, a straight line may miss those patterns and overlook key changes in a typical sales cycle.

When revenue growth accelerates or slows over time, users can explore polynomial regression to model curved relationships by supplying multiple predictor columns for known_x's, such as time and its powers {(x), (x^2), and (x^3)}. For seasonal fluctuations that a basic linear trend will not capture, Excel's Forecast Sheet - one of the program's lesser-known AI features - or other time-series forecasting methods may be more suitable.

Unusual revenue months also require attention. A one-off bulk order or an exceptional downturn can pull the trendline away from typical performance, particularly when historical data is limited. Users should investigate such outliers before letting them influence a forecast, as they may reflect rare circumstances rather than a repeatable pattern.

Excel’s TREND function offers a time-saving, practical starting point for revenue planning. While it cannot factor in a new competitor, an upcoming price change, or shifting customer demand, it provides a baseline prediction to build upon.

By entering arguments in the proper order, keeping data ranges consistent, confirming that sales follow a linear pattern, and validating outputs against chart trendlines, users can generate a useful revenue forecast from existing sales data.

This article was written by AI from reporting by makeuseof.com.