FORECAST.LINEAR function

The FORECAST.LINEAR function in Excel is used to predict a future value based on the linear regression of a dataset. It calculates a predicted value based on the relationship between an independent variable (X) and a dependent variable (Y), assuming a linear trend. This is useful for making simple linear forecasts when you have historical data and want to estimate a future value based on a consistent trend.

Syntax:

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

Arguments:

• x: The x-value (independent variable) for which you want to predict the corresponding y-value (dependent variable). This is the future data point that you want to forecast.
• known_y’s: The array or range of dependent values (Y-values) that correspond to the known_x’s. These are the historical data points for the dependent variable.
• known_x’s: The array or range of independent values (X-values) corresponding to the known_y’s. These represent the historical data points for the independent variable.

How It Works:

The FORECAST.LINEAR function uses linear regression to determine the relationship between the known_x's and known_y's. It then uses that relationship to predict the value of the dependent variable (y) at the given independent variable (x). Essentially, the function fits a straight line (linear equation) to the data points and uses that line to forecast the value of y for a new x.

The formula for a simple linear regression is:

$$y = mx + b$$

Where:

• $m$ is the slope (rate of change) of the line, calculated as the covariance of known_x's and known_y's divided by the variance of known_x's.
• $b$ is the intercept, the value of $y$ when $x = 0$.

The FORECAST.LINEAR function calculates the predicted $y$ value by plugging the input x value into the equation of the best-fit line.

Example:

Suppose you have the following data showing the sales in dollars (known_y's) for the first 5 months of the year (known_x's), and you want to forecast the sales for month 6 (x = 6):

• Month (known_x's): {1, 2, 3, 4, 5}
• Sales (known_y's): {100, 150, 200, 250, 300}

To predict the sales for month 6, use the following formula:

=FORECAST.LINEAR(6, B2:B6, A2:A6)

Where:

• 6 is the target month (the x value you want to predict).
• B2:B6 is the range of known_y’s (sales data).
• A2:A6 is the range of known_x’s (months).

The result will return the forecasted sales for month 6 based on the linear trend of the previous months.

Key Points:

• The FORECAST.LINEAR function is based on linear regression, assuming a straight-line relationship between the independent and dependent variables.
• It is ideal when there is a consistent trend in the data (i.e., the data follows a linear pattern).
• This function returns the predicted value for a new x value based on the linear trend established by the historical data.

Use Cases:

• Sales Forecasting: Predict future sales based on past sales trends.
• Trend Analysis: Forecast future values based on a linear relationship in fields like economics, finance, and operations.
• Budgeting: Predict future expenses or revenue based on historical financial data.
• Demand Forecasting: Estimate future product demand based on past sales or usage patterns.

Notes:

• The function assumes a linear trend between the independent and dependent variables. If the data exhibits non-linear trends (e.g., exponential growth or periodic fluctuations), this function may not provide accurate predictions.
• If you need more complex forecasting that accounts for seasonality or non-linear relationships, you may want to explore other forecasting methods, like FORECAST.ETS.