Excel FORECAST and related functions with formula examples

This tutorial explains how to use Excel's forecasting functions, including FORECAST, FORECAST.LINEAR, and the exponential smoothing functions (FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY, FORECAST.ETS.STAT), with practical examples.

Microsoft Excel offers several functions for creating linear and exponential smoothing forecasts using historical data (sales, budgets, etc.). This tutorial focuses on the key forecasting functions, briefly explaining others for context.

Excel Forecasting Functions

Six forecasting functions are available in recent Excel versions:

Linear Forecasts:

• FORECAST: Predicts future values using linear regression (backward compatible with older Excel versions).
• FORECAST.LINEAR: Identical to FORECAST; part of the newer forecasting function suite (Excel 2016 and later).

Exponential Smoothing Forecasts (Excel 2016, 2019, and Office 365):

• FORECAST.ETS: Predicts future values using the exponential smoothing algorithm.
• FORECAST.ETS.CONFINT: Calculates the confidence interval for FORECAST.ETS predictions.
• FORECAST.ETS.SEASONALITY: Determines the length of seasonal patterns in the data.
• FORECAST.ETS.STAT: Returns statistical values relevant to time series forecasting.

FORECAST and FORECAST.LINEAR Functions

Both functions use linear regression (y = a bx) to predict future values. They calculate the intercept (a) and slope (b) using the sample means of known x and y values. FORECAST.LINEAR is the preferred function in newer Excel versions, while FORECAST remains for backward compatibility.

Error Handling: Errors like #N/A!, #VALUE!, and #DIV/0! may occur if input ranges are mismatched, inputs are non-numeric, or the variance of known x-values is zero.

FORECAST Function Example:

To predict website traffic for the next 7 days based on 3 weeks of data (y-values in B2:B22, x-values (dates) in A2:A22):

• Excel 2000 - 2019: =FORECAST(A23, $B$2:$B$22, $A$2:$A$22)
• Excel 2016 and 2019: =FORECAST.LINEAR(A23, $B$2:$B$22, $A$2:$A$22)

(A23 represents the new x-value for prediction). Absolute cell references are used to prevent range changes when copying the formula.

FORECAST.ETS Function

FORECAST.ETS performs exponential smoothing forecasts using the AAA version of the Exponential Triple Smoothing (ETS) algorithm. This algorithm smooths out data fluctuations by identifying seasonality and confidence intervals.

FORECAST.ETS Arguments:

• target_date: The date/time or number for which to forecast.
• values: Historical data.
• timeline: Dates/times or numeric data with a constant step.
• seasonality (optional): Length of the seasonal pattern (1 or omitted for auto-detection, 0 for no seasonality).
• data_completion (optional): How to handle missing data (1 for average of neighbors, 0 for zero).
• aggregation (optional): How to aggregate multiple values with the same timestamp (1 for AVERAGE, others available).

FORECAST.ETS Considerations:

1. Requires a timeline with a regular interval.
2. Suitable for non-linear data with recurring patterns.
3. Falls back to linear forecasting if no pattern is detected.
4. Handles incomplete datasets (up to 30% missing data).
5. Allows duplicate timestamps; values are aggregated.

FORECAST.ETS Function Example:

Using the same data as before: =FORECAST.ETS(A23, $B$2:$B$22, $A$2:$A$22)

FORECAST.ETS.CONFINT Function

Calculates the confidence interval for FORECAST.ETS predictions. A smaller interval indicates higher prediction confidence. The optional confidence_level argument (0-1) sets the confidence level (defaults to 95%).

FORECAST.ETS.CONFINT Example:

=FORECAST.ETS.CONFINT(A23, $B$2:$B$22, $A$2:$A$22)

FORECAST.ETS.SEASONALITY Function

Determines the length of recurring patterns in the timeline.

FORECAST.ETS.SEASONALITY Example:

=FORECAST.ETS.SEASONALITY(B2:B22, A2:A22)

FORECAST.ETS.STAT Function

Returns various statistical values related to the exponential smoothing forecast (alpha, beta, gamma, MASE, SMAPE, MAE, RMSE, step size).

FORECAST.ETS.STAT Example:

=FORECAST.ETS.STAT(B2:B22, A2:A22, 1) (returns alpha)

This comprehensive guide provides a solid foundation for using Excel's forecasting functions effectively. Remember to download the practice workbook for hands-on experience.

The above is the detailed content of Excel FORECAST and related functions with formula examples. For more information, please follow other related articles on the PHP Chinese website!