Using the Excel Data Analysis Add-in with Tables - dummies

Using the Excel Data Analysis Add-in with Tables

By Conrad Carlberg

The Data Analysis add-in (known in earlier versions of Excel as the Analysis ToolPak or ATP) helps you do statistical analyses of all sorts — and sales forecasting is definitely one sort of statistical analysis.

An add-in contains Visual Basic code: a program, often 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 watch legislators making sausage.

You need to install the Data Analysis add-in on your computer from your Office installation CD or your download source. You’ll find it among the Add-Ins under Excel if you do a custom installation; normally the add-in will be installed automatically if you do a complete installation.

But installing the Data Analysis add-in on your computer doesn’t mean it’s installed in Excel. If you don’t see the words Data Analysis in the Analyze group of the Ribbon’s Data tab, then so far you might have 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, click the File tab.
  2. Choose Options from the navbar at the left of the Excel window.
  3. Choose Add-Ins from the navbar at the left of the Excel Options window. Click OK.
  4. Make sure that the Manage drop-down near the bottom of the Excel Options window contains Excel Add-ins. Click Go.
  5. The Add-ins dialog box appears. Make sure that the check box next to Analysis ToolPak (sic) is checked, and click OK.

Now you’ll find a new item, Data Analysis, in the Ribbon’s Data tab, in the Analyze group. Click that item to get at the add-in’s tools.

With the Data Analysis add-in 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 table. Do this:

  1. Click the Ribbon’s Data tab and click Data Analysis in the Analyze group.
    Not all the analysis tools are useful for doing forecasting. The three best are Exponential Smoothing, Moving Average, and Regression, but you might want to use others for other purposes.

    The Data Analysis dialog box appears.

  2. Scroll down the Data Analysis list box and click Moving Average, and then click OK.
    Using a Data Analysis tool is easiest if the worksheet that contains the table is active — but this isn’t required.

    The Moving Average dialog box appears.

  3. Click in the Input Range field and, using your mouse pointer, drag through the Revenues part of your table.
  4. Include the column label in the Input Range. If you do so, select the Labels in First Row check box.
  5. Click in the Interval field and enter the number of months (or whatever date period your table uses) you want to base your moving average on.
    For example, to base your moving average on a three-month interval, enter 3. If your table measures dates in weeks and you want to base the analysis on a two-week 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. If you want to see a chart of the moving average, select the Chart Output check box.
    Don’t make me beg here. Select the check box.
  8. Click OK.

You’ll see the results shown here.

This moving average is based on three intervals — that is, each average consists of three months.

Notice how the moving average smoothes out the individual observations. This tends to suppress the noise (the random variation in each of your table’s records) and to emphasize the signal (the main direction of the baseline).

Also notice how much easier it is to see what’s going on when you look at the chart than when you just look at the table. The lesson: Chart your baselines.