Linear Forecasting with TREND and FORECAST Functions

Moving averages are reactive — they follow the data. Linear forecasting is predictive — it finds the underlying direction of the data and projects it forward as a straight line. When your revenue has a consistent upward or downward trajectory, linear methods will outperform moving averages because they capture the growth momentum, not just recent averages.

How Linear Forecasting Works

Linear forecasting fits a straight line through your historical data points — the same line you'd draw by eye through a scatter plot — and extends it into future periods. This line is defined by two values:

• Slope — how much the metric changes per period. A slope of 15,000 means revenue grows by $15,000 each month on average;

• Intercept — the starting value of the line at period zero. You rarely use this directly, but Excel calculates it internally for every linear forecast.

Excel gives you three ways to apply this: the FORECAST.LINEAR function for single future points, the TREND function for ranges of future points at once, and the Forecast Sheet tool covered in Lesson 2.4.

Using FORECAST.LINEAR

FORECAST.LINEAR predicts a single future value based on the linear relationship between your known x-values (time periods) and y-values (revenue).

=FORECAST.LINEAR(x, known_ys, known_xs)

x          → the future period you want to predict (a number, not a date)
known_ys   → your historical revenue values
known_xs   → your historical period numbers (1, 2, 3 ... 36)

Practical example — predicting revenue for month 37 (January 2025):

=FORECAST.LINEAR(37,
    Forecast_Input_Table[Total_Revenue],
    Forecast_Input_Table[Month_Num])

Where Month_Num is a helper column in your Forecast_Input_Table containing 1 through 36. If you haven't added it yet:

Month_Num column:   Row 2: =1    Row 3: =2    or simply =ROW()-ROW(Forecast_Input_Table[#Headers])

For the full 12-month forecast (months 37 through 48):

Jan 2025 (month 37):  =FORECAST.LINEAR(37, Forecast_Input_Table[Total_Revenue], Forecast_Input_Table[Month_Num])
Feb 2025 (month 38):  =FORECAST.LINEAR(38, Forecast_Input_Table[Total_Revenue], Forecast_Input_Table[Month_Num])

Or more cleanly, if your forecast month numbers are in column E:

=FORECAST.LINEAR([@Month_Num], Forecast_Input_Table[Total_Revenue], Forecast_Input_Table[Month_Num])

Using TREND for a Range of Forecasts

TREND is more powerful than FORECAST.LINEAR because it calculates an entire range of future values in one formula. It is an array formula — it fills multiple cells at once.

=TREND(known_ys, known_xs, new_xs)

known_ys   → historical revenue (36 values)
known_xs   → historical period numbers 1–36
new_xs     → future period numbers 37–48

To enter it for 12 future months at once:

1. Select 12 blank cells in your forecast column (e.g. F38:F49)
2. Type =TREND(Forecast_Input_Table[Total_Revenue], Forecast_Input_Table[Month_Num], E38:E49)
3. Press Ctrl+Shift+Enter (not just Enter) — this confirms it as an array formula and fills all 12 cells simultaneously

In Excel 365 you can press Enter normally as dynamic arrays handle it automatically.

Calculating the Slope and Intercept Directly

Sometimes you need the slope and intercept as standalone values — for example, to document your forecast assumptions or build a sensitivity table around the growth rate.

Slope (revenue change per month):    =SLOPE(Forecast_Input_Table[Total_Revenue], Forecast_Input_Table[Month_Num])
Intercept:                           =INTERCEPT(Forecast_Input_Table[Total_Revenue], Forecast_Input_Table[Month_Num])

Manual forecast using slope and intercept:
=Intercept + (Slope * Month_Num)

Which is identical to FORECAST.LINEAR but written explicitly — useful for
assumption documentation and scenario modeling in Section 3.

Add both values to your KPI_Calculations sheet. The slope is particularly meaningful — it tells you the average monthly revenue increase implied by your historical trend.

Measuring Fit with R-Squared

R-squared tells you how well the linear model fits your data. It ranges from 0 to 1 — a value of 1 means the data falls perfectly on a straight line, a value of 0 means the linear model explains nothing.

=RSQ(Forecast_Input_Table[Total_Revenue], Forecast_Input_Table[Month_Num])

Add the R-squared value to your KPI_Calculations sheet next to the slope and intercept. It tells you before you even plot anything how much to trust the linear forecast.

Comparing Linear vs Moving Average Forecasts

By now your forecast extension table should have three forecast columns side by side. Add a fourth — the linear forecast — so you can compare all methods visually:

The linear forecast will diverge from the moving average forecast over time. Which one is higher tells you whether recent months have been above or below the long-term trend — itself a useful business insight.

Task 1: Build the Linear Forecast

Goal: Add a FORECAST.LINEAR projection to your forecast extension table and compare it to the moving average.

1. In your Forecast_2025 area, add a helper column Month_Num containing 37 through 48 for the 12 forecast months.
2. Add a Linear_Forecast column. In the first row enter =FORECAST.LINEAR([@Month_Num], Forecast_Input_Table[Total_Revenue], Forecast_Input_Table[Month_Num]) and autofill down all 12 rows.
3. In your KPI_Calculations sheet, add three new rows: Slope (=SLOPE), Intercept (=INTERCEPT), and R-Squared (=RSQ), all referencing the Forecast_Input_Table columns.
4. Add the Linear_Forecast series to your existing forecast chart from Lesson 2.2 as a third dashed line in a distinct color.
5. Observe where the linear forecast is higher or lower than the MA3_Forecast — note the difference in a comment cell next to the chart.

Task 2: Use TREND as an Array Formula

Goal: Reproduce the linear forecast using TREND and confirm it matches FORECAST.LINEAR exactly.

1. In a blank column next to your Linear_Forecast, select 12 empty cells.
2. Type =TREND(Forecast_Input_Table[Total_Revenue], Forecast_Input_Table[Month_Num], E38:E49) where E38:E49 contains your forecast month numbers 37–48.
3. Press Ctrl+Shift+Enter to confirm as an array formula (or just Enter in Excel 365).
4. In a validation column, subtract the TREND results from the FORECAST.LINEAR results — every cell should return zero, confirming the two methods are identical.
5. Delete the TREND column after validation — keep only the FORECAST.LINEAR column in your final forecast table.