The Baseline in Excel Sales Forecasting - dummies

By Conrad Carlberg

A baseline in sales forecasting 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 here. 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 the figure, that type could be exponential smoothing.
The Line chart is ideal for just one variable, such as sales revenues.
  • 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 here. 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.
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

Trends are important in sales forecasting. For one thing, knowing if there’s a trend in your baseline is critical to knowing more about what’s going on in the product line. For another, the presence of a trend sometimes tells you that you have to do more preparation. If you’ve decided to use exponential smoothing, for example, you may want to remove the trend first.

If you pored over the baseline shown in column A of the following figure, it wouldn’t take you long to conclude that there’s an upward trend in the sales revenues. But if you exert the tiny bit of effort needed to chart the baseline, not only do you immediately see the trend, but you get a good intuitive idea of where the sales are headed and how fast they’re getting there.

You could either detrend this series and use simple exponential smoothing, or forecast sales revenues using the period number as the predictor.

Be careful when you see a trend such as the one shown. If these are weekly results, it may just be the first part of a seasonal pattern (or a cycle) that’s about to head back down. Notice that the final seven periods look as though the results may be getting ready to do just that.