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 tool in the Data Analysis add-in is convenient. The add-in takes responsibility for the math, just as it does with moving averages and exponential smoothing. Remember: You still have to give a good baseline to the tools in the Data Analysis add-in to get accurate results.
Here’s a quick look at forecasting with regression. (You can find a more detailed look in Chapter 11.)
The idea behind regression is that one variable has a relationship with another variable. When you’re a kid, for example, your height tends to have a relationship to your age. So if you want to forecast how tall you’ll be next year – at least, until you quit growing – you can check how old you’ll be next year.
Of course, people differ. When they’re 15 years old, some people are 5 feet tall, some are 6 feet tall. On average, though, you can forecast with some confidence how tall someone will be at age 15. (And you can almost certainly forecast that a newborn kidlet is going to be under 2 feet tall.)
The same holds true with sales forecasting. Suppose your company sells consumer products. It’s a good bet that the more advertising you do, the more you’ll sell. At least it’s worth checking out whether there’s a relationship between the size of your advertising budget and the size of your sales revenue. If you find that there’s a dependable relationship – and if you know how much your company is willing to spend on advertising – you’re in a good position to forecast your sales.
Or suppose your company markets a specialty product, such as fire doors. (A fire door is one that’s supposed to be resistant to fire for some period of time, and there are a lot of them in office buildings.) Unlike consumer products, something such as a fire door doesn’t have to be a particular off-the-shelf color or have a fresher-than-fresh aroma. If you’re buying fire doors, you want to get the ones that meet the specs and are the cheapest.
So if you’re selling fire doors, as long as your product meets the specs, you’d want to have a look at the relationship between the price of fire doors and how many are sold. Then you check with your marketing department to find out how much they want you to charge per door, and you can make your forecast accordingly.
You use Excel’s tools to quantify that relationship. In the case of regression forecasts, you give Excel a couple of baselines. To continue the examples used so far in this section:
❯❯ Historical advertising expenses and historical sales revenues
❯❯ How much you charged per fire door and how many doors you sold
If you give Excel good baselines, it will come back to you with a formula.
❯❯ Excel will give you a number to multiply times how much you expect to spend on advertising, and the result will be your expected sales revenue.
❯❯ Or, Excel will give you a number to multiply times the unit cost per door, and the result will be the number of doors you can expect to sell.
I’ve been doing this stuff for a long time, and I can’t tell you how critical it is to chart your baseline and your forecast. Being able to visualize what’s going on is important for several reasons.
Using Excel’s charts, you can see how your actuals are doing (see Figure 1-5). And by charting your actuals, you can see how well your sales forecasts do against the actual sales results. Figure 1-6 shows a forecast that’s based on moving averages, against the monthly actuals.
FIGURE 1-5: An Excel chart makes it much easier to see how your sales are doing.
FIGURE 1-6: Notice how the moving average lags behind the actual results.
By charting your baseline and your forecasts, you can:
❯❯ See how your actual results are doing. A chart is almost always more revealing than a table of numbers.
❯❯ See how well your forecasts predict actual results. Your eye is a good gauge of the quality of your forecasts.
❯❯ See how well a different variable – advertising dollars or the Consumer Price Index – predicts the sales of your product.
Yes, an R squared or some other summary statistic can give you a concise estimate of how well your forecasts are working. But there’s nothing, nothing, like a chart to tell you if you’re forecasting results
