The Microsoft Analysis ToolPak (ATP) helps you do statistical analyses of all sorts — and Excel sales forecasting is definitely one sort of statistical analysis. The ATP is an add-in. An add-in is a hidden workbook containing Visual Basic code: a program written in a version of BASIC that Excel can run. It's password protected, locked up so that you don't get to look at the code itself. That's okay — you probably wouldn't want to see it any more than you'd want to look at legislation or sausage making.
Installing the ATP
 | You need to install the ATP on your computer from your Office installation CD. You'll find it among the Add-Ins under Excel if you do a custom installation, or it will be installed automatically if you do a complete installation. |
But installing the ATP on your computer doesn't mean it's installed in Excel; so far you've just installed it on your hard drive. As with all add-ins, you need to bring it to Excel's attention. To do so, follow these steps:
1. In Excel, choose Tools --> Add-Ins.
2. Select the check box for Analysis ToolPak.
If you don't see that check box, you'll have to rerun the Excel installation routine from the CD and make sure you install the ATP.
3. Click OK.
Now you'll find a new menu item, Data Analysis, in Excel's Tools menu. Click that item to get at the ATP's tools.
With the ATP installed both on your computer and in Excel, you'll find 19 analysis tools. Suppose you want to wield the Moving Average tool on your list. Do this:
1. Choose Tools --> Data Analysis.
The Data Analysis dialog box appears.
2. Scroll down the Analysis Tools list box and click Moving Average, and then click OK.
The Moving Average dialog box appears.
3. Click in the Input Range field and, using your mouse pointer, drag through the Revenue part of your list.
4. If you include the column label in the Input Range, select the Labels in First Row check box.
5. Click in the Interval field and enter the number of months you want to base your moving average on.
For example, to base your moving average on a three-month interval, enter 3. If you want to base it on a two-month interval, enter 2.
6. Click in the Output Range field, and then click the worksheet cell where you want the results to start showing up.
7. To view a chart of the moving average, select the Chart Output check box.
8. Click OK.
Your results appear.
You get #N/A values at the start of a moving average forecast because at the start of the sequence there are never enough values to complete the average.
Notice how the moving average smoothes out the individual observations. This tends to suppress the noise (the random variation in each of your list's records) and to emphasize the sound (the main direction of the baseline).
Also notice how much easier it's to see what's going on when you look at the chart than when you just look at the list. The lesson: Chart your baselines.
There's more, much more, to moving averages, but now you know the basics of getting and charting a moving average from a list.
Avoiding the Analysis ToolPak's Traps
For years, some of the ATP's tools (for example, the Regression tool) have confused the input range with the output range. If you're going to forecast by means of regression you need at least two variables: a predictor variable (such as date or advertising dollars) and a predicted variable (in this context, something such as sales revenue or unit sales).
The ATP's Regression tool refers to the predictor variable's values as the Input X Range, and the predicted variable's values as the Input Y Range.
Now, suppose you do this:
1. Choose Tools --> Data Analysis, click on Regression and then click OK.
2. Click in the Input Y Range field, and then drag through something such as your sales revenue values on the worksheet.
3. Click in the Input X Range box, and then drag through something such as the date values on the worksheet.
Notice that the default option for the Output Range is New Worksheet Ply.
If you now override the default option and click the Output Range option button (which lets you put the Regression output on the same sheet with your list), the focus snaps back to Input Y Range. If you then click in some worksheet cell, that cell becomes the Input Y Range. Because you normally want to use an empty range for the output, you certainly won't select a cell with input values in it. So you choose an empty cell, and because of the change in focus, that cell becomes the Input Y Range.
In other words, the ATP is trying to get you to choose a range, or cell, without any data in it to supply your Input Y Range — that is, the predicted values.
If you're not aware of what's going on, this can cost you time and unnecessary skull sweat. Unfortunately, there's no good solution — remember, you can't open the code that drives the ATP — other than to be aware that it happens, and to know you have to click the Output Range option button and then its associated field again to reset the focus.
 | Several tools in the ATP have this problem. Take care when you're identifying an output range for an ATP tool. |
The other main problem with the ATP is that its output is often static. The Regression tool, for example, puts calculated values in cells rather than formulas that can recalculate when the inputs change. If you get new or changed input values, you'll have to rerun the tool to get the revised results.
Other tools, such as Moving Average and Exponential Smoothing, report their results as formulas, so they'll recalculate if you change the inputs. If you have new values for these tools to use (for example, your input range changes from A1:A20 to A1:A25), you'll need to reset the input range address; but if you're just revising an earlier value, the formulas will recalculate and the charts will redraw without any extra effort on your part.
| 
