much like the other tools that are a part of Excel – the difference is that you can choose whether to install an add-in. For example, you can’t choose whether the Goal Seek tool (under What-If Analysis on the Ribbon’s Data tab) is available to you. If you decide to install Excel on your computer, Goal Seek is just part of the package. Add-ins are different. You can decide whether to install them. When you’re installing Excel – and in most cases this means when you’re installing Microsoft Office – you get to decide which add-ins you want to use.
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 in, you find that your July forecast of $100,000 was $25,000 too low – you actually made $125,000 in sales. Now you need to forecast your sales for August. The idea behind this approach to forecasting is to adjust your August forecast in a way that would have made the July forecast more accurate. That is, because your July forecast was too low, you increase your August forecast above what it would have been otherwise.
More generally:
❯❯ If your most recent forecast turned out to be an underestimate, you adjust your next forecast upward.
❯❯ If your most recent forecast turned out to be an overestimate, you adjust your next forecast downward.
You don’t make these adjustments just by guessing. There are formulas that help out, and the Data Analysis add-in’s Exponential Smoothing tool can enter the formulas for you. Or you can roll your own formulas if you want. Turn to Chapter 15 to see how to do that.
Figure 1-3 shows what you would forecast if your prior forecast (for July) was too low – then you boost your forecast for August.
FIGURE 1-3: Here’s what happens if your forecast for July was an underestimate. Notice that the August forecast is kicked up.
And if your prior, July forecast was too high, you cool your jets a little bit in your August forecast, as shown in Figure 1-4.
FIGURE 1-4: Your forecast for March 2015 was too high, so exponential smoothing makes you back off your forecast for April 2015.
Using regression to get what you want
The term regression doesn’t sound as bad as exponential smoothing, but it is – I admit – more complicated, at least in terms of the math.
And that’s why the Regression