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 or if you’re forecasting junk. Chapter 9 shows you how to set up charts with Excel.
There’s a lot to be said for using the Data Analysis add-in to create your forecasts. The add-in’s tools are quick, they do the heavy lifting for you, and they’re reasonably comprehensive, taking care of the math and some of the charting.
But there’s nothing like doing it yourself. When you wave goodbye to the Data Analysis add-in, you establish and maintain control over what’s going on with the forecast. If you have formulas in your worksheet cells – formulas that support your forecasts – you can change those formulas as your forecasting needs change. And you can change – or add to – the baseline and immediately see what the effect doing so has on your forecast. That’s because the formulas are live: They react to changes in their inputs.
When the add-in’s tools give you not formulas but static values instead, you can’t easily experiment with the forecasts or see the effect of modifying the baseline. And the add-in’s Regression tool gives you just the static values. The Exponential Smoothing tool is a little better, but it mixes formulas with static values. And the Moving Averages tool forces you to start from scratch if you want to change the number of records in the baseline that make up a moving average.
Suppose that you have the number 3 in cell A1 and the number 5 in cell A2. In cell A3 you can enter the sum of those two numbers, 8. But if you now change the number 3 in cell A1 to, say, 103, you still have 8 in A3. It’s a constant – a number, not a formula. It doesn’t react to what’s in cell A1 or A2: You’re still going to see the number 8 in cell A3.
On the other hand, suppose you have this in cell A3:
=A1 + A2
That’s a formula, not a constant, and it tells Excel to add whatever’s in A1 to whatever’s in A2. So if you change what’s in A1, or what’s in A2, Excel recalculates the result and shows it – in this example – in A3.
The point to keep in mind is that the add-in’s regression tool gives you numbers, not formulas. It calculates your forecast, and the underlying figures, and writes numbers onto your worksheet. That means, regardless of how you change the numbers in your baseline, you’re still going to be looking at the same forecast as offered by the Regression tool.
But – and it’s a big one – if you make the forecast yourself instead of relying on the add-in’s tool, you can enter the formulas that the add-in denies you. Why is this important? By entering the formulas yourself, you have more control over what’s going on with the forecast.
Relying on the add-in, which isn’t a bad toolbox, and is one that you can generally trust, is perfectly okay. However, if you enter formulas, ones that react to changes in your baseline, you can make a change in the baseline and see what happens