Sunday, 3 April 2016

...the consolidation functionality

It is two days before the end of the year-end reporting period and at 4:50 pm the helpdesk receives a new ticket with title “Consolidation of subsidiaries and NCI query”.

Silence in front of my screen… the subject of the ticket is quite worrying for such a week and as I read further down the situation becomes even worst.  The request of the user was to explain how the system calculates the NCI for specific subsidiaries, why the closing balance was 150.000$ and what the movement consolidation movement represents. A lot of questions….

Proem

Until today, I have seen four approaches in consolidating the values of an entity to the parent entity.
  • The first approach is to create manual journals at "Entity Curr Adjs". This journal will be posted both at the subsidiary and at the consolidation entity of the group.
  • The second approach is to use the Sub Calculate and the function HS.Exp in order to populate specific members or consolidation accounts at the value dimension members "Entity Currency" / "Entity Curr Adjs".
  • The third approach is to use the Sub Calculate and the function HS.Exp in order to populate specific members or consolidation accounts at the value dimension members [Proportion] / [Elimination].
  • The fourth approach is to use the Sub Consolidate and the function HS.Con  in order to populate specific members or consolidation accounts at the value dimension members [Proportion] / [Elimination].

Main

Personally, I am quite careful with the use of the first approach for two reasons. First, the process is decentralized which means that every user will have to create their own journals. As a result, the group reporting team will need more time to review these journals. Additionally, if the granularity of the data allows us to automatically consolidate the data, it is a waste of HFM’s core functionality as well as a waste of the whole investment not to do it. 

The second and the third approaches (let’s call them Calculate approaches) use the Sub calculate and the function HS.Exp to directly calculate the value of the target account without any analysis. 

This means that the system will calculate a value for the account/movement NCI either at "Entity Currency" / "Entity Curr Adjs" or at [Elimination] and the user will not be able to retrieve any other information on how these values have been created. If the user needs to understand how these values have been created, we need to maintain an excel file outside of HFM.

The fourth approach (Consolidation approach) uses the Sub Consolidate and the function HS.Con to create the transactions that will create the value of the target account.  

I am not going to be too technical but with the use of the HS.Con and if the parameter “Nature” is populated, the system will produce and present the individual transactions that will create the value of the NCI account. As a transaction I consider the process of each populated POV at the value dimension member [Parent] in order to populate either the [Proportion] or the [Elimination] value dimension members.

With the use of the Consolidation approach, the user will be able to retrieve the source POVs that were used to produce this value as well as the source values and the minority percentage that were used to create the final value of the NCI movement. To retrieve this information, the user will have to either open the Entity Details screen for the NCI account or to open the journal that has been automatic created in the value dimension [Elimination]. (For more information, I would suggest to open the HFM guides.)

As a summary, the Calculate and the Consolidation approaches will automatic produce the consolidated values. 
  • The advantage of the use of the Consolidation approach is that it will allow the user too easier audit the values and the tasks that are executed within HFM. 
  • The advantage of the use of the Calculate approaches is that the consolidation time is shorter comparing to the Consolidation approach. Based on my tests that I run on version 11.1.2.1, there is a 5% increase in consolidation time if the Nature is not populated and a 15% increase in consolidation time if the Nature is populated.

Epilogue

In my example, If the existing rules were built based on the Calculate approach, I would have had to spend a considerable amount of time to reverse engineer the mechanism and build an excel file to explain how the value of the NCI has been created and what the consolidation movement represents.

On the other hand, if the existing rules were built based on the the Consolidation approach, I would have had to print the automatic journals that were created in the [Elimination] member of the Value dimension and send them to the user for additional analysis.

So, the question is which is more important, to run quicker consolidations or to be able to audit your information?

Finally and in order to be honest, I had to answer at least 10 times the above question the past 7 years and it was always during the most stressful periods.

Cheers,

Thanos


...Smartview and OneDrive

Recently (July 2017), I discovered a new functionality of OneDrive which allows multiple users to work on the same file concurrently a...