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 the market, maybe you shouldn’t be in the market for that commodity. So, have your competitors been cutting into your market share, or is the total size of the market shrinking? If the problem is the competition, maybe you want to do something to take back your share, even if that requires putting more resources into the product line – such as retooling its manufacture, putting more dollars into promotions, or cutting the price. But if the total market itself is shrinking, it may just be time to bail out.
❯❯ How old is the product? Products do have life cycles. When products are bright and shiny, the sales revenues can grow sharply over a fairly short time frame. When products reach maturity, the sales usually flatten out. And then, as newer, better, fancier products arrive, the sales start to drop. Think streaming video versus DVD. Get Marketing and Product Management to assess whether the product is getting long in the tooth. If it is, it may be time to get out. Or, it may be smart to spruce up the product and differentiate it from the competition’s versions, in order to squeeze some more profitable revenue out of it before you give up on it. Forecasting can inform that kind of decision, although it can’t make it for you.
❯❯ How will Sales support the product? If your company decides that it’s not yet time to abandon the product, Sales Management needs to make some decisions about how to allocate its resources – that is, its sales reps. One way to do that, of course, is to take the product out of some reps’ bags and replace it with another, more robust product. (Keep in mind that some reps prefer older products because they can use familiar sales strategies.)
❯❯ Is it possible that the decline in sales is due more to large-scale economic conditions than to problems with the product itself? If so, you may decide to hang in and wait for the economy, consumer confidence, or the index of leading economic indicators to improve, instead of making a drastic decision to drop a product line.
To size inventories
During the late 1980s, I worked for a Baby Bell – one of the companies that was spun off by the AT&T breakup. For a couple of years, I was in charge of managing resale equipment inventories at that Baby Bell.
My staff and I reduced the size of the equipment intended for sale to customers from a grotesque $24 million to a more reasonable $9 million in 18 months, without resorting to write-downs. We did it by forecasting sales by product line. This helped us tell which products we could expect to have high turns ratios (the speed with which the product line would sell) and we’d buy those in quantities that increased our discounts from our suppliers.
Until we were almost out of them, we refused to buy any products that our forecasts indicated would have low turns ratios. It didn’t matter how piteous the pleadings of the sales managers who wanted them on hand for fast delivery just in case a customer decided to buy one and wanted it installed right now. (Getting a huge PBX out of warehouse storage in West Eyesocket, Connecticut, and shipping it to Broken Pelvis, Montana, can take longer than you may think. For one thing, you may have to pressure Connecticut’s Regional VP into letting go of it. Today, VoIP software is rapidly replacing big electronic switches,