Forecast in Excel: linear and exponential smoothing forecasting models

This tutorial demonstrates how to perform time series forecasting in Excel using both the built-in Forecast Sheet feature and custom formulas. Excel offers several forecasting methods, and this guide covers two key approaches: exponential smoothing and linear regression.

- Excel Forecasting Techniques

• Exponential Smoothing
  • Using the Forecast Sheet
  • Manual Exponential Smoothing Formulas
• Linear Regression Forecasting
  • Linear Forecast Formulas
  • Creating a Linear Regression Chart

Understanding Excel Forecasting

Forecasting uses historical data to predict future trends. It's valuable for tasks like budgeting, sales projections, and cash flow analysis. Remember, forecasts provide probabilities, not certainties; always review results critically before making decisions. Excel provides tools including built-in functions, features, and charting capabilities.

Exponential Smoothing in Excel

Excel's exponential smoothing utilizes the AAA version (additive error, additive trend, additive seasonality) of the Exponential Triple Smoothing (ETS) algorithm. This method smooths data by identifying seasonal patterns and confidence intervals, making it ideal for non-linear data with recurring patterns (available in Excel 2016, 2019, and Office 365).

Automated Exponential Forecasting with the Forecast Sheet

The Forecast Sheet (introduced in Excel 2016) simplifies time series forecasting. Organize your data correctly, and Excel handles the calculations.

Data Preparation

Prepare two adjacent columns:

• Time Series: Sequentially observed dates or times (hourly, daily, monthly, etc.) at regular intervals. Consistent intervals are crucial. While the tool can handle up to 30% missing data or duplicate dates, summarizing data for accuracy is recommended.
• Data Values: Corresponding numerical values to be predicted.

Example: Forecasting sales based on historical monthly data (Note: Column A uses a custom format showing month and year, but the underlying data is date values, not text).

Creating the Forecast

1. Select both data series (selecting one cell often automatically selects the entire series).
2. Navigate to the Data tab > Forecast group and click Forecast Sheet.
3. In the Create Forecast Worksheet window, choose your chart type (line or column) and specify the forecast end date. Click Create.

Excel generates a new sheet with a table of original and predicted values, along with a chart.

Examine the formulas in the forecast sheet or refer to the section on Exponential Smoothing Formulas for details on the calculations.

Customizing the Forecast

Access forecast options by clicking Options in the Create Forecast Worksheet window:

Adjust settings like:

• Forecast Start: Start date for predictions (consider starting before the last historical point for seasonal data or to compare predictions with known values).
• Confidence Interval: Prediction range (default is 95%).
• Seasonality: Length of seasonal cycles (Excel auto-detects, but manual input is possible).
• Include Forecast Statistics: Displays statistical information (smoothing constants, error metrics).
• Timeline Range & Values Range: Data ranges for time series and values.
• Fill Missing Points Using: Handles missing data points (interpolation or zeros).
• Duplicate Aggregates Using: Handles multiple values with the same timestamp (average, median, etc.).

Manual Exponential Smoothing Formulas

The Forecast Sheet uses the FORECAST.ETS and FORECAST.ETS.CONFINT functions.

Forecasted Values (FORECAST.ETS)

Syntax: FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

Example: =FORECAST.ETS(A31, $B$2:$B$30, $A$2:$A$30, 1, 1)

Confidence Interval (FORECAST.ETS.CONFINT)

Syntax: FORECAST.ETS.CONFINT(target_date, values, timeline, confidence_level, [seasonality], [data_completion], [aggregation])

Example: =FORECAST.ETS.CONFINT(A31, $B$2:$B$30, $A$2:$A$30, 0.95, 1, 1)

Confidence bounds are calculated by adding and subtracting the confidence interval from the forecast value.

Linear Regression Forecasting

Linear regression is suitable for data lacking seasonality or with insufficient data for detecting it. Excel offers FORECAST (older versions) and FORECAST.LINEAR (Excel 2016 and later) functions. Both have identical functionality.

Linear Forecast Formulas

Example (using FORECAST.LINEAR): =FORECAST.LINEAR(A14, $B$2:$B$13, $A$2:$A$13)

Linear Regression Chart

Create a line chart combining the historical and forecasted data for a visual representation. Remember to copy the last historical data point to maintain a continuous line.

This comprehensive guide empowers you to leverage Excel's forecasting capabilities effectively. For a practical demonstration, download the sample workbook (link provided in original text).

The above is the detailed content of Forecast in Excel: linear and exponential smoothing forecasting models. For more information, please follow other related articles on the PHP Chinese website!