How to Set Up Your Excel Sales Forecast - dummies

By Conrad Carlberg

The most straightforward way of getting a sales forecast is to lay out your baseline on an Excel worksheet in a table configuration and then call on the Data Analysis add-in to generate a forecast for you. That add-in accompanies Microsoft Office.

The add-in and its tools are good news and bad news — more good than bad, actually. It hasn’t changed substantially since Excel 1995, except that now the code is written using Visual Basic rather than the old weird Excel 4.0 macro language. It can be quirky, as you’ll see if you decide to use it. Despite its quirks, it can save you some time. It can serve as a reasonably good springboard for learning how to do it all yourself. And it can spare you the errors that inevitably occur when you roll your own forecasts.

The add-in has 19 different numeric and statistical analysis tools. If you lay out your data in the right way, you can point one of its tools at your data and get a fairly complete and usually correct analysis — including autocorrelation analyses, moving-average forecasts, exponential-smoothing forecasts, and regression forecasts. It does the hard work for you, and because it’s all precoded, you don’t need to worry so much about, say, getting a formula wrong.

Smoothing data

If you decide to use exponential smoothing to create your forecast, all you’ll need is your baseline of historic sales revenues. Each observation in the baseline should be from the same sort of forecast period — as often as not, revenue totals on a monthly basis.

You need no variable other than your sales results because, using smoothing, you’re going to use one period’s result to forecast the next — which is one reason you’ll use the Data Analysis add-in’s Correlation tool to determine the amount of autocorrelation in the baseline before you do the forecast. Substantial autocorrelation will tend to lead you toward using the Exponential Smoothing tool as your forecasting method — and it will help you determine what damping factor (or, equivalently, what smoothing constant) to use in developing your forecast.

Regression: It’s all about relationships

If you have available some variable in addition to sales revenues or units sold, and you suspect that it’s strongly related to the sales results, you should take a closer look at the relationship.

Suppose you can lay your hands on historical data that shows — by year and month, say — the unit price that you’ve charged and the number of units you’ve sold. If you’re interested in forecasting the number of units you’ll sell next month, the Data Analysis add-in’s Regression tool can ease your task.

The chart gives you a visual of what’s going on between the two variables: Unit Price and Units Sold.

(In the figure, the appearance of the chart has been modified as the Regression tool creates it to make it easier to gauge the relationship between price and volume.)

With this baseline, including unit price and units sold, your interest doesn’t focus on revenues. After all, it’s pretty clear from the chart that the higher the unit price, the fewer the units sold — and that will tend to minimize the variation in quarterly revenue. Instead, this analysis speaks to production. If you know how you’ll set your unit price for next quarter, you can use the Regression tool to forecast the number of units you’ll sell next quarter. That forecast might well inform your Production department about how to allocate its resources.

By the way, Excel terms the solid line shown a trendline. When you see a trendline run from the upper left to the lower right, as shown, you know that the correlation between the two variables is negative (and in this case, the correlation between unit price and units sold is –0.57). A negative correlation means that the higher the level of one of the variables, the lower the corresponding value of the other variable. If the trendline runs from the lower left to the upper right, you know that the correlation is positive. A positive correlation means that lower values on one variable are associated with lower values on the other, and that higher values on one are associated with higher values on the other.