Using Qualitative Data in Excel Sales Forecasting

By Conrad Carlberg

Qualitative data is information that helps you understand the background for quantitative data. Of course, that begs the question: What’s quantitative data? Quantitative data is numeric data — the number of units your team sold during the prior quarter, or the revenue that your team brought in during March.

With quantitative data, you can use Excel to calculate the number of units sold per month, or the fewest, or the most. You can use Excel to figure a moving average of the revenue your sales team has earned, or its minimum revenue, or the percentage of annual revenue earned during October.

In contrast, qualitative data doesn’t have an average, a minimum, or a maximum. It’s information that helps you understand quantitative data. It puts the numbers into a context. It helps to protect you against making really dumb mistakes.

Asking the right questions

Suppose that your VP of Sales asks you to forecast how many cars your agency will sell during the next year. If your agency sells mostly Fords, it’s reasonable to take a whack at a forecast. If, up until last year, your agency sold mostly Duesenbergs, making a forecast is unreasonable. You can’t sell any Duesenbergs because nobody’s making them anymore.

That example is admittedly extreme, but it’s not entirely stupid. You need to know what your company is going to bring to market during the time period that you want to forecast into. Otherwise, your sales history — your baseline — just isn’t relevant. And you can’t make an accurate forecast that’s based on an irrelevant baseline.

Here are some questions you should ask before you even start thinking about putting a baseline together:

  • How many salespeople will your company make available to you? Will you have more feet on the street than you did last year? Fewer? About the same? The size of the sales force makes a difference. To make a decent forecast, you need to know what sales resources you’re going to have available.
  • Will the commission levels change during the forecast period? Is your company incentivizing its sales force as it has during, say, the last 12 months? If so, you don’t need to worry about this in making forecasts. But if the business model has changed and commission rates are going to drop because the competition has dropped — or rates are going up because the competition has stiffened — your forecast needs to take that into account.
  • Will the product pricing change during this forecast period? Will your product line’s prices jump? If so, you probably need to build some pessimism into your forecast of units sold. Will they drop? Then you can be optimistic. (Keep in mind that pricing usually affects units sold more than it does revenue.)

You can’t use forecasting to answer questions like these. And yet their answers — which qualify as qualitative data — are critical to making good forecasts. You can have a lengthy, well-behaved baseline, which is really key to a good forecast. And then you can get completely fooled if your company changes its product line, or reduces its sales force, or changes its commission structure so much that the sales force walks, or lowers its prices so far that the market can’t keep its collective hands off the product line. Any of these is going to make your forecast look like you shrugged and rolled a couple of dice.

You can’t depend entirely on a baseline to make a sales forecast. You need to pay attention to what your company is doing in its marketing, its pricing, its management of people, its response to the competition, in order to make a good sales forecast.

Keeping your eye on the ball: The purpose of your forecast

Set up your baseline to reflect the period you want to forecast into. That is, if you want to forecast one month’s sales, your baseline should show your sales history in months. If the purpose of the forecast is to help guide financial projections such as earnings estimates, you probably want to forecast a quarter’s results, and your baseline should be organized into quarters.

The figure shows an example of a useful baseline.

2801_baseline
The forecast is for the next month, so the baseline provides monthly sales history.

You could easily create the list of month names in column B. You would select cell B2 and type January, or Jan. Press Enter, and if necessary reselect cell B2 (or press Ctrl+Enter to leave cell B2 selected when you enter its value). Notice the small black square in the lower-right corner of the cell — it’s called the fill handle. Move your mouse pointer over the fill handle. You’ll see the pointer change to crosshairs. Now, although you can still see the crosshairs, press the mouse button, continue holding it down, and drag down as far as you want. Excel fills in the names of the months for you. This also works for days of the week.

There are some rules of thumb about building a baseline that you’ll find it useful to keep in mind.

  • Use time periods of the same length in your baseline. Using one period covering February 1 through February 14, and the next period covering February 15 through March 31 is peculiar. I’ve seen it done, though, just because it turned out to be convenient to put the data together that way. But that throws things off, because the apparent February revenues are an underestimate and the apparent March revenues an overestimate. Regardless of the forecast approach you use, that’s going to be a problem. (You can safely ignore small differences, such as 28 days in February and 31 days in March.)
  • Make sure the time periods in your baseline are in order, earliest to latest. Several popular forecasting techniques, including two described in this book, rely on the relationship between one period’s measurement and the next period’s measurement. If your time periods are out of time order, your forecast will be out of whack. Often, your raw measures won’t be in chronological order, and for various reasons you’ll want to summarize them with a pivot table — which you can easily put into date order. In fact, pivot table’s put summarized data into chronological order by default.
  • Account for all time periods in the baseline. If your baseline starts in January 2015, you can’t leave out February 2015, even if the data is missing. If the remaining months are in place, skip January 2015 and start with March 2015. Why? Because you want to make sure you’re getting the relationship right between one period and the next.