Excel Sales Forecasting For Dummies, 2nd Edition

Chapter 18

Same Time Last Year: Forecasting Seasonal Sales

IN THIS CHAPTER

Recognizing seasonal patterns in your exponential smoothing

Calculating your first forecast

Modifying the formulas to finish your forecast

Years have their seasons, and seasons make their mark on sales — particularly in the retail sector. If you’re going to forecast sales in a business segment that has seasonal peaks and valleys, you’re going to need a topo map. And you can get that map by accounting for seasons in your smoothing. It’s just a step more complicated than regular old exponential smoothing. Your seasonal forecast is based not only on the most recent observation, but also on the last time this season came through on the calendar.

So, as you start to get a ways into the baseline, there are two components to a seasonal forecast, and one is the level component. This component is analogous to the previous actual baseline value used in exponential smoothing, described in Chapter 15. The current baseline level needs some adjustment before you apply the smoothing constant, in order to separate out the seasonal effect so that you can focus on the level.

The second component is seasonal. The idea is that, every year, the seasons have similar effects on sales. In preparing to make a forecast, you need to quantify those effects. You assign a number to each season — that is, you might’ve found that, over time, you experience a $2,000,000 falloff during spring and a $4,000,000 boost during winter. You can ...