The FORECAST formula with example in Excel

The FORECAST function in Excel is used to predict a future value based on existing values. It uses linear regression to predict the future value of a dependent variable (y) based on the relationship between two sets of data: the known values of the independent variable (x) and the known values of the dependent variable (y).

The 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.
• known_y's: The dependent array or range of data.
• known_x's: The independent array or range of data.

Example

Suppose you have a dataset of sales over a period of months, and you want to forecast future sales based on this historical data.

Month	Sales
1	100
2	150
3	200
4	250
5	300

You want to forecast the sales for month 6. Here's how you can do it:

1. Enter the data into an Excel sheet:

   • Column A (Month): 1, 2, 3, 4, 5
   • Column B (Sales): 100, 150, 200, 250, 300

2. Use the FORECAST function to predict the sales for month 6. In cell C1, enter the following formula:

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

3. Press Enter.

The formula will output the predicted sales for month 6 based on the linear trend of the previous months.

Explanation of the Formula

• 6 is the x value for which you want to predict a y value.
• B2:B6 are the known y values (Sales).
• A2:A6 are the known x values (Month).

Result

The formula calculates the predicted sales for month 6 using the linear regression equation derived from the known data points. Based on the given data, the prediction might be around 350.

Here’s a step-by-step explanation of what Excel does:

1. It determines the slope (m) and intercept (b) of the best-fit line using the least squares method.
2. It uses the linear equation y = mx + b to calculate the forecasted value.

By using the FORECAST function, you can easily predict future values based on historical data trends.