3 Methods of Excel Forecasts - dummies

By Conrad Carlberg

If you want to forecast the future in Excel — next quarter’s sales, for example — you need to get a handle on what’s happened in the past. So you always start with what’s called a baseline (that is, past history — how many poppy seeds a company sold during each of the last ten years, where the market futures wound up each of the last 12 months, what the daily high temperature was year-to-date).

Unless you’re going to just roll the dice and make a guess, you need a baseline for a forecast. Today follows yesterday. What happens tomorrow generally follows the pattern of what happened today, last week, last month, last quarter, last year. If you look at what’s already happened, you’re taking a solid step toward forecasting what’s going to happen next.

An Excel forecast isn’t any different from forecasts you make with a specialized forecasting program. But Excel is particularly useful for making sales forecasts, for a variety of reasons:

  • You often have sales history recorded in an Excel worksheet. When you already keep your sales history in Excel, basing your forecast on the existing sales history is easy — you’ve already got your hands on it.
  • Excel’s charting features make it much easier to visualize what’s going on in your sales history and how that history defines your forecasts.
  • Excel has tools (found in what’s called the Data Analysis add-in) that make generating forecasts easier. You still have to know what you’re doing and what the tools are doing — you don’t want to just jam the numbers through some analysis tool and take the result at face value, without understanding what the tool’s up to. But that’s what this book is here for.
  • You can take more control over how the forecast is created by skipping the Data Analysis add-in’s forecasting tools and entering the formulas yourself. As you get more experience with forecasting, you’ll probably find yourself doing that more and more.

You can choose from several different forecasting methods, and it’s here that judgment begins. The three most frequently used methods, in no special order, are moving averages, exponential smoothing, and regression.

Method #1: Moving averages

Moving averages may be your best choice if you have no source of information other than sales history — but you do need to know your baseline sales history. The underlying idea is that market forces push your sales up or down. By averaging your sales results from month to month, quarter to quarter, or year to year, you can get a better idea of the longer-term trend that’s influencing your sales results.

For example, you find the average sales results of the last three months of last year — October, November, and December. Then you find the average of the next three-month period — November, December, and January (and then December, January, and February; and so on). Now you’re getting an idea of the general direction that your sales are taking. The averaging process evens out the bumps you get from discouraging economic news or temporary boomlets.

Method #2: Exponential smoothing

Exponential smoothing is closely related to moving averages. Just as with moving averages, exponential smoothing uses past history to forecast the future. You use what happened last week, last month, and last year to forecast what will happen next week, next month, or next year.

The difference is that when you use smoothing, you take into account how bad your previous forecast was — that is, you admit that the forecast was a little screwed up. (Get used to that — it happens.) The nice thing about exponential smoothing is that you take the error in your last forecast and use that error, so you hope, to improve your next forecast.

If your last forecast was too low, exponential smoothing kicks your next forecast up. If your last forecast was too high, exponential smoothing kicks the next one down.

The basic idea is that exponential smoothing corrects your next forecast in a way that would have made your prior forecast a better one. That’s a good idea, and it usually works well.

Method #3: Regression

When you use regression to make a forecast, you’re relying on one variable to predict another. For example, when the Federal Reserve raises short-term interest rates, you might rely on that variable to forecast what’s going to happen to bond prices or the cost of mortgages. In contrast to moving averages or exponential smoothing, regression relies on a different variable to tell you what’s likely to happen next — something other than your own sales history.