Wednesday, April 30, 2014

Consolidation in Management Reporter with MR CU8

Can't help myself but talk about consolidation again! It's a hot subject in our company so I wanted to share my experience.
Before upgrading Management Reported to their latest version CU8, I was doing our consolidation and eliminations in AX. I always preferred to have a set of consolidation balances in a trial balance that I can look at in AX. Also, Management Reporter was not mature to offer a better process.
Recently we upgraded to Management Reporter CU8 which is the latest version at the moment and I just started to build our consolidated trial balance as a report. Here are few ideas on how it works:

1) The first element to setup is your reporting tree. The reporting tree will have the list of all the companies you need to consolidate. To create a new reporting tree in MR, click on File > New > Reporting Tree Definition. Then manually select the companies you want to add in your tree by clicking in the Company column and selecting in the drop down. Manually enter the Unit Name and Unit Description of what you will want to see in your reports.

You want to leave the first line that is automatically created when you create a new reporting tree definition. This row will allow you to run your report for all companies, useful if you don't create a side by side report (view side by side the companies, see below).

2) The second element to setup is the row definition. The row definition will have the list of all the accounts you want to display on your report. To create a new row definition in MR, click on File > New > Row Definition. Below is a simple example of 2 accounts and a total row for the sum of the 2 accounts. The purpose of this post is not to go through all the options on the row definition.
Do not forget to setup the category account on your retained earnings account in AX in order for MR to calculate the balance correctly.

In your row definition you will need to setup a row for the currency translation adjustment if you are dealing with entities in different currencies. I will write a new post soon to explain how to do that.

3) Then you need to decide if you wand to view you consolidated companies side by side or a single company at a time. This will define the format of your column definition. I will talk about the side by side format. However, the single company at a time view is simpler and only requires to use the reporting tree.

In your column definition, add one column per company you want to see on your report. The screen shot below shows an example with 4 companies side by side, the total pre-elimination, the elimination entries and the consolidation total.
For each company column, select the reporting unit from your reporting tree to specify which company to pull the number from.
The elimination entries are coming from the elimination company in AX. This is not mandatory to book the elimination entries in AX, you can eliminate on the report directly. However, in our case, we have to eliminate specific amounts and we wanted to keep track of those entries in AX for audit purposes.

When it's time to execute your report, make sure to select the correct row and column definitions. Then select the reporting tree you used in your column definition under Tree Type and Tree.

I hope this will help you create your consolidation reports in Management Reporter without having to run the consolidation in AX. Let me know if you have any questions and enjoy!