Excel Sales Forecasting For Dummies. Carlberg Conrad

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