Real-Time Collaborative Planning in ExcelReal-Time Collaborative Planning in Excel

Are you experiencing "spreadsheet hell" in financial planning and budgeting but don't want to sacrifice your investment in the Excel interface? Are you an established Applix customer looking to add control and workflow to your existing TM1-based applications used for planning and budgeting? If you answered "yes" to either question, you'd do well to consider TM1 Planning Manager.

Financial planning often consumes weeks if not months, occupies numerous resources across departments and regions, involves several iterations and invariably comes around again before anyone's ready for it.

Excel is by far the most commonly used BI and financial analysis tool, and it's the mainstay for planning and forecasting in many finance departments. However, planning and forecasting in Excel becomes more labor intensive and error prone as complexities and the number of collaborators multiply.

TM1 Planning Manager streamlines the planning process, using the familiar Excel interface but reducing errors common to linked spreadsheets. Built using components of the TM1 business intelligence product set (see the screenshot below), Planning Manager provides a structure in which organizations can create plans, assign responsibility and monitor the progress of activities through the planning cycle.

The core of the Applix product set is the TM1 Server, an OLAP server that uses a multicube architecture in which a database is defined as a collection of cubes. Measures that share the same dimensions or keys are defined in the same cube. Cubes can share dimensions with other cubes. Thus the Product dimension can be common to the Sales Analysis and Financial Reporting cubes.

The TM1 Server calls dimension members "elements" and lets you define them as simple (leaf level), consolidated (dynamically aggregated) or string (text data). In this way, a cube can hold both numeric and text data, providing the ability to enter annotations alongside numeric data. In addition, you can classify dimension elements with attributes.

You arrange dimension elements into hierarchies, and a dimension can have multiple hierarchies. The Organization dimension, for example, could be viewed or aggregated by both Geography and Legal Entity. Ragged hierarchies, in which descendants need not be at the same level, are also supported. Thus, the Geography hierarchy for the Organization dimension might break down the United States by Region and then by Office, but Europe just by Country.

After arranging dimensions in hierarchies, you then define aggregation rules for each dimension element, simple or weighted. In a Sales Analysis cube, for instance, Revenue and Cost of Sales would be given aggregation rules of "+1" and "-1" respectively. Margin would be the aggregation of Revenue and Cost of Sales.

Two different mechanisms are provided for calculations within a cube. Simple aggregations are performed using aggregation rules. More complex calculations can be defined as rules in the nonprocedural rules language. For example, in the Sales Analysis cube, Margin Percentage could be defined as Margin/Revenue*100.

Although very powerful, the rules language can be difficult to apply to complex calculation logic. Also, unlike some other OLAP servers, TM1 has no built-in intelligence for the Account and Time dimensions. To do time-series calculations such as year-to-date and year-over-year comparisons, you must use rules or subsets.

TM1 supports calculations between cubes, and you can use attributes to control behavior in these calculations. For instance, you can enter data in Local Currency and convert it to Reporting Currency, using an attribute to determine which exchange rates to apply for Income Statement (monthly average) and Balance Sheet (end of period) accounts.

Data security to the cell level, multiuser write access, and rollback and audit capabilities bolster TM1 Server's suitability for financial planning and analysis. What differentiates TM1 Server from other OLAP servers is the ability to operate entirely in RAM without having to precalculate or preaggregate anything. TM1 performs all calculations dynamically, the results cached in memory and immediately available to all users. Calculations are automatically cleared from memory if underlying data changes. TM1's real-time calculation capability eliminates "database explosion" and potentially long calculation and aggregation times — the bane of most OLAP servers.

The TM1 Perspectives client interface operates as an Excel add-in and includes development, administrative and cube viewing functionality via the Server Explorer component. The In-Spreadsheet Browser (accessed from the TM1 add-in menu and implemented as an ActiveX control) provides a flexible user interface to view and edit TM1 data directly from Excel; it's at least as functional as other OLAP Server Excel interfaces.

TM1 Planning Manager exploits Excel's ease of use while ensuring data consistency by complementing the "single version of the truth" stored in a central TM1 database. The Planning Manager interface operates entirely from within Excel, and the extent of integration differentiates it from similar products from Hyperion and Cognos, each of which uses its own application interface in addition to Excel. With Planning Manager, users don't have to learn a new interface. OutlookSoft's product is well integrated with Excel, too; however, it's based on Microsoft Analysis Services, which doesn't offer nonprocedural rules language. Thus, TM1 is better suited to applications that require complex financial models.

Planning administrators (typically the power users in the finance department responsible for managing the planning cycle) call wizards from the Planning Manager Administrative Console (see the diagram on the left) to build and modify the "version dimension," the "task and review hierarchy" and Excel worksheets. The version dimension holds the names of the multiple versions of a budget that may be created during an iterative planning cycle. The task and review hierarchy mimics the flow of information through the planning cycle by associating TM1 users to the tasks assigned to them. Elements from more than one dimension can be combined to map the tasks required to complete the planning cycle. That is, you could combine the department dimension with the business unit dimension to create tasks for departments within business units.

Tasks can be delegated and passed down the hierarchy. For example, the European Sales Manager would delegate the task of creating a Sales Forecast to each country manager. When complete, the country Sales Forecast would be submitted back to the delegator, who can then review it and choose to accept or reject it. The planning administrator associates Excel worksheets to tasks using attributes, and business users then input and review plans on these worksheets. The Planning Manager Excel tool bar provides workflow capabilities.

Planning Manager can be installed on existing TM1 applications provided that there's a dimension to hold the names of budget versions. This flexibility lets users work with the data model and worksheets they're familiar with. New customers can install a Planning Manager template that has 12 dimensions (including Time) and a chart of accounts with 42 elements. This template can be modified to reflect the company's own chart of accounts.

Planning Manager is preconfigured with a customizable set of workflow actions and statuses, and it provides version tracking and e-mail notification as plans progress through the various levels of approval. E-mail messages are triggered when you create a new budget version or execute a task action from the Planning Manager toolbar.

Provided that you can define the structure and tasks that make up your planning cycle, Applix TM1 Planning Manager can improve the efficiency and accuracy of your process without sacrificing Excel's flexibility and ease of use.

• Planning Manager server and client components start at $15,000, for access to one TM1 server. A required TM1 Server with Perspectives starts at $25,000. Contact Applix at 1-800-8APPLIX or www.applix.com.

PAUL DEAN is an independent consultant providing OLAP product evaluation and selection, implementation and training services.