Ravindra BagaleCourses & study guides मराठी Track your progress

31. Forecasting in Excel

31.2 FORECAST.LINEAR and TREND: Straight-Line Forecasts

By Last updated:

Example first. Zoya has monthly orders for a Nashik dark store from October 2025 to September 2026: 8,200 in the first month, 10,350 in the last, with steady growth and no clear repeating pattern. Her manager wants October to December 2026. With the months numbered 1 to 12, =FORECAST.LINEAR(13,C2:C13,B2:B13) returns 10659.85, about 10,660 orders for October. This lesson covers FORECAST.LINEAR for one value, TREND for many values at once, and how to tell when a straight line is the wrong tool.

FORECAST.LINEAR and TREND — in 30 seconds

What: FORECAST.LINEAR(x, known_y's, known_x's) predicts y for a new x on the least-squares straight line. TREND returns the same line for many x values at once, and without new x values it returns the fitted line for the existing data.

Why: For steady growth with no repeating pattern, a straight line is simple, explainable and hard to beat. It is also the baseline every fancier forecast must beat.

Tiny example: Months 1 to 12 in B2:B13 and orders in C2:C13. =FORECAST.LINEAR(13,C2:C13,B2:B13) = 10659.85 and =TREND(C2:C13,B2:B13,{13;14;15}) spills October, November and December.

First look at the data (before → after):

FORECAST.LINEAR and TREND: before and after tables, fictional Blinkit Maharashtra data

Expected result — Forecast

Month t Forecast
Oct 2026 13 10659.85
Nov 2026 14 10858.80
Dec 2026 15 11057.75
Before table and formula as text

Before — Monthly orders (part)

Month t Orders
Oct 2025 1 8200
Nov 2025 2 8450
Dec 2025 3 8900
… … …
Sep 2026 12 10350

Formula / click path

=FORECAST.LINEAR(13,C2:C13,B2:B13)
=TREND(C2:C13,B2:B13,{13;14;15})
=SLOPE(C2:C13,B2:B13)   =RSQ(C2:C13,B2:B13)

Do it step by step:

  1. Put the months in A2:A13, the numbers 1 to 12 in B2:B13 (or =SEQUENCE(12) in B2), and the orders in C2:C13.
  2. In E2 type =FORECAST.LINEAR(13,C2:C13,B2:B13). Result 10659.85 for October 2026.
  3. In F2 type =TREND(C2:C13,B2:B13,{13;14;15}). Three forecasts spill: October, November, December.
  4. In G2 type =SLOPE(C2:C13,B2:B13): growth of 198.95 orders per month.
  5. In G3 type =RSQ(C2:C13,B2:B13): 0.968, so the straight line fits the history well.
  6. Chart actuals and forecasts together and say it as a range, for example "about 10,660 orders in October".

The data: monthly orders, Nashik dark store (fictional)

Month Oct 2025 Nov 2025 Dec 2025 Jan 2026 Feb 2026 Mar 2026
Orders 8200 8450 8900 8700 9100 9350
Month Apr 2026 May 2026 Jun 2026 Jul 2026 Aug 2026 Sep 2026
Orders 9300 9800 9750 10100 10400 10350

FORECAST.LINEAR, step by step. The line is Orders = 8073.48 + 198.95 × t. For t = 13: 8073.48 + 198.95 × 13 = 10659.85. Check it with =INTERCEPT(C2:C13,B2:B13)+SLOPE(C2:C13,B2:B13)*13. Microsoft's syntax: FORECAST.LINEAR(x, known_y's, known_x's). The y range comes before the x range. The older FORECAST function has the same syntax and result and is kept for compatibility; use FORECAST.LINEAR in new work.

TREND for several values, and for the fitted line

Formula Returns
=TREND(C2:C13,B2:B13,{13;14;15}) 10659.85, 10858.80, 11057.75 (a vertical spill; use {13,14,15} for a horizontal one)
=TREND(C2:C13,B2:B13) The 12 fitted values for the history, from 8272.44 to 10460.90: chart them as a trend line
=TREND(C2:C13,B2:B13,SEQUENCE(3,1,13)) The same three forecasts, with the new t values made by SEQUENCE

Month numbers or real dates as x? You can use the dates themselves: =FORECAST.LINEAR(DATE(2026,10,1),C2:C13,A2:A13) returns 10662.70, slightly different from 10659.85, because months have 28 to 31 days and Excel measures x in days. For monthly data, month numbers (1, 2, 3…) give a cleaner line. For daily or weekly data with no gaps, dates work fine.

How good is the line? =RSQ(C2:C13,B2:B13) = 0.9681: the straight line explains about 97% of the month-to-month variation. =STEYX(C2:C13,B2:B13) = 136.57 is the typical miss in orders, which is a fair "±" for the forecast.

When a straight line is the wrong tool. Try it on the daily Pune data from lesson 31.1: =FORECAST.LINEAR(57,B2:B57,SEQUENCE(56)) predicts 491.0 for Monday 28 September and 499.7 for Saturday 3 October, almost the same number, because a straight line cannot know that Saturdays are busy. Real Saturdays in this data are about 59 orders above average. For data with a repeating pattern use FORECAST.ETS (lesson 31.3) or add weekday offsets (lesson 31.6).

Exact values on this page were computed with Excel's documented formulas and re-checked in LibreOffice Calc 25.2 with the same formulas.

Microsoft sources (checked 7 October 2026): FORECAST and FORECAST.LINEAR functions; TREND function.

Practice task (try it before you open the answer):

  1. Forecast March 2027 (t = 18) with FORECAST.LINEAR.
  2. What does =TREND(C2:C13,B2:B13,{16;17}) return?
  3. The actual October 2026 turns out to be 10,520. By how much did the forecast miss, in orders and in percent?
Answer
  1. =FORECAST.LINEAR(18,C2:C13,B2:B13) = 11654.60. This is 6 months beyond the data, so treat it with care.
  2. 11256.70 and 11455.65 (January and February 2027).
  3. 10,520 − 10659.85 = -139.85 orders; 1.33% of the actual (=ABS(10520-E2)/10520).

Ravindra Bagale's Tip

Always keep the straight-line forecast as a baseline, even when you use ETS. If a fancy model cannot beat FORECAST.LINEAR on held-back data, use the simple one and explain it in one sentence.

Loading practice tasks...

  1. Month 11 forecast (Easy): Forecast revenue for month 11, rounded to 1 decimal.
  2. Month 12 forecast (Easy): Forecast for month 12, rounded to 1 decimal.
  3. Monthly growth (Easy): Slope of B2:B11 on A2:A11, rounded to 3 decimals.
  4. Starting level (Medium): Intercept of B2:B11 on A2:A11, rounded to 2 decimals.
  5. Fit quality (Medium): R squared, rounded to 4 decimals.
  6. Year-end forecast (Medium): FORECAST for 12, rounded to 1 decimal.
  7. Fitted month 10 (Medium): Forecast for A11, rounded to 1 decimal.
  8. Next 3 months total (Hard): Sum of forecasts for months 11, 12, 13, rounded to 1 decimal.
  9. When does it pass $130k? (Hard): (130 − intercept) ÷ slope, rounded up to a whole month.
  10. Growth % per month (Hard): Slope ÷ average of B2:B11, rounded to 4 decimals.

See all practice tasks in the Task Hub →