FORECAST

Fits a straight line to data using linear regression and returns a point on that line.

Syntax:

FORECAST(new_xvalue, yvalues, xvalues)

yvalues and xvalues are single row or column ranges specifying points in a set of data. yvalues and xvalues must be the same size.

FORECAST fits a straight line through these data points, using the linear regression method.

FORECAST returns the y value on the straight line found, corresponding to the x value in new_xvalue. It is equivalent to TREND(yvalues, xvalues, new_xvalue).

Example:

FORECAST(6, B2:B6, A2:A6)

where the x values in A2:A6 are 1, 2, 3, 4, 5 and the y values in B2:B6 are 2, 4, 7, 8, 11 returns 13.

TREND(B2:B6, A2:A6, 6)

with the same x values and y values, also returns 13.

Application:

Predicting Quarterly Sales

Imagine you are a sales manager for a company that sells widgets. You have the quarterly sales data for the last three years (2023, 2024, and the first half of 2025) and you want to predict the sales for the last two quarters of 2025.

The Data:

Here's a table showing the historical quarterly sales:

	Quarter	Year	Quarter Number (x-value)	Currency (y-value)
	A	B	C	D
1	Q1	2023	1	$150,000.00
2	Q2	2023	2	$165,000.00
3	Q3	2023	3	$170,000.00
4	Q4	2023	4	$185,000.00
5	Q1	2023	1	$190,000.00
6	Q2	2024	2	$210,000.00
7	Q3	2024	3	$225,000.00
8	Q4	2024	4	$230,000.00
9	Q1	2025	1	$245,000.00
10	Q2	2025	2	$255,000.00

The Goal:

We want to predict the sales for:

Quarter 11 (Q3 2025)
Quarter 12 (Q4 2025)

The Formula:

The basic syntax for the FORECAST function is:

FORECAST(x, known_y's, known_x's)

x: The data point for which you want to predict a value. In our case, this will be 11 for Q3 2025 and 12 for Q4 2025.
known_y's: The range of your historical sales data (the y-values). In the table above, this would be the range of the "Sales" column.
known_x's: The range of your historical quarter numbers (the x-values). This would be the range of the "Quarter Number" column.

Applying the Formula:

Quarter Number (x-values) are in cells C1:C10
Sales (y-values) are in cells D1:D10

1. Predicting Q3 2025 Sales (Quarter 11):

The formula would be: FORECAST(11, D1:D10, C1:C10)

Result: The function would return a value of approximately $225,868.

2. Predicting Q4 2025 Sales (Quarter 12):

The formula would be: FORECAST(12, D1:D10, C1:C10)

Result: The function would return a value of approximately $228,554.

Interpretation:

The FORECAST function uses the linear trend from your past sales data to project a likely outcome for the future. The results of $225,868 for Q3 and $228,554 for Q4 are based on the assumption that the historical sales trend will continue. This provides you with a data-driven estimate to help with budget planning, resource allocation, and setting sales targets for the end of the year.

Result for FORECAST(11, D1:D10, C1:C10):

$225,867.77

Result for FORECAST(12, D1:D10, C1:C10):

$228,553.72