Why engage with IBM TM1 FAP on top of your consolidation?

Grasp the opportunity to add flexible and powerful reporting functionality to your consolidation system and leverage its use in the context of planning, budgeting and forecasting

With this insight, we target to describe the additional functionality and reporting possibilities that you can bring to your Cognos Controller consolidation platform by activating the Cognos TM1 FAP (Financial Analytic Publisher) solution. Moreover, we will share some of our element61 best practises for maximizing the ease of use of this ‘add-on’.

This article is predominantly aimed towards a Consolidation and Controlling or finance-type audience.

From a relational consolidation model towards a multi-dimensional reporting model

To adequately deal with all the different aspects of a typical financial consolidation process, IBM’s solution Cognos Controller is spot on, both in the context of a multinational group with large number of reporting entities as in the context of a midsize- or even a small group, with only a limited number of reporting entities.

Typical challenges, such as online & real-time reporting, period’s roll forward, multi-currency reporting, recording of consolidation adjustments and calculation of consolidation eliminations to name some, are smoothly handled, as one may expect from a top tier consolidation solution.

In line with any ‘accounting-based’ process, the consolidation process holds a transactional approach in its nature. Data is decomposed into different layers of detail, which in the end add up to a consolidated position. As such one can distinct several ‘positions’ (or even transactions if you want) underneath any reported number. Typically, a balance sheet position will be composed out of underlying movements, an opening balance, one or more period specific movements (depreciation, disposal, capital increase, etc.) leading into the closing balance.  In the context of an amount reported by counterpart, such as intercompany sales, the underlying details are also related to the different counterpart transactions for the specific account, in some cases even operational specific information may be detailed, such as products, markets, business unit etc.

From a database perspective Cognos Controller approaches this ‘transactional’ nature of the consolidation process with a relational data model. As such data principally resides in tables with a row and column structure and a unique identifying key per row. This unique key may even be a technical one, and is as such not necessarily ‘visible’ at the reporting front end.

Whereas the relational approach is adequate for handling the ‘transactional’ consolidation process, it may well be a downside in offering a flexible reporting context to the end-users, specifically when these end-users tend to look for drill-down or slicing & dice enabled reports. To enable such reporting functionality, one preferably needs a multi-dimensionally organised dataset, often referred to as an OLAP (online analytical processing) cube (which refers to a multi-dimensional dataset). As such, when data is organized across multiple dimensions, this allows reporting to break-out any ‘consolidated’ data amount across one or several of its analytical axes, such as time, actuality, entity, department, product, currency, closing & contribution version or even journal number etc., available at a double click.

As TM1 FAP is a multi-dimensional reporting application offering real-time integration with Cognos Controller, it adds a highly valuable reporting layer which is neatly integrated with the consolidation platform.

Real time data processing, analysis and reporting

With TM1 FAP, IBM provides an out of the box solution for transferring all data, dimensional information (structures) and security definitions from Cognos Controller towards TM1 cubes. 

As a result, all information which resides in the Cognos Controller relational database, is transferred into a multidimensional cube within TM1.  As TM1 is a very powerful in-memory multi-dimensional database, this allows for real-time reporting, since every few seconds all information is synced between Cognos Controller and TM1.

Real-time reporting of consolidated numbers in TM1:

• Every Cognos Controller user finds back all Cognos Controller information within TM1, in line with the appropriate security profile as defined towards Cognos Controller, in real time
• Any data updates in Cognos Controller, are transferred in real time towards TM1 and are instantly ready for analysis. There is no need to create a data dump using excel retrieve functions.
• Upon maintenance of master data, such as adding new elements to the company structure etc., the new elements are immediately passed through towards the dimensions in TM1.  Any summations or calculated fields are instantly updated and available.  The same principle applies to adding accounts or any other structure updates.
• Upon ‘dimensional’ changes, reports will automatically update, there is no need to manually change the structure of any report.

This FAP functionality comes out of the box and is included within the regular Cognos Controller license. As such TM1 FAP offers a standard integrated reporting platform upon which one can further build, optimize and integrate reporting processes far beyond the context of legal consolidation. Integration of actual data with forward looking data sets, such as forecast or even strategic planning, as well as integration of the analytical information from the management reporting (such as product, customer or geographical information) into a consolidation view are frequently realized.

Solid CPM foundation

Repeatedly the availability of the consolidation system throughout all reporting units of the group, is ‘abused’, adding many different types of information request and reports onto the initial consolidation reporting context. As such the setup of the consolidation model is often stretched to and beyond its limits, as numerous additions are made to the original master data, creating ‘statistical’ or ‘dummy’ placeholders for reporting of different kinds of analytical information.

Very frequently, even as per today, and across all sizes and types of organisations, the budget collection process is still an Excel based exercise, which in the end, somewhere and somehow, needs to be integrated into the consolidation tool and/or reporting platform, for purpose of a central data storage and/or as the basis for comparable reporting between different actualities of data (actual – forecast – budget). For budget reporting purposes, different types of reports, such as BU-, Cost Centre- or Management Reports, Statutory or legal Reports, different versions of the Forecasts or Budget data, are retrieved in Excel and more frequently than not, get altered in different (local) versions of the original excel report, initiating all the know pitfalls around abusing Excel as a reporting ‘database’.

As TM1 offers a seamless integration with Excel as a front end, while maintaining the central repository, it combines the best of both worlds and can facilitate integrated reporting processes.

Where the FAP mechanism provides two TM1 cubes containing all the information from Cognos Controller, it is common practise to make use of the cubes to start building additional TM1 reports for reporting and analysis on consolidated data or define other reporting needs such as simulations or allocations, or build planning or forecasting applications.

Reporting for Consolidation and beyond

When deploying TM1 FAP onto Cognos Controller, it is best practise to elaborate the default cubes that are made available as they include too much ‘dimensional’ information in one place to allow fluid reporting. Therefore, we advise to enrich the default content with additional reporting cube(s) that favour the typical reporting queries for specific user-groups. As such we typically distinct a more ‘controlling’ oriented reporting cube from a typical ‘consolidation’ reporting since both teams commonly differ their analysis both in content as approach.

Where a financial consolidation analysis is often driven from a reconciliation perspective, ensuring the correct, complete and consistent processing of data, the controlling data exploration will often look at data from an operational rather than a financial perspective, with a need for additional analytical information in the context of a trend and or variance analysis.

Unquestionably the dimensionality within each of those datasets differs, the comparatives for the datasets are different, as well as the required calculations. Where a consolidation analysis will benefit from precision towards the origin of the data (closing version, contribution version, journal number, etc.) from a controlling perspective potentially a cost centre or region identification may be more relevant. Where a consolidation analysis might benefit from a comparison against prior month or year-end, the controlling request may want to see periodic data against a 12-month (rolling) time series at constant (budget) rates.

Concluding, the above context, and much more, is easily addressed in a proper dimensional reporting model, whereas the consolidation tool tackles the more financial complexity. The combination of both in an integrated setup, is a best of both worlds solution, provided of course that from the different angles, the setup is correctly realized and is matching the company-specific business requirements and reporting characteristics.

Some more technical insights

To make all Cognos Controller data available in TM1, the FAP mechanism maintains and feeds two cubes containing 18 dimensions

• FAPConsMonthlyCompanyDetails
• FAPConsMonthlyConsolidatedGroups. 

These two default cubes should be considered as a staging area, containing the full information from Cognos Controller.  To make this information available for ‘consumption’ and easier to work with, it is best practice to create functional cubes on top of them where several dimensions should be left out or can be reorganised. Information around a specific Counter Company or a detailed Journals id, may not be required in a cube serving the annual report, or monthly management or Board reporting.  As such, these dimensions can be left out for purposes of such a reporting. Same ratio may apply to the reporting domain of personnel and headcount, where also the accounts structure can be limited. Those accounts that are not linked to payroll or headcount may be skipped as you can see in figure1.  These derived cubes have a direct link with the default FAP cubes and thus are always up to date with the latest data. 

Figure 1: tweaking the default dimensionality

In addition to the above, summations of accounts or calculation of variances between Actual and budget (AC – BU) or even KPI’s can be added in the reporting cubes, making use of the power and flexibility of TM1 rather than overloading Cognos Controller with calculations which are too complex. As an example a KPI cube can be targeted centralising the reporting and monitoring of KPI’s as :

• CETURN - Funds Turn
• DAYDIO - DIO (Days Inventory Outstanding)
• DAYDOP - DOP
• DAYDPO - DPO (Days Payable Outstanding )
• DAYDSO - DSO (Days Sales Outstanding )
• OCETURN - Operating Funds Turn
• PercCEBIT - CEBIT Perc of net sales
• PercCEBITDA - CEBITDA Perc of net sales
• PercEBITDA - EBITDA Perc of net sales
• VATDO - VAT on domestic sales

Another advantage of such setup, is that these KPI’s are standardized, making life easier for the controllers when comparing like for like scenario’s or in the case of applying a change to the calculation of a KPI, this is instantly reflected on the KPI for all companies and sets of data available in the system.

Although Cognos Controller has a good interface with excel to retrieve data, adding companies into a group or division, setting up a functional P&L structure etc. often result in using basic excel functionality creating, sums or lookups etc.  In case of updates of masterdata are required, e.g. when a company or an account is to be added or removed, the downside from working ‘offline’ in excel is suddenly proving to be a small nightmare. Such is not the case with using ‘online’ reporting models through the TM1.  In case the account or company structure changes the changes immediately trickle through to FAP and all the derived cubes and dimensions which makes this mechanism the more powerful.

Making use of TM1 functionality at hand

To bookmark the benefits of making use of the native functionality of TM1 as an alternative to the use of excel and Cognos Controller, we list some practical examples put in practise at customers.

The functionality of TM1 allows for a flexible preparation of a proposed forecast-version, for example combining 3 months of actual data with 9 months of the budget. As such the end user is presented with a first automated forecast-proposal with the possibility to update the forecast months to reflect their newest estimate.

Corporate certified reports: Most companies set out standard reports and reporting bundles used to inform different stakeholders. With TM1 FAP, these report bundles are linked with the full data set available from the TM1 cube and as such can be directly refreshed monthly benefitting from additional functionality such as print report wizard.  This print report wizard takes a ready-for-print-layout template and produces the same template for a set of companies.  No more need for bulk data download in excel and rework it. 

Analysis: On the basic TM1 cube some views on P/L, BS, CF and headcount are designed that can be used for ad hoc analysis.  Use cases are slicing and dicing, dragging and dropping dimensions to search for anomalies.  Variances or KPI’s can easily be added.  As soon as for example we add ‘AC vs BS’ in the actuality dimension this number is available instantly for all amounts in the cube.  Same thing for ‘2016 vs 2015’ or any other variance that is needed to help analysis.  In a dynamic kind of report the report user is in the capacity of going from the general to the specific to seek and explain exceptional figures.  For creating a Cash Flow analysis tool, we added summations to the account dimension to be able to provide a Cash Flow la carte.  Again, the basic (legal) data comes from Cognos Controller and in TM1 FAP we add functionality to provide better analysis and insight into the Cash Flow.

FX simulations: Although Cognos Controller perfectly can simulate FX conversions, this is done only by means of creating another actuality.  With TM1 the user can at any time chose the base FX rate and all numbers will be recalculated on the spot, now, instantly, 0 seconds waiting time.

Figure 2: FX Simulation in TM1.  Basis is up to date Cognos Controller/FAP information

Long Term Strategic Planning: take the historical numbers of all group companies and project these into the future with driver based assumptions to get a P/L, BS and CF for the next 10 years.

Allocations: Allocate costs from Cognos Controller per relative product sales to get a PL per product.

click to enlarge

Figure 4: Cognos Controller through FAP in Combination with a Data Warehouse for allocation purposes.  Includes different functional domains.

From the basic TM1 cube we can make functional domain cubes i.e. capex, fixed cost, working capital, headcount, … accessible to only those people (treasury, AR, ...) who should have access to it.

Budget Input and Forecast updates: next to reporting capabilities TM1 also enables users to perform budget or forecast entry on any level of detail.

… the possibilities are limitless