Forecast Video Tutorial (Data Mining Table Analysis Tool)

Video Summary

In this tutorial we will learn how to use the Forecast Table Analysis Tool for Excel 2007.

Video Transcript


Hi. My name is Mary Brennan. I’m a technical writer for Microsoft SQL Server.

The Forecast tool uses the Microsoft time series algorithm to predict future values based on trends in existing time series data. For example, you can forecast what annual sales will be two years from now, based on the current conditions and other factors in your data. The tool appends the forecast values as new rows to the table selected for forecasting. It also creates a new worksheet to display the historical and forecasted evolution of the series.

In this tutorial we will forecast the bike buying potential of a new set of customer data given the characteristics of current bike buyers.

Both the Forecast tool (in the Table Analysis Tools for Excel) and the Forecast wizard (in the Data Mining Client for Excel) use the Microsoft Time Series algorithm. However, the Forecast tool is easier to use because it automatically configures the algorithm to use the settings that are best for your data.

The Wizard

To begin,

  1. Select the Forecasting tab and click anywhere inside the table to activate the Table Analysis Tools.

  2. Under the Table Tools menu select the Analyze tab to open the Table Analysis Tools ribbon.

  3. Click Forecast to launch the wizard.

Let’s forecast sales in Europe and North America. Remember that this tool will only work on continuous numeric data, such as currency or other numbers.

If possible, your data should also include a column that contains a series of times or dates. In this example, we have Year/Month. If your data does not have date or time data, you can use a numeric series (1, 2, 3…) instead. However, values in the series column must be unique. An error occurs if the Forecast tool finds duplicate values in the series column.

Something else to keep in mind, you cannot use the Forecast tool to predict a date. Although an error might not occur, this algorithm is not designed to use dates as predictable values.

  1. By looking at our data, we can tell that our time series is based on year and month. Let’s extend the date series to the end of the year.

  2. If we want the Year/Month labels to be updated, we should specify a Time stamp.

  3. We’ll leave Periodicity of data as <detect automatically>.

  4. Click Run. The wizard adds the predictions to the source table, and creates a Forecasting report in a new worksheet.

The Reports

The Forecasting report automatically displays. At the same time, the new predictions are appended, in a new column, to the end of the source data table.

The new worksheet contains a line graph that shows the historical trends and plots the forecasted values. The historical data is shown as a solid line and the forecasted data as a dotted line. You can click a point on the line to see the forecasted value.

In some cases, the forecast may not have as many time slices as requested. This usually means that data was insufficient to enable the algorithm to forecast that far into the future. The Forecast tool will only make predictions that meet a minimum probability threshold.

Notice that there are no labels for the predicted values. We can add these on the Forecasting worksheet.

  1. Open the Forecasting worksheet.

  2. Scroll down to view the new predictions (which are highlighted).

  3. Select the values in B40 until the end of the predictions.

  4. Use the Fill, Series function to extend the time stamp column.

  5. Select Columns, Trend, and click OK.

  6. Back on the Forecasting worksheet, we see the new time stamp axis labels.


This concludes the Forecast video tutorial. For additional help with the Table Analysis Tools, I recommend viewing the other Table Analysis Tools video tutorials and the Help documentation included with the Data Mining Add-ins. Thank you for viewing this tutorial.