They have a unique way of helping you group your historical data – by day, by week, by month, by quarter, by year, you name it. Chapter 8 gives you the details, as well as much more information on pivot tables, including troubleshooting some common problems.
Part 3 gets into the business of making actual forecasts, ones that are based on historical data (that is, what’s gone on before). You see how to use the Data Analysis add-in to make forecasts that you can back up with actuals – given that you’ve looked at Part 2 and set up your actuals correctly. (Your actuals are the actual sales results that show up in the company’s accounting records – say, when the company recognizes the revenue.)
The Data Analysis add-in is a gizmo that has shipped with Excel ever since 1995. It includes a convenient way to make forecasts, as well as to do general data analysis. The three principal tools that the Data Analysis add-in gives you to make forecasts are:
❯❯ Moving Averages
❯❯ Exponential Smoothing
❯❯ Regression
Those are the three principal forecasting methods, and they form the basis for the more-advanced techniques and models. So it’s no coincidence that these tools have the same names as the forecasting methods mentioned earlier in this chapter.
The following sections offer a brief introduction to the three Data Analysis tools.
Putting moving averages to work for you
You may already be familiar with moving averages. They have two main characteristics, as the name makes clear:
❯❯ They move. More specifically, they move over time. The first moving average may involve Monday, Tuesday, and Wednesday; in that case, the second moving average would involve Tuesday, Wednesday, and Thursday; the third Wednesday, Thursday, and Friday, and so on.
❯❯ They’re averages. The first moving average may be the average of Monday’s, Tuesday’s, and Wednesday’s sales. Then the second moving average would be the average of Tuesday’s, Wednesday’s, and Thursday’s sales, and so on.
The basic idea, as with all forecasting methods, is that something regular and predictable is going on – often called the signal. Sales of ski boots regularly rise during the fall and winter, and predictably fall during the spring and summer. Beer sales regularly rise on NFL Sundays and predictably fall on other days of the week.
But something else is going on, something irregular and unpredictable – often called noise. If a local sporting goods store has a sale on, discounting ski boots from May through July, you and your friends may buy new boots during the spring and summer, even though the regular sales pattern (the signal) says that people buy boots during the fall and winter. As a forecaster, you typically can’t predict this special sale. It’s random and tends to depend on things like overstock. It’s noise.
Let’s say you run a liquor store, and a Thursday night college football game that looked like it would be the Boring Game of the Week when you were scheduling your purchases in September has suddenly in November turned into one with championship implications. You may be caught short if you scheduled your purchases to arrive at your store the following Saturday, when the signal in the baseline leads you to expect your sales to peak. That’s noise – the difference between what you predict and what actually happens. By definition, noise is unpredictable, and for a forecaster it’s a pain.
If the noise is random, it averages out. Some months, sporting goods stores will be discounting ski boots for less than the cost of an arthroscopy. Some months, a new and really cool model will come out, and the stores will take every possible advantage. The peaks and valleys even out. Some weeks there will be an extra football game or two and you’ll sell (and therefore need) more bottles of beer. Some weeks there’ll be a dry spell from Monday through Friday, you won’t need so much beer, and you won’t want to bear the carrying costs of beer you’re not going to sell for a while.
So with moving averages, you take account of the signal – the fact that you sell more ski boots during certain months and fewer during other months, or that you sell more beer on weekends than on weekdays. At the same time you want to let the random noises – also termed errors – cancel one another out. You do that by averaging what’s already happened in two, three, four, or more previous consecutive time periods. The signal in those time periods is emphasized by the averaging, and that averaging also tends to minimize the noise.
Suppose you decide to base your moving averages on two-month records. That is, you’ll average January and February, and then February and March, and then March and April, and so on. In that case you’re getting a handle on the signal by averaging two consecutive months and reducing the noise at the same time. Then, if you want to forecast what will happen in May, you hope to be able to use the signal – that is, the average of what’s happened in March and April.
Figure 1-2 shows an example of the monthly sales results and of the two-month moving average.
FIGURE 1-2: The moving average shows the general direction of the sales (the signal), and deemphasizes the random variations (the noise).
Chapter 14 goes into more detail about using moving averages for forecasting.
Making sense of exponential smoothing
I know, the term exponential smoothing sounds intimidating and pretentious. I guess it’s both – although I promise I’m not responsible for it. (If you really want, you can find out why it’s called that in Chapter 15.) In any event, don’t worry about what it’s called – it’s just a kind of self-correcting moving average.
Suppose that in June, you forecast $100,000 in sales for July. When the July sales results are
