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 to the forecast. You can change this month’s result from $100,000 to $75,000 and see whether your forecast for next month changes substantially. You can’t do that with the add-in’s Regression tool unless you start all over again, because it doesn’t give you formulas. To a smaller degree, the same is true of the Exponential Smoothing tool.
But the more important reason, the reason for you to consider entering the formulas yourself, is that you’re relying on your own knowledge of how and why forecasting works. In Part 4, I show you how to use functions like LINEST and TREND to do your regression-based forecasts. You also see how to use array formulas to get the most out of those Excel functions.
You don’t need to enter all the formulas yourself to make good forecasts. The add-in includes reasonably good tools. But if you do enter the formulas yourself, not only can you be more confident that you know what’s going on with your forecast, but you can also exercise more control over what your forecast says is going to happen. In a business as tricky and trappy as forecasting, the more control you have, the better.
Chapter 2
Forecasting: The Basic Issues
IN THIS CHAPTER
Knowing why you need to forecast
Understanding the language of forecasting
Seeing what Excel can do for you
Unless you really enjoy playing with numbers, you need a good reason to bother with forecasting sales. In this chapter, I tell you some of the business reasons to forecast, beyond the fact that your Vice President of Sales makes you do it.
Like all specialties, forecasting uses terms that are unfamiliar to those who haven’t yet been inducted into the secret society. This chapter introduces you to some of the important sales forecasting terminology.
If you’re going to make a credible forecast, you need access to an archive of historical data that isn’t necessarily easy to access. You’ll often find it right there in an Excel workbook, but sometimes it isn’t there; instead, it’s in your company’s accounting database, and someone will have to exhume it. In this chapter, you see some of the reasons to put yourself or your assistant through that task.
Excel offers several methods of forecasting. Each method works best – and some work only – if you set up a baseline using what Excel terms a table. Depending on the method you choose, that table may occupy only one column, or two (or more) columns. This chapter gives you an overview of those forecasting methods, along with a brief explanation of why you might use just one column of data for your baseline, or two or more columns, depending on your choice of forecasting method.
Excel is an ideal general-purpose analysis program to use for forecasting, in part because it has functions and tools that are intended to help you make your forecasts, and in part because you often store the necessary data in Excel anyway – so, it’s right there, ready for you to use. In this chapter, you find out what’s so great about using Excel to create your forecasts, and you find some groundwork on how best to put it to use in your own situation.
People tend to think of the process of sales forecasting as a knee-jerk response to a frantic call for reassurance from some nervous, jumpy, excitable VP who’s worried about having to dust off the résumé. And often, you have some reason to believe that’s exactly what’s going on.
But there are plenty of more productive reasons to go to the trouble of gathering up baseline data, getting it into the right shape to support a credible forecast, do the analysis, and then interpret it than just responding to a VP who’s afraid the job is on the line. Here are a few of those reasons.
To plan sales strategies
If you can use sales forecasts to get a handle on either future revenues, or unit sales, or both, you can help groups like Marketing, Product Management, and Production make decisions about activities such as promotion, pricing, and purchasing – each of which influences your company’s sales results as well as its net income.
Suppose you take a look at quarterly sales results over a period of several years, and you see that during that time the sales of a particular product have been gently declining. (If the decline had been steep, you wouldn’t have to look at a baseline – everyone from the sales force to the CEO would have been rattling your cage.) Your forecast indicates that the decline is likely to continue. Is the market for the product disappearing? That depends. You need to ask and answer some other questions first.
❯❯ Is the product a commodity? Some business analysts sneer at commodities – they’re not very glamorous, after all – but commodities can be very profitable products if you dominate the market. If you don’t dominate
