TM1 Forum

Having had some recent experience with Controller and seeing some of the challenges to get a nice slick integration with TM1 budget/forecasts, the question is soon asked "why not use TM1 to manage the financial consolidation? ".

I remember Applix at one point had a consolidation product, from what little I remember this was less than ideal for more complex type
consolidations, but possibly ok for small/medium type financial consolidations. Was this product ever really used ?

Are companies now starting to look to develop internally a financial consolidations module within TM1 ?
It would seem a fairly large undertaking but possibly a good investment in the long run......

Be interested to hear anyones thoughts.

JDLove wrote:Are companies now starting to look to develop internally a financial consolidations module within TM1 ?

I've built more financial consolidation systems than I can count. Did my first one in 1996 so I wouldn't say companies are just now looking to develop financial consolidations in TM1. The challenge with TM1 is that it is not a journal entry based system so you have to put something together to let you make journal entries (consolidation adjustments and such) and also have controls around it adequate for Sarbanes-Oxley rules (assuming you are U.S. based). It's not insurmountable but it takes a bit of work. In any case, it's likely to be better AND easier to implement than Controller.

JDLove wrote:Having had some recent experience with Controller and seeing some of the challenges to get a nice slick integration with TM1 budget/forecasts, the question is soon asked "why not use TM1 to manage the financial consolidation? ".

This is something we come across reasonably often. You can do it with TM1 but it can get very fiddly and there are some areas where TM1 is not suited at all well.

Key factors for a statutory\financial consolidation are:

Consolidation hierarchy:
TM1 aggregates well but the problem is changes to the consolidation hierarchy. TM1 does not have time based (or even named) hierarchies. This is generally fine for management reporting\budgeting where users want to see consistency but for financial reporting most users don't want historic number to change. There are ways of overcoming this but they need work and I don't think any provide an optimal solution

Minority interests\associates
TM1 can do this but it needs to be programmed in and can be fiddly

Journals
Can be done in TM1 but not "natural" for OLAP and features such as recurring\repeating journals require quite a bit of work. QueBit (formerly Creeth Richman) developed a module to do this and it was resold by Applix. I think it is still available and works with current TM1 versions.

Currency Translation
Can be programmed in TM1 but gets fiddly, particularly when looking at the more arcane areas such as assets at historic rates

Intercompany accounts\eliminations
TM1 can do this but it needs to be programmed in

Commentary
Can be done in TM1 but not well.

JDLove wrote: Are companies now starting to look to develop internally a financial consolidations module within TM1 ?

If you have a simple consolidation you can develop something in TM1 reasonably easily but a full feature financial consolidation system would require a lot of custom work and, as Tomok points out, would need to satisfy auditors, Sarbanes Oxley etc. Controller may not be easy to set up but my understanding is it has a lot more inbuilt functionality for these features and more controls.

Applix and other companies have tried to develop consolidation packages based on TM1 but these have never succeeded commercially (or even technically?)

Been awhile and I finally got back to this development.

Looking back and reading both replies was interesting.

I have invested some time in researching / remembering stuff I had long forgot about financial accounting and consolidations.
TM1 is not ideal and some aspects can get a little clumsy but I think all in all I think I have a solution.

Once I had a clear idea of the process I found the complexities were around the two areas

1. Consolidation Hierarchies
I wanted to allow for both Direct and Indirect methods and to allow for fairly complex ownership structures.
Also allowing users to enter new companies and ownership details per period.


2. Journal Entries
Recurring, Reversing, automated Investment elimination, Intra-group eliminations, Minority Interest, FX etc
Allowing easy review and drill back to detail, strong process flow. Simple copy process.

Still a few areas to iron out and resolve but so far I am pleased with the outcome, I mainly used excel forms, lots of TI as against rules.

TM1 gave me many challenges but I think with careful design choices and clean use of excel forms the process can be managed in an articulate way, that for me, is far superior to Controller.
The ability to manage many versions and within each have complete flexibility enables a fast consolidation close that can be used for budget and actual or other.

Can anyone point to where I may find out what I would need to do to comply with Sarbanes-Oxley ?

Hi JDLove:

Sorry to come to this topic a bit late.

On the topic of journal entries with TM1, I did want to take a minute to direct you to our FrameWORQ products page on the Commercial forum, specifically ControlWORQ which was sold by Applix as TM1 Consolidations many versions/moons ago and provides journal entry capabilities with detailed audit trail directly on top of existing TM1 cubes.

Please don't make inquiries within this thread - they will be deleted per forum policies. Please send a PM or use the contact information on the page linked above.

Regards,
Mike

JD
This is the journalling product I referred to in my original reply, well worth a look. Quebit took over Creeth Richman a couple of years ago and they have a lot of TM1 expertise.

Mike Cowie wrote:On the topic of journal entries with TM1, I did want to take a minute to direct you to our FrameWORQ products page on the Commercial forum,
Mike

Hi Mike & Steve,

Its was only the other day I was speaking to some sales guys in a meeting and they again asked me if TM1 could be used for Financial Consolidation, of which I replied yes but it depends on the complexity as to whether its the best option. They responded that at the past company they worked for (IBM Partner) the head of consulting told them it wasn't possible and TM1 could only be used for "simple budget consolidation"... so some hold this view and I can only assume its due to the fear of trying to build something, thus better to use a solution like Controller. But this is missing the many cases where Controller is not a good fit !

I have been asked to investigate this further as in (Asia) there is strong interest in Consolidation products and some customers are openly saying this is the key feature in the entire solution for them.

I missed that post in the commercial section !
I am keen to take a closer look. This could answer some of these guys questions ! I'll use the contact information on the site first before a PM .....

looking forward to finding out more, looks like some solid work.

Thanks
JD

JDLove wrote: at the past company they worked for (IBM Partner) the head of consulting told them it wasn't possible and TM1 could only be used for "simple budget consolidation"... so some hold this view and I can only assume its due to the fear of trying to build something, thus better to use a solution like Controller. But this is missing the many cases where Controller is not a good fit !

JD, remember that not all IBM partners are equal! Many partners who come from legacy Cognos backgrounds are familiar with BI and Planning but have struggled (and some still do) to learn TM1 and understand its capabilities and power, eg I have heard in the past that TM1 cannot calculate year-to-date values!

The responses you have had are from experienced TM1'ers who have implemented these systems, often many times. They know what they are talking about, bothe the possibilities and limitations.

btw, I am sure Mike would be happy if you contact him directly, he is normally very helpful.

Best of luck

IMHO as an external consultant there is a prfessional and moral obligation that you atleast mention to your client about Cognos Controller and all its features before developing the same in TM1.

nick_leeson wrote:IMHO as an external consultant there is a prfessional and moral obligation that you atleast mention to your client about Cognos Controller and all its features before developing the same in TM1.

Nick,
If you read the posts I don't think anyone here is suggesting hiding the existence of Controller or even pretending that TM1 will easily implement all features of a complex financial consolidation system.

However, if you have a straightforward financial consolidation AND need other things such as planning, reporting, analysis, reporting etc, then TM1 is well worth considering. Possibly also look at integrating TM1 with Controller and FAP cubes but this will add considerably to cost.

JD,

If you're still out there, can you provide an update on this potential implementation of financial consolidation in TM1? Were you successful? I'm curious because I am very interested in doing the same thing but our financial reporting team is wary of being pioneers (for good reason it seems - I haven't found anyone actually using TM1 for consolidations). I've replicated in TM1 all of the functionality and more that they're getting out of their current tool, but it would be nice to know that someone else has done it.

You may check this http://www.tm1forum.com/viewtopic.php?f=20&t=12046

erinbg wrote:JD,

If you're still out there, can you provide an update on this potential implementation of financial consolidation in TM1? Were you successful? I'm curious because I am very interested in doing the same thing but our financial reporting team is wary of being pioneers (for good reason it seems - I haven't found anyone actually using TM1 for consolidations). I've replicated in TM1 all of the functionality and more that they're getting out of their current tool, but it would be nice to know that someone else has done it.

Hi

Regards the potential implementation well its was unfortunate but the project went on without my input, the fixed contract offer to me as an independent contractor was far too low for the level of risk and I simply had to decline.

I was informed recently the project was completed then went live, I am aware of others using TM1 for financial consolidations.
I really don’t see why not, sure you need to build it from scratch in some cases but the requirements are very defined and understood and TM1 is flexibly able to adapt.

My efforts were over a period of time on my own dime, I was wanting to build a template that could be adapted to various projects.
I didn’t base it on HFM or anything else, I went ground up based on the “process” and followed the excellent book ("Consolidated Financial Statements" Ng Eng Juan McGraw Hill Singapore ).
I am a chartered accountant (NZ) and spent time working in finance of large public companies so this content was fairly easy for me to digest but I really did lean a lot from this book and can’t recommend it highly enough.

If you can read this and follow it then you are in a strong position to think about TM1 as a solution.

I’ll possibly spend much more time on this next year as I have a few colleagues that are in Audit and I hope to get their input as to what they see in practice and some additional information around the regulations. I’m happy to share at that point so I will get back to the community.

JD

Hello
I am an independent consultant (former employee of Cognos and then IBM Italian branch).

I can tell you that in Italy statutory consolidation in TM1 is now the rule.

I have developed a model called "TM1SEC" which is currently used by 7 groups of different sectors and sizes ( the biggest is an italian holding with 1,5 B in turnover consolidating 120 legal entities).

Here are the highlights of the model:
- Pre-defined security schemas
- Data load from any external sources (even from excel reporting packages)
- Multiple chart of accounts management (statutory italia, IAS, managerial reporting...)
- Pre-defined Web data entry forms according to the selected chart of account
- Debit-credit layout journal entries
- Workflow management (approval of financial statements)
- Ownership calculations
- Stage consolidation can be enabled
- Business areas for segment reporting can be enabled
- Automatic jouranls (intercompany, investments, equity, dividends...)
- Pre-defined Working output Reports
- Pre-loaded Italian statutory schemas for annual report
- Pre-loaded proposals for IAS statutory schemas for annual report
- Implementation time: 2 weeks to define the design, 2 weeks to develop, 2 weeks for testing and training. Live in 2 months.

From my experience this model have brought to a secure WIN when the customer is already using TM1. For example a customer using TM1 for budget and wants to extend the usage of TM1 to statutory consolidation.
Anyway the TM1SEC model has win in other software selections as TM1 was presented as "one product" to really fulfill all "old CPM" customer needs.

Ciao