Excel FORECAST Functions: How to Predict a Future Value in Excel

by

The Excel forecast function can predict a variable’s future value based on existing data. This function is often used to predict sales, trends, or other values that are likely to change in the future. Let’s take a look at how you can use the forecast function in Excel.

FORECAST.LINEAR Function

The forecast linear function predicts the future value of sales along a linear trend. Here are the steps to forecast sales using the linear trend:

  1. Include your sales data and dates in an excel sheet.
  2. Enter the formula =FORECAST.LINEAR(x, known_ys, known_xs).
  3. In the formula, x is the future date for which you want to predict the sales. known_ys is the sales data till the date for which you have data. known_xs is the corresponding date for the sales data.
  4. Make the sales data and the dates till the known data absolute. This ensures that they don’t overlap when you apply the formula to the rest of the dates. In our case, the formula will =FORECAST.LINEAR(A18,$B$2:$B$17,$A$2:$A$17)
  5. Press Enter to get the result. Apply the formula to the rest of the dates, and you will get the forecasted sales for those dates.

FORECAST.ETS Function

With the FORECAST.ETS Function, you can accurately predict future values from existing data with the help of AAA’s version of the Exponential Smoothing (ETS) algorithm. This formula uses the same inputs as the linear trend, but it requires some extra argument to specify the type of exponential smoothing.

1. After adding the data to the sheet, enter the formula: FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

2. Here, target_date is the date you want to forecast for. Values are the sales data before that date. Timeline is the corresponding date for the values. Seasonality is whether or not there is any seasonality in your data (use 1 to detect automatically). We will ignore the rest for simplicity.

3. Make sure to make the values and timeline absolute to avoid overlaps. Our formula will = =FORECAST.ETS(A18,$B$24:$B$39,$A$24:$A$39,1)

4. Press Enter to get your forecasted result for that date.

5. Apply the formula to all the remaining dates, and you will get the forecasted values for each of them.

Conclusion

With these two FORECAST functions, you can easily predict future values from existing data in Excel. You can use these formulas to forecast sales, trends, or any other variable that is likely to change over time. Give them a try today!

Related Posts: