Chapter 1

A Forecasting Overview

IN THIS CHAPTER

Knowing the different methods of forecasting

Arranging your data in an order Excel can use

Getting acquainted with the Analysis ToolPak

Going it alone

A sales forecast is like a weather forecast: It’s an educated guess at what the future will bring. You can forecast all sorts of things — poppy-seed sales, stock market futures, the weather — in all sorts of ways: You can make your own best guess; you can compile and composite other people’s guesses; or you can forecast on the basis of wishful thinking.

Unfortunately, none of these options is truly acceptable. If you want to make better forecasts, you need to take advantage of some better options. And there are different ways to forecast, ways that have proven their accuracy over and over. They take a little more time to prepare than guessing does, but in the long run I’ve spent more time explaining bad guesses than doing the forecasts right in the first place.

Microsoft Excel was originally developed as a spreadsheet application, suited to figuring payment amounts, interest rates, account balances, and so on. But as Microsoft added more and more functions — for example, AVERAGE and TREND and inventory-management stuff — Excel became more of a multipurpose analyst than a single-purpose calculator.

Excel has the tools you need to make forecasts, whether you want to prepare something quick and dirty (and who doesn’t from time to time?) or something sophisticated enough for a boardroom presentation.

The tools are there. You just need to know which tool to choose for which situation and, of course, how to use it. You need to know how to arrange data for the tool. And you need to know how to interpret what the tool tells you — whether that tool’s a basic one or something more advanced.

Understanding Excel Forecasts

If you want to forecast the future — 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. (Part 1 of this book talks about forecast baselines and why they work.)

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. Later in this chapter, I show you more of the logic behind using moving averages. 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.

Getting the Data Ready

Which method of forecasting you use does make a difference, but regardless of your choice, in Excel you have to set up your baseline data in a particular way. Excel prefers it if your data is in the form of a table. In Part 2, I fill you in on how to arrange your data so that it best feeds your forecasts, but following is a quick overview.

Using tables

There’s nothing mysterious about an Excel table. A table is something very much like a database. Your Excel worksheet has columns and rows, and if you put a table there, you just need to manage three requirements:

• Keep different variables in different columns. For example, you can put sales dates in one column, sales amounts in another column, sales reps’ names in another, product lines in yet another.
• Keep different records in different rows. When it comes to recording sales information, keep different sales records in different rows. Put information about a sale that was made on January 15 in one row, and information about a sale made on January 16 in a different row.
• Put the names of the variables in the table’s first row. For example, you might put “Sales Date” in column A, “Revenue” in column B, “Sales Rep” in column C, and “Product” in column D.

Figure 1-1 shows a typical Excel table.

Why bother with tables? Because many Excel tools, including the ones you use to make forecasts, rely on tables. Charts — which help you visualize what’s going on with your sales — rely on tables. Pivot tables — which are the most powerful way you have for summarizing your sales results in Excel — rely heavily on tables. The Data Analysis add-in — a very useful way of making forecasts — relies on tables, too.

For years, Excel depended on an informal arrangement of data called a list. A list looked a lot like a table does now, with field names in its first row, followed by records. But a list did not have built-in properties such as record counts or filters or total rows or even a name. You had to take special steps to identify the number of rows and columns the list occupied.

In Excel 2007, Microsoft added tables as a new feature, and tables have all those things that lists lack. One aspect of tables is especially useful for sales forecasting. As time passes and you get more information about sales figures,...