October 30, 2013
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
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:
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
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
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.
IBM SPSS ModelerTime SeriesTime Series ForecastForecast RevenuesAnalyticsPredictiveSPSSTM1Cognos BI