MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: The Forecast Sheet Can Handle Some Seasonality


November 18, 2020 - 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


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.