Thursday, September 15, 2005

OLAP and Planning

My name is Tony and I'm a planner. There I said it. Six years ago I was describing the work I was doing to someone and he said, "Oh, you're a planner." I was shocked to be called such a name. Today I am out of the closet.

I am not a planner though in the mold that most organizations think of planners. In many places, planning is a combination of very mechanical rollups and dysfuctional negotiations; collect sandbagged data, add it all together, twist arms until people go back to what they were willing to live with, repeat yearly. This type of planning adds little if any value, but is very common.

In many places, even the adding up of the data is extremely painful. We used to use Excel for adding up all our data. I have no problem with Excel, when used as designed. Once you start to treat it as a database, all kinds of bad things can happen.

First, Excel has capacity limitations. The tool I had built in Excel had to contain 500 assets, 20 to 50 years looking forward of data, and about 50 accounts. That's a minimum of 500,000 pieces of data. Also, when trying to do some of the slicing and dicing that is normally required, e.g. business unit earnings profiles, you are really analyzing along multiple dimensions. At some point, Excel starts to choke on some combination of the dimensionality and the sheer volume of data.

As an example, last year when I was building the model, it had reached about 35 MB. I copied a formula across one of my 500 by 20 arrays and Excel just went off and did something for about an hour. When it came back to my control, a few things happened: Trace dependents and precedents no longer worked, the calculate sign would not go out even after recalculation, it receclulated the entire spreadsheet every time I changed something, and it exploded to over 100 MB. In addition, any changes would make the model unstable sometimes leading to calculation errors. On top of all of that, the data collection process was deadly.

We collected a total of about 1500 templates from our business units. These needed to be linked in to a master spreadsheet and update links periodically. If even one value changed, we would have to go through a process of opening files and updating links that took about an hour.

I have digressed a bit here, but the point of all this is that even things that should be very simple--adding a bunch of numbers--end up taking a lot of time because of all the data management that happens behind the scenes. This typically leaves little time for "real" analysis.
So, because rollups will always be necessary, it is important to make the whole rollup and data management side as painless as possible. For this we need something stronger than Excel. This is where OLAP comes in.

OLAP stands for OnLine Analytic Processing. In a nutshell, if it is FASMI, Fast Analysis of Shared Multidimensional Information, it is an OLAP tool. The OLAP Report goes into a lot of detail about what this means, but in my mind it is a natural evolution of database technology.

Our particular implementation uses Outlooksoft CPM as the main vendor of our OLAP tools. They in turn are built on Microsoft Analysis Services and SQL server. Our intital implementation was definitely an improvement over Excel, and with the learnigns from our first planning cycle, I am confident that the next pass will be even better. This is a real step-change in our planning process--a platform on which to build.

Now that we have a platform that permanently elevates our game, where do we go from here?

There are a few paths. One is to improve the quality of our forecasts. We are plagued by the dreaded hockey stick. Each plan cycle shifts projects in time and increases capex. Another is to develop standards around a probabilistic plan. Strategic Decisions Group has done some interesting work on planning processes that incorporate probabilistic planning as well as portfolio analysis in capital allocation. Finally, other types of portfolio analysis, such as that advocated by John Howell of Portfolio Decisions for strategy development, or a more tactical approach as advocated by Steve Rasey of WiserWays hold potential for better strategy development as well as more effective, focused capital allocation.

1 comment:

Anonymous said...

Have you tried using FarPoint Spread for spreadsheet applications? Let 'em know what you can or can't do with Excel. See them at