Recovering from Mistakes in Excel Sales Forecasting - dummies

Recovering from Mistakes in Excel Sales Forecasting

By Conrad Carlberg

Forecasting in Excel can be tricky business. You can do everything right and wind up with a forecast that completely misses the mark. It’s not pure math. Human factors, the economy, the weather, technology — they all conspire to make your forecast look bad. Here are some reasons for mistakes that are beyond your control, and some that you should be able to get your arms around.

Getting over it

In the forecasting dodge, you have to get used to being wrong. The best you can do is get close. More often than not you’re going to miss the target. Fortunately, in sales forecasting, close is usually all that’s needed. You just can’t tell what’s going to happen in the marketplace tomorrow, next month, next year. The best you can do is to act on these recommendations. It will help to get your management used to that idea. Then, they won’t be too surprised when the forecast is off base.

And it will be. You can use the past as a guide to the future, but it won’t always be a reliable guide. Because the future doesn’t always respond to the past, your forecasts will sometimes be, well, wrong.

The problem is that the market doesn’t stand still, for reasons like these:

  • Customers make new choices.
  • Product lines change.
  • Marketing strategies change.
  • Pricing strategies change.

Given all that, you just can’t expect to nail your forecasts time and again.

But — and this is a big but — you usually have some lead time. Market conditions tend not to change suddenly. Customers don’t all shift to ordering exclusively Hewlett-Packard computers on Tuesday, when they’ve been ordering Hewlett-Packards and Dells through Monday.

These things happen more gradually, and that’s one reason that your baseline is so important. The forecasting tools take that into account. They take note of the fact that one product’s market share is gently declining, while another’s is gently rising.

Using revenue targets as forecasts

Here’s how sales forecasts frequently come about: The Sales VP at corporate needs to tell the CFO what the revenues are going to be for Q2 2017. As those who have been in sales all know:

Little bugs have littler bugs

Upon their backs to bite ’em,

And littler bugs have littler still,

And so on, ad infinitum.

So the Sales VP gets after the regional sales directors, who get after the district sales managers, and so on, ad infinitum, for Q2 2017 sales forecasts.

Now, suppose you’re a district sales director or a branch sales manager, and you’re supposed to come up with a sales forecast for Q2 2017. Here’s how you might do it:

  1. You check your quota for the second quarter.
    Turns out that’s $1,500,000.
  2. You phone in your forecast, which coincidentally is also $1,500,000.
    An experienced sales manager would also build in a fudge factor.

Now that’s a sorry way of forecasting. It’s bad business and it chases its own tail. One major purpose of forecasting is to set sales quotas on a regional, branch, and personal basis. And here companies are rolling up quotas to set forecasts.

So you shouldn’t take a quota and pretend it’s a forecast. Of course, people do it all the time, but that doesn’t make it a good idea.

A good idea is to look at the qualitative aspects of your product line, your sales force, your market, and your competition, to make sure you’re on point for your forecast period. Then, if you still feel comfortable with those, take your baseline and extend it.