Understanding Seasonality in Excel Sales Forecasting - dummies

Understanding Seasonality in Excel Sales Forecasting

By Conrad Carlberg

A seasonal baseline in Excel sales forecasting is one that rises and falls regularly. For example, one that has higher sales revenue during the summer and lower sales revenue during the winter (such as Speedo swimsuits), or higher during the first and third quarters, and lower during the second and fourth quarters (such as a line of textbooks for a course that is offered every other quarter).

A seasonal baseline can be a special case of a cyclical baseline. Cyclical baselines rise and fall but not necessarily on a regular basis. A good example is the business cycle as it’s related to recessions. Recessions come and go, but nothing requires them to follow the calendar. The U.S. economy contracted, big time, in the late 1860s, the early and mid-1880s, the 1910s, during the Great Depression of the late 1920s and early 1930s, and during the Great Recession of 2007 through 2009. But there is nothing regular about when these contractions occurred. They’re cyclical, not seasonal.

Contrast that with a baseline that rises and falls along with a calendar grouping. Sales that depend on the season of the year are both cyclical and seasonal. They follow a cycle, and it’s a regular, seasonal cycle. Depending on the product and the time of year, the seasonal cycle might rise and fall every 3 months, or every 6 months, or even every 12 months.

A trap may be lurking. Suppose that you’re dealing with a cyclical or seasonal series that looks like the one shown here. How serious the trap is depends on how long your baseline is, and on how far out you want to forecast.

You can get fooled into thinking you’re dealing with a trend in your baseline, when a longer look would show you that you have a seasonal baseline.

Suppose you build your baseline on a weekly basis, from January 1 through March 12. It could look like the one here, which shows a subset of the baseline in the preceding figure.

Here, your baseline is limited to the upward trend of the seasonal series shown earlier.

If you want to forecast into March 19, you’re probably okay — although you don’t yet know it on March 12, the series is still on its way up. But if on March 19 you want to forecast beyond into March 26, you’re going to have a problem — although you don’t know it yet, the series is starting down because of its seasonality.

Generally, the longer your baseline, the better your forecast. If your baseline is as shown in the figure figure, where you have six months’ worth of data to forecast from, then you can tell the trend is seasonal and allow for that in your forecast. If the baseline extends from January 1 through March 19, though, you’re going to get fooled.