MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: The Forecast Sheet Can Handle Some Seasonality


November 07, 2019 - by Bill Jelen

Excel The Forecast Sheet Can Handle Some Seasonality. Photo Credit: Jeremy Thomas at Unsplash.com

Before Excel 2016, Excel offered a few forecasting tools that did not fit in every situation. If your sales data included some seasonality, the old forecasting tools would do a bad job. Consider a movie theatre where sales peak on the weekend and plummet on Monday. Using the old linear trendline, Excel shows an R-Squared of 0.02, meaning that the trendline is doing a horrible job of predicting the future.

Daily sales at a movie theatre. Sales are high on the weekend and low during the week, creating a predictable pattern. But old Excel would forecast a straight line, which produces an R-Squared of 0.0247 - indicating the forecast is doing a poor job of modeling the future.

Excel 2016 introduced a new set of forecasting functions and an icon on the Data tab of the Ribbon to create a Forecast Sheet. If you have data with Date in column A and Sales in column B, select the data and choose Data, Forecast Sheet.


In the Create Forecast Worksheet dialog, click Options and review the Seasonality and ask for stats. Click Create and Excel inserts a new worksheet with FORECAST.ETS functions to create a forecast. The 3% error shown means that this forecast is explaining most of the variability.

Using the new Excel 2016 Forecast Sheet, Excel can detect the seasonality and produces a forecast with 3% error.

Caution

The Forecast Sheet works fine if you have one level of seasonality: More movie-goers on Friday or more gift buyers in November and December. But if your real-life data calls for both a December boom and a Saturday/Sunday peak, the FORECAST.ETS won't handle it.

Title Photo: Jeremy Thomas at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.