Finding Relationships in the Sales Forecasting Data

By Conrad Carlberg

When you make a quantitative forecast (a forecast that uses a numeric baseline rather than something like expert opinions), you’re always looking for relationships. Suppose you’re considering using regression to forecast. You can get your hands on several possible predictor variables, any one (or any combination) of which might give you your best forecast.

In the sales arena, this means looking for relationships between sales and some other variables like size of sales force, time period, or unit price. (Expert opinions, as long as they come from a real expert, are valuable, too — even if you use them only to provide a context for your quantitative forecast.)

The relationship between sales revenue for one time period and a prior time period is also frequently of interest. This is called an autocorrelation and is close conceptually to autoregression. Calculating an autocorrelation can help you make many decisions, including the following:

  • Which forecasting method to use
  • Whether you’d be misled by a moving-average forecast
  • How to structure an exponential-smoothing forecast
  • Whether to detrend a baseline

Especially if you have a sizable number of possible predictor variables, calculating the relationships one by one can be a real pain. For that, you’ll want to use the Data Analysis add-in.

One of the tools you’ll find in the Data Analysis add-in is the Correlation tool. If you set up your baseline as an Excel table, the Correlation tool takes most of the agony out of calculating several correlations.

The following figure shows:

  • Sales revenues (the variable you want to forecast)
  • Time period
  • Unit price
  • Size of sales force
  • Advertising dollars
  • Total of sales managers’ revenue estimates
3501_data-overload
This is too much data to calculate conveniently with worksheet functions.

Your goal is to decide which (if any) of the last five variables to consider as predictor variables in a regression forecast of sales revenue. To begin that work, calculate each of the correlation coefficients.