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.

This tutorial will demonstrate how to install the Data Analysis Toolpak add-in in Excel for both Mac and PC. For PC Users: Click on the File tab on the top left, then select Options. Where it says Manage at the bottom, select Excel Add-ins from the drop-down menu and click Go. Click Add-Ins, and then in the box to the right, select the Analysis ToolPak check box, and then click GO. In the Add-ins box that opens, make sure the Analysis Toolpak is checked and then click on OK. After you load the Analysis ToolPak, the Data Analysis command is available under the Data tab. Excel for the Mac.

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.

Excel Data Analysis Add On

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.

   The Data Analysis dialog box appears.

2. Scroll down the Data Analysis 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 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.

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).

How To Add In Analysis Toolpak In Excel On A Mac

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.