Forecast Data with Seasonality

May 19, 2022 - by Bill Jelen

Problem: Straight-line forecasting won’t work because our sales are seasonal. We sell more near the Christmas holiday and less in the summer.

Strategy: Excel 2016 introduced a new Forecast Sheet command. Select your data with dates and sales. From the Data tab, choose Forecast Sheet. Excel will analyze your data, look for seasonality and produce new cells showing a future forecast and a chart with a confidence interval. The new rows will use new functions such as FORECAST.ETS and FORECAST.ETS.CONFINT.

The Forecast Sheet will take data with seasonality and forecast it into the future. — Figure 476. Excel can detect seasonality and produce a forecast.

Gotcha: If you have two factors for seasonality: Fridays are better than Wednesdays and December is better than July, the command will not handle the real-life scenario well.

This article is an excerpt from Power Excel With MrExcel

Title photo by Luke Porter on Unsplash