Читать книгу Excel Sales Forecasting For Dummies - Carlberg Conrad - Страница 5

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.

Why Forecast?

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.

There’s at least one good aspect to a product that’s entering the final stage of its life cycle: You very likely have lots of historical data on its sales figures. And in general, the more historical data you have to base a forecast on, the more confidence you can place in that forecast.

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, but the principle remains the same: Expensive stuff can be hard to move.)

Plus, the annual carrying costs for equipment inventory in the late 1980s averaged around 15 percent of the cost of the equipment, including storage, cost of money, obsolescence, and so on. So by reducing the total inventory cost by $15 million, we saved the company $2.25 million each year. (That savings actually covered the cost of our salaries, by the way, with plenty left over.)

Simply reducing the size of inventory isn’t the end of the story, though. Sales forecasting helps you plan just-in-time (JIT) inventory management, so you can time your purchases to correspond to when sales need to be fulfilled. The less time inventory spends in the warehouse, the less money you’re paying to let it just sit there waiting to be sold.

Talking the Talk: Basic Forecasting Lingo

You need to get a handle on the specialized terminology used in forecasting for a couple very practical reasons. One is that you may be asked to explain your forecasts to your boss or in a meeting of, for example, sales managers. In those situations you want to say things like, “We decided to use regression on the baseline because it turned out to be more accurate.” You don’t want to find yourself saying “Jeff found a formula in a book he has, and we used it on these numbers here. Seems to work okay.”

Another good reason is that Excel uses many of these terms, as do other programs, and figuring out what’s going on is a lot easier if you know what the terms mean. Okay, deep breath.

Autoregressive integrated moving averages (ARIMA)

I mention autoregressive integrated moving averages (ARIMA) here not because this book is going to use it or even talk much about it. But if you’re going to do forecasting, some smart aleck will eventually ask you if you used ARIMA, and you should know how to reply. ARIMA is in part a forecasting method, and also a way of evaluating your baseline so that you can get quantitative evidence that supports using a regression approach, a moving-average approach, or a combination of both. Unless you really take to this forecasting stuff, you’ll usually do just fine without it, even though it’s an excellent, if complex, diagnostic tool.

By the way, your answer to the smart aleck should be, “No. I’ve been working with this baseline for so long now that I know I get my best results with exponential smoothing. Which, as you know, is one of the forms that ARIMA can take.”

Baseline

A baseline is a sequence of data arranged in chronological order. In terms of this book’s basic topic, the forecasting of sales, some examples of baselines include total monthly revenues from January 2010 through December 2015, number of units sold weekly from January 1, 2015, through December 31, 2016, and total quarterly revenues from Q1 2007 through Q4 2016. Data arranged like this is sometimes called a time series, but in this book I use the term baseline.

Correlation

A correlation coefficient expresses how strongly two variables are related. Its possible values range from –1.0 to +1.0, but in practice you never find correlations so extreme. The closer a correlation coefficient is to +/–1.0, the stronger the relationship between the two variables. A correlation of 0.0 means no relationship. So, you might find a correlation of +0.7 (fairly strong) between the number of sales reps you have and the total revenue they bring in: The greater the number of reps, the more that gets sold. And you might find a correlation of –0.1 (quite weak) between how much a rep sells and his telephone number.

A special type of correlation is the autocorrelation, which calculates the strength of the relationship between one observation in a baseline and an earlier observation (often, but not always, the relationship between two consecutive observations). The autocorrelation tells you the strength of the relationship between what came before and what came after. This in turn helps you decide what kind of forecasting technique to use. Here’s an example of how to calculate an autocorrelation that might make the concept a little clearer:

=CORREL(A2:A50,A1:A49)

This Excel formula uses the CORREL function to show how strong (or how weak) a relationship there is between whatever values are in A2:A50 and those in A1:A49. The most useful autocorrelations involve baselines that are sorted in chronological order. (This sort of autocorrelation is not quite the same as the autocorrelations calculated in ARIMA models.)

Cycle

A cycle is similar to a seasonal pattern (see the “Seasonality” section, later in this chapter), but you don’t consider it in the same way as you do seasonality. The upswing might span several years, and the downswing might do the same. Furthermore, one full cycle might take four years to complete, and the next one just two years. A good example is the business cycle: Recessions chase booms, and you never know just how long each is going to last. In contrast, yearly seasons have the same length, or nearly so.

Damping factor

The damping factor is a fraction between 0.0 and 1.0 that you use in exponential smoothing to determine how much of the error in the prior forecast will be used in calculating the next forecast.

Actually, the use of the term damping factor is a little unusual. Most texts on exponential smoothing refer to the smoothing constant. The damping factor is 1.0 minus the smoothing constant. It really doesn’t matter which term you use; you merely adjust the formula accordingly. This book uses damping factor where necessary because it’s the term that Excel’s Data Analysis add-in uses.

Exponential smoothing

Stupid term, even if technically accurate. Using exponential smoothing, you compare your prior forecast to the prior actual (in this context, an actual is the sales result that Accounting tells you – after the fact – that you generated). Then you use the error – that is, the difference between the prior forecast and the prior actual – to adjust the next forecast and, you hope, make it more accurate than if you hadn’t taken the prior error into account. In Chapter 15, I show you how really intuitive an idea this is, despite its pretentious name.

Forecast period

The forecast period is the length of time that’s represented by each observation in your baseline. The term is used because your forecast usually represents the same length of time as each baseline observation. If your baseline consists of monthly sales revenues, your forecast is usually for the upcoming month. If the baseline consists of quarterly sales, your forecast is usually for the next quarter. Using the regression approach, you can make forecasts farther into the future than just one forecast period, but the farther your forecast gets from the most recent actual observation, the thinner the ice.

Moving average

You’ve probably run into the concept of moving averages somewhere along the line. The idea is that averaging causes noise in the baseline to cancel out, leaving you with a better idea of the signal (what’s really going on over time, unsullied by the inevitable random errors). It’s an average because it’s the average of some number of consecutive observations, such as the average of the sales in January, February, and March. It’s moving because the time periods that are averaged move forward in time – so, the first moving average could include January, February, and March; the second moving average could include February, March, and April; and so on.

There’s no requirement that each moving average include three values – it could be two, or four, or five, or conceivably even more. (Chapter 13 fills you in on the effects of choosing more or fewer periods to average.)

Predictor variable

You generally find this term in use when you’re forecasting with regression. The predictor variable is the variable you use to estimate a future value of the variable you want to forecast. For example, you may find a dependable relationship between unit sales price and sales volume. If you know how much your company intends to charge per unit during the next quarter, you can use that relationship to forecast the sales volume for next quarter. In this example, unit sales price is the predictor variable.

Regression

If you use the regression approach to sales forecasting, it’s because you’ve found a dependable relationship between sales revenues and one or more predictor variables. You use that relationship, plus your knowledge of future values of the predictor variables, to create your forecast.

How would you know those future values of the predictor variables? If you’re going to use unit price as a predictor, one good way is to find out from Product Management how much it intends to charge per unit during each of the next, say, four quarters. Another way involves dates: It’s entirely possible, and even common, to use dates (such as months within years) as a predictor variable. Even I can figure out what the next date value is in a baseline that at present ends at November 2015.

Seasonality

During the span of a year, your baseline might rise and fall on a seasonal basis. Perhaps you sell a product whose sales rise during warm weather and fall during cold. If you can see roughly the same pattern occur within each year over a several-year period, you know you’re looking at seasonality. You can take advantage of that knowledge to improve your forecasts. It’s useful to distinguish seasons from cycles. You never know how long a given cycle will last. But each of four seasons in a year is three months long.

Trend

A trend is the tendency of the level of a baseline to rise or fall over time. A rising revenue trend is, of course, good news for sales reps and sales management, to say nothing of the rest of the company. A falling baseline of sales, although seldom good news, can inform Marketing and Product Management that they need to make and act on some decisions, perhaps painful ones. Regardless of the direction of the trend, the fact that a trend exists can cause problems for your forecasts in some contexts – but there are ways of dealing with those problems. Chapter 17 shows you some of those ways.

Understanding the Baseline

A baseline is a series of observations – more to the point in this book, a revenue stream – that you use to form a forecast. There are three typical forecasts, depending on what the baseline looks like:

❯❯ If the baseline has held steady, your best forecast will probably be close to the average of all the sales amounts in the baseline.

❯❯ If the baseline has been rising, your forecast will likely be higher than the most recent sales amount.

❯❯ If the baseline has been falling, your forecast will probably be lower than the most recent sales amount.

Note: Those weasely words likely and probably are there because when there’s a seasonal aspect to the sales that doesn’t yet appear in your baseline, the next season might kick in at the same point as your forecast and reverse what you’d expect otherwise.

Why is a baseline important? Because it elevates your forecast above the status of a guess. When you use a baseline, you recognize that – absent special knowledge such as the fact that your per-unit price is about to change drastically – your best guide to what happens next is often what happened before.

There’s another weasel word: often. You’ll have plenty of opportunities to use one variable, such as the total of sales estimates from individual sales representatives, to forecast the variable you’re really interested in, sales revenues. In that case, you might get a more accurate forecast by using Excel to figure the formula that relates the two variables, and then use that formula to forecast the next value of sales revenues.

Depending on the strength of the relationship between the two variables, that formula can be a better guide than looking solely to the baseline of sales history. It’s still a baseline, though: In this case, the baseline consists of two or more variables, not just one.

Charting the baseline

The eye is a great guide to what’s going on in your baseline. You can take advantage of that by making a chart that shows the baseline. There are a couple of possibilities:

❯❯ If you’re making your forecast solely on the basis of previous sales revenues, a good choice is a Line chart, like the one shown in Figure 2-1. You can see that the revenues are flat over time, even though they jump around some. The baseline’s pattern in the chart is a clue to the type of forecast to use: In Figure 2-1, that type could be exponential smoothing.

❯❯ If you’re using another variable – such as the total of the sales estimates provided by individual sales reps – you’d probably use an XY (Scatter) chart, like the one shown in Figure 2-2. Notice that the actuals track fairly well against the sum of the individual estimates, which may convince you to use the regression approach to forecasting the next period, especially because you can get your hands on the next estimate from the sales force to forecast from.

FIGURE 2-1: The Line chart is ideal for just one variable, such as sales revenues.

FIGURE 2-2: In this case, a positive relationship exists between the sum of individual estimates and the actual results.

If you’re going to base your next forecast on information from individual sales reps, don’t make your forecast periods too short. If you do, you’ll have the reps spending more time making estimates than making sales, which means their commissions decline, and the next thing you know they’re working for your competition – and you can flush your forecast down the toilet.

Looking for trends