Creating Consolidated Financial Statements using Management Reporter – Consolidating data across one or multiple Dynamics ERPs with the same chart of accounts and same fiscal periods (Part 1 of 7)

Many organizations need to prepare financial statements that report on multiple companies in a consolidated format. Management Reporter can help a company consolidate their data across one Microsoft Dynamics ERP, multiple Microsoft Dynamics ERPs, or across ERPs. Consolidations can also occur at different levels, based on a company’s needs. This series of blogs will help you determine which consolidation options in Management Reporter are right for your business and provide you with information on how to create consolidated financial statements on consolidating data in Management Reporter.

• Part 1 contains an overview of consolidating data across one or multiple Dynamics ERPs with the same chart of accounts and same fiscal periods.
• Part 2 describes consolidating across one or multiple Dynamics ERPs with different chart of accounts.
• Part 3 defines the process for consolidation across one or multiple Dynamics ERPs with different fiscal periods.
• Part 4 is focused on reporting with multiple Dynamics AX 2009 instances (AOS)
• Part 5 will show you how to report on consolidation data that resides in non-Dynamics ERPs.
• Part 6 will discuss how to produce a consolidated report in Management Reporter that includes a column for elimination entries.
• Part 7 will focus on scenarios where a company does not own 100% of a subsidiary.

The simplest method for consolidating using Management Reporter is to use reporting trees to aggregate data across companies that have the same chart of accounts and fiscal periods. For this consolidation method, all companies will need to be using a Dynamics ERP (Dynamics AX, Dynamics GP, or Dynamics SL) but it can be a mix of these various Dynamics ERPs.

TIP : Verify all dimensions are named the same. For example, if Company CEU has a dimension named Department, verify it is also named Department in CEE, not Dept or Segment 1.

The high level steps to consolidating using a reporting tree are:

1. Verify each Dynamics ERP company is created in Management Reporter.
2. Create a row definition that includes all accounts for all companies.
3. Create a column definition.
4. Create a reporting tree definition and define each company as a reporting unit in the Reporting tree.

You will need to verify that each Dynamics ERP company has been set up in Management Reporter. All ERPs use a data mart, so the companies are integrated or imported from the Configuration Console. If you are using Dynamics AX 2009, use the Modify Company form to ensure that each company is pointed to a source system, which is the Dynamics ERP in which the data resides. In this example, CEU is pulling data from Dynamics AX and CEE is pulling data from Dynamics GP.

Next create a row definition, ensuring that all appropriate accounts in all companies are included in the rows. Then create a column definition that includes the columns required for the report you are creating.

Finally, create a reporting tree that includes a reporting node for each of the companies that you are using on consolidated reports. The following shows an example of using a Management Reporter Reporting Tree Definition to identify each company you will be consolidating.

When you use this reporting tree with a report definition, you can view each company individually with the consolidated amounts displayed at the summary level.

Viewing companies side by side

Many customers prefer seeing each company side by side in the report and then a consolidated total column. This is very easy to do once you have the reporting tree created.

1. Create a column definition that includes a Financial Dimension column for each company.
2. Use the Reporting Unit field to select the tree and reporting unit for each column.
3. Add headers and totaling columns, if you wish.

Here is a same column definition in a side by side format.