It’s Getting Easier To Go Beyond Spreadsheets for Modeling
February 27, 2012

One trend in business software that’s still in its early stages but gathering momentum is the availability of modeling tools that fill the gap between desktop spreadsheets and enterprise systems. Granted this “early stage” has been under way for quite some time, but the technology has finally progressed to the point where I expect it to get increasing market traction. 

The temptation for business modelers and analysts is that it is very easy to create models using spreadsheets like Microsoft Excel. Tens of millions of people worldwide are trained in using spreadsheets, so it’s often a default choice. Desktop spreadsheets are handy because they make it simple for anyone to translate their concepts into a computer model. As someone who has done this for more than 30 years, I can attest that analysts skilled in using spreadsheets mentally frame business issues and relationships in a grid structure. It’s relatively easy, for instance, to create a dynamic integrated income statement, balance sheet and cash-flow model in a spreadsheet; it’s a laborious process to construct one using a relational or multidimensional database. Yet especially where complex calculations are used or where the models involve more than a few dimensions (more on this below), models created this way are error-prone and “brittle,” which means that they break quickly when someone tries to make a change to the original construction. 

In contrast, more sophisticated business intelligence tools or dedicated enterprise planning applications, which can produce more powerful and flexible models, have required formal training. Although some individuals and/or companies have been willing to make the investment in such training, the majority have not, opting to keep using spreadsheets. I suspect the main reason is that the amount of training required and the frustration that most spreadsheet jockeys encounter when changing to a new tool have been too great. As well, there is a network effect in reverse: In any organization where many or even just several people share a model, all must be trained in using a tool or its usefulness is substantially diminished. 

To be sure, the problem – and solutions to address it – is not new. For example, Essbase was developed in the 1980s partly to address the above issues. However, Essbase has been lightly adopted by purely business or financial analysts because of the training it requires. More recently, Microsoft has offered an Excel Server that can address some – but not all – of the shortcomings of shared desktop spreadsheets. (Note that for many companies this will require purchasing new versions of Microsoft Office and Microsoft SQL Server.) And the rise of model-building alternatives is part of a broader adoption of more powerful but easier-to-use alternatives that fit between Excel and enterprise systems. For example, BizNet Softwareoffers what I would call an enterprise spreadsheet for more sophisticated reporting on other business applications using its in-memory computing technology. 

So what is it that makes me see the barriers to going beyond spreadsheets for modeling beginning to fall? 

One reason is that organizations increasingly want more value from modeling and analyses. Spreadsheets are easy to set up, but as well as being error-prone and producing brittle models, they have other inherent flaws that limit their overall effectiveness when they are used to support repetitive and collaborative business functions. For example, they lack referential integrity, which means that adding rows and columns creates issues when multiple spreadsheets must be collated. Another is that they can readily handle two or three “dimensions” but grow exponentially harder to work with as modelers try to add more. Dimensions include time, corporate structure (divisions and business units), organizational structure (functions and roles), product lines, customers and currency, to name some of the more common ones, which obviously can overlap. Businesses are inherently multidimensional, so to be truly useful for assessing outcomes, choosing between options and making plans, models and analyses must be structured to reflect the various dimensions. Nor do spreadsheets compare well to in-memory computing, an increasingly popular technology that allows for more interactive interplay with models. This means, for example, being able to do detailed what-if analyses rapidly while in a business review meeting in order to determine what to do next about some opportunity or issue. Desktop spreadsheets seldom can do this interactively at a detailed level. 

To get more value from modeling and analyses requires changing the balance of the work that business analysts do. Today, analysts spend too much time on the mechanics of analytics and modeling and not enough on analyses and their implications for the business, as our analytics research shows. A main reason for this waste of time is the limitations of spreadsheets. 

The other reason I expect the barriers to change to fall is that the alternatives to spreadsheets are increasingly easier to learn and use. One example is Quantrix, which has been around for a decade and therefore was early to a market that has been slow to develop. It is one in the latest round of new tools that attempts to fill the gap between spreadsheets and enterprise BI and analytic applications. Quantrix requires training, but in my judgment, it’s not hard to pick up and not difficult for business analysts to adapt their spreadsheet skills to building models in this tool. Another example is Anaplan, which my colleague Mark Smith commented on. It is designed to replace spreadsheets in operational planning functions (such as sales operations or demand planning, to name just two) as well as in financial planning and budgeting. It, too, offers modeling capabilities that are more powerful and more flexible than spreadsheets yet not difficult for business analysts to learn. 

I believe a lack of awareness of what’s possible is a major barrier to analysts adopting more capable tools for modeling, forecasting and reviewing. As the number of products that address the inherent limitations of desktop spreadsheets increases, marketing efforts in this area are going to gain attention. Companies are going to realize that they can achieve greater awareness and better decision-making if they have a more effective approach to modeling and analysis. There will always be a need for desktop spreadsheets, which serve the needs of tens of millions of users daily. But these tools no longer need be a barrier to modelers and analysts being able to do a better job of doing what they are hired to do: model and analyze. 

Regards, 

Robert Kugel CFA – SVP of Research 


 

 Copyright © 2013 Ventana Research All Rights Reserved :: Privacy Statement :: Contact Us ::