How to use SPSS Modeler and Time Series Algorithms to Forecast Revenues

A planning exercise typically involves a batch of spreadsheets, which probably look chaotic and consumes a lot of time to maintain. A much better, more efficient and optimised option is to use specialised software for this work, like TM1. Using TM1, speeds up the process considerably and the planning process is a lot more efficient. But still you have to rely to some arbitrary decisions regarding next years budget, like an estimation, from the business analyst, of what the target might be. An evolution of this would be to use a predictive tool, like SPSS Modeler, along side TM1. This way you can get data from TM1 into SPSS Modeler and use SPSS Modeler to do the forecasting. When this is done you push back the forecasts from SPSS modeller to TM1. So, this time we are going to demonstrate how to do a simple time series forecast of revenues.

Let’s talk about the planning process here. We have a retail company that has 3 sales channels:

  • Direct
  • Internet
  • Retail
The budgeting process here is very simple, for demonstration purposes, and involves the forecast of revenues for the 3 channels. We have actuals for 2009,2010, 2011 and we are going to forecast revenues for 2012. To do the forecast we are going to use a Time Series model.

Here is a report that shows the actual revenues:


First step is to get data from TM1 into SPSS Modeler. We have several options to do that. We can export the data from TM1 into a text file or we can connect directly to the cube via IBM Cognos (framework manager) package or we can create a report with report studio that contains all the data that we need and use that report as our data source. In this case we used the latter. Here is an overview of the whole process in SPSS Modeler.



The second step is to transform the data as needed by the time series algorithm. This involves aggregating and pivoting the data and creating time intervals. The Time Series algorithm in SPSS Modeler has an automated procedure to create models that in most of the cases works well. So we will use the expert modeller (the automated procedure) that will try to fit various models and pick the best. Here are the results:

It makes sense that in 2 of the time series SPSS used the same model since the revenues in the channels seem to follow the same trend and seasonal cycle. The models seem to be adequate enough and by investigating the residuals there do not seem to be any trends left.

So we can use these models to forecast the revenues. But before creating the final report we need some additional steps. We need to transform the results in friendly way for TM1 and then export the forecasts. For export we again have various options. We can export the forecasts into a text file or to a database or publish a package to IBM Cognos portal. In this case we exported the results in a text file and imported them in the TM1 cube. The benefits are better forecasts without any arbitrary decisions involved, in an automated process. You can schedule everything to run in an automated way and you can have a rolling forecast every month without the need to do anything else.