Build Faster Forecasts with Excel’s FORECAST.ETS Function

In the world of financial planning and analysis, the ability to predict future trends with precision is a superpower. While many users rely on simple linear regressions, these often fall short when dealing with real-world business cycles. If your business experiences predictable fluctuations — such as a holiday retail spike or a summer slowdown in construction — you need a tool that understands “seasonality.”

Excel’s FORECAST.ETS function is designed specifically for this purpose. Using Exponential Triple Smoothing (ETS), this function detects recurring peaks and valleys in your historical data to provide a much more nuanced projection than standard linear models.

Here is a step-by-step guide to building faster, more accurate forecasts using FORECAST.ETS.

Why Choose FORECAST.ETS Over Standard Forecasting?

Standard forecasting functions often assume a straight-line growth pattern. However, most revenue, demand, and expense models are cyclical. FORECAST.ETS (which stands for Error, Trend, and Seasonal) is an advanced algorithm that assigns weights to past data points, giving more importance to recent observations while identifying seasonal patterns. This results in a sophisticated predictive model that can be implemented in seconds.

Step 1: Prepare Your Historical Data

The accuracy of your forecast is entirely dependent on the quality of your input. To use FORECAST.ETS effectively, you must organize your data so Excel can interpret the timeline correctly.

Organize Your Layout

Set up your spreadsheet with two adjacent columns. Typically, Column A will contain your Timeline (dates), and Column B will contain your Values (sales figures, inventory levels, or expenses).

Ensure Data Integrity

For the ETS algorithm to work, your dates must be clean and sequential. This means:

• Consistent Intervals: Your data should be at regular intervals, such as daily, monthly, or quarterly.
• No Missing Gaps: If you are missing data for a specific month, it is better to use an average or a placeholder than to leave the cell blank, though the function does have a sub-argument to handle minor gaps.
• Chronological Order: Ensure your timeline flows from the oldest date to the most recent.

Step 2: Apply the FORECAST.ETS Formula

Once your data is structured, you can apply the formula to project into the future. The syntax for the function is:

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

Understanding the Arguments

• Target_date: This is the future date you want to predict (e.g., the first month of the next fiscal year).
• Values: The range of cells containing your historical numerical data.
• Timeline: The range of cells containing your historical dates.

Let Excel Do the Heavy Lifting

One of the greatest benefits of FORECAST.ETS is that the “seasonality” argument is optional. By default, Excel uses a value of “1,” which means it automatically detects seasonal patterns by looking for repetitive cycles in your timeline. Simply point the formula to your target date and historical ranges, and Excel produces a projected value that accounts for your business’s unique rhythm.

Step 3: Visualize the Output for Better Insights

A number in a cell is useful, but a visual trend is easier to communicate to stakeholders. To make your forecast actionable, you should transform your results into a dynamic chart.

Compare History and Projections

Create a line chart that includes both your historical data and your new projected values. Using a different color or a dashed line for the forecast section helps distinguish between “what happened” and “what we expect.”

Highlight Key Inflection Points

When designing your chart, use markers for key inflection points. This allows you to pinpoint exactly when you expect a seasonal peak to occur or when a trend is likely to shift. Visualizing the “valleys” in your data is equally important, as it helps management prepare for periods of lower demand or tighter cash flow.

Practical Tips for Success

To get the most out of Excel’s advanced forecasting tools, keep these best practices in mind:

• The Two-Year Rule: For the ETS algorithm to accurately identify a seasonal pattern, you should provide at least two full years of historical data. This allows the software to confirm that a “peak” in December is a recurring seasonal event rather than a one-time anomaly.
• Compare Multiple Models: Don’t rely on a single output. Pair FORECAST.ETS with other models, such as FORECAST.LINEAR, to see the variance between a seasonal projection and a standard trend line.
• Watch for Outliers: If a specific month had an unprecedented spike due to a one-time event (like a massive government contract), consider smoothing that data point so it doesn’t skew your future projections.

Excel’s FORECAST.ETS function provides a high-level forecasting approach that was once reserved for dedicated statistical software. Whether you are modeling revenue, demand, or long-term expense trends, this tool allows you to move beyond simple guesswork. By following these steps to prepare, calculate, and visualize your data, you can provide your organization with the predictive insights needed to stay ahead of the competition.

For more Excel tutorials, quick-tip videos and articles, check out LearnExcelNow.

Filed under

• Finance Technology