Does TM1 need Excel?

YES! Do you agree? If so, read no further :) !

But if you do not agree, and believe that it is time for TM1 to grow up, move on and shake off its spreadsheet roots, then please read on!

It is well known that the over use of spreadsheets brings substantial risks. Famous recent examples range from scandals in high finance (http://www.forbes.com/sites/timworstall/2013/02/13/microsofts-excel-might-be-the-most-dangerous-software-on-the-planet/) to discredited economic analyses (http://www.nytimes.com/2013/04/19/opinion/krugman-the-excel-depression.html?_r=0).

The root causes are twofold: first, Excel is not a data management tool (despite its widespread use as one), and second, poor auditability - the fact that it is very difficult to check the calculations in a complex spreadsheet model for errors (even if you created the model!).

Why then does Excel persist? Because, despite these serious risks, it solves a very real business problem better than anything else out there. Doing business is ultimately a human (read: unpredictable!) activity, and organizations have to react quickly to unexpected pressures that can come from within (we're buying our biggest competitor!) and without (the price of our main raw material has suddenly gone up by 30% in commodity markets!).

The article (http://www.theiia.org/intAuditor/itaudit/archives/2006/january/the-role-of-spreadsheets-in-todays-corporate-climate/) says it well: .. it is rare for one software application to support an organization's entire gamut of finance-related activities and needs. In addition, because organizations change over time, gaps exist between a company's business needs and a system's capacity to fulfill them. Consequently, many companies rely on spreadsheets to help fill these gaps.

In other words, there is no way even the best IT organization would be able to churn out the what-if scenarios and other financial models at the speed at which at CFO or CEO might need them, even if there were a clear specification of requirements - which most often there aren't. But a talented financial analyst armed with Excel can figure out the questions and get answers together in just hours or days!

Which brings us to TM1's most basic value proposition: TM1 solves the data management problem of Excel, and much of the auditability problem as well. TM1, at its core, is a client server multi-dimensional database that is tightly integrated with the spreadsheet. The ease with which a multi-dimensional "cell" concept maps into Excel's two (and a half!) dimensional one, makes it intuitive for Excel users, and easy to produce familiar formatted reports. On the auditability front, the ability to define hierarchies, among other calculations, means that there can be a single centralized definition of items like "Gross Margin", "Western Region", "Acme Product Lines" and (using TM1 rules) "translated into USD".

The combination of Excel with TM1 is therefore a very powerful one. A financial analyst able to master Excel will typically be able to develop advanced skills with TM1 as well, ending up with:

1. an agile, flexible system that can adjust in the face of changing business imperatives,
2. that has a secure, controlled central repository for data and calculations,
3. that can be managed and owned by financial analysts

But can’t TM1 do all this without Excel? After all, now we have Application Servers to drive portals, and off-line data entry through Cognos Insight, and enterprise reporting through Cognos Business Intelligence (”Cognos BI”) integration. What does Excel bring to the table?

To answer that, let’s go back to the earlier question of why Excel persists: it solves a very real business problem better than anything else out there. Although TM1 can be applied to many kinds of data, it hits a particular sweet spot in the intertwined areas of financial reporting, financial consolidation, planning, forecasting and customer/product profitability modeling. The people who care about these things are financial analysts. And for reasons we have already explained, financial analysts need a tool with Excel’s flexibility. The addition of TM1 improves Excel, but only if all the benefits of Excel continue to be fully realized.

TM1 therefore needs Excel in order to deliver the best of all worlds for financial analytical applications, like the ones listed above:

• Reporting. There is no tool out there that can produce formatted financial reports the way a spreadsheet can. Excel is not a general purpose reporting tool the way Cognos BI is, but in this specialized area, Excel is superior. Not only have generations of finance and accounting executives been conditioned to expect the look that comes from placing data in a perfectly aligned spreadsheet grid, but a financial analyst can easily – with no programming knowledge – augment the data presented with additional formulas (like ratios), commentary, and, in a pinch, additional new data that is not yet in the database. TM1 therefore needs Excel in order to deliver formatted financial reports with the requisite look and flexibility.
• Modeling. A spreadsheet is essentially a programming environment for people who are not trained as programmers (see http://www.miramontes.com/writing/spreadsheet-eup/ for more on what we mean by this). The people who need to build a model of a business, in order to support good decision making, are typically experts in business and finance, and not experts in technology. Model building is a creative process that combines a deep understanding of the business subject matter with an exploration of possible solution sets. It is most successful when the business user is heavily engaged and ideally drives the process. In an Excel with TM1 application, models are ultimately pushed to the controlled environment of TM1, but Excel remains the best tool for that important creative engagement for early prototyping and development. Once there is a working, tested example in Excel, the formulas serve as a perfect detailed specification for how it can be modeled in TM1.
• Data submission. Excel is the environment in which websheets (for reporting and data collection) are designed. A creative Excel user can build rich grid based data capture screens, leveraging the formulas and formatting of Excel. For example, conditional formatting can be used to do traffic lighting to to highlight an invalid entry on the fly. Excel can also calculate values that are ultimately sent to TM1 – this gives you the ability to, for example, populate cells with calculated values that can then be overwritten if desired, which is sometimes a desired behavior. On a different front, there may be data – for example a historical budget - that needs to be loaded into TM1 from pre-existing spreadsheets. TM1 DBS (database SEND) formulas provide an agile way to deal with a one-off situation like this. TM1 DBS formulas can also be used creatively to build flexible administrative and maintenance user interfaces, for example to calculate and post recurring journal entries.
• User Interface Separation. It is a well known principle of software design, and for good reason, that the way in which data is stored or calculated should be separated from how it is presented to end users. There is a lot of literature on this subject, for example http://martinfowler.com/ieeeSoftware/separation.pdf. Using Excel as the primary user interface design tool, reports and input templates can flexibly combine data from multiple TM1 cubes in one place. It is important to understand that TM1 is first and foremost a database, and cube views are a way of querying data from the cubes, which is not the same as the ultimate presentation to the end user. One major deficiency of the early versions of TM1 Contributor (starting with TM1 9.5) was that the cube view was treated as the only user interface option, which meant that efficient cube design for performance and to support complex calculations was not possible when it was at odds with what end users wanted to see. At time of writing, only TM1’s Excel and websheet interfaces provide the possibility of full user interface separation. Until this is fully addressed in newer interfaces like Cognos Insight and Cognos BI, TM1 needs Excel for this purpose too.

In a perfect world there would be no need to ever create an ad-hoc model on the fly, or deliver a new report in an hour. But we live in a real world, not a perfect one (fortunately!) and financial analysts on the front lines cannot do their jobs without the ability to make something up at short notice. TM1 adds much needed structure and control to Excel’s freewheeling world, but it cannot be done at the cost of the flexibility that Excel brings to the table. The best scenario based on tried and true technologies available today is the TM1 with Excel combination owned by engaged finance and business users, but managed through a well defined business process with the right balance of control and flexibility.

We would welcome your comments: do let us know what you think!