Reporting Series Part 2- Consolidation with Dynamics AX Management Reporter

Dan McAllister

The Microsoft Dynamics AX/D365 Support Team at Avantiico is focused on solving our client’s problems, from daily issues to large and more complex problems.

See All Posts

March 23, 2015
10:40 am
No Comments
Read Time: 1 Hour

About Management Reporter

Dynamics AX Management Reporter (MR) is a reporting application that financial and business professionals can use to create, maintain, deploy, and view financial statements. This tool creates reports from the general ledger, including all ledger accounts and financial dimensions. Commonly we see that the report definitions, or layouts, are maintained by either a business or financial analyst; training on writing straightforward report definitions takes only a day or two for a clever analyst. Reports are typically consumed by the CFO but also by department managers. Using financial dimensions allows for financial statements to be easily created in bulk based on a financial dimension, such as Department.

For companies with multiple legal entities there is an additional opportunity to create a consolidating set of reports in Management Reporter. The way to do this prior to AX 2012 R2 CU7 and AX 2012 R3 was to (1) do consolidations and eliminations in AX (2) run consolidating MR reports (3) make any changes in AX, reconsolidate, and re-run reports. While this is common and still works, this blog post is to discuss pros and cons of leveraging new capabilities in AX2012 R2 CU7 and AX 2012 R3 to cut out AX consolidations entirely.

One final note before we jump into MR configuration: if your company has multiple legal entities that all use USD as their primary currency, this functionality should be available to you even on prior versions of AX. The new versions and releases of AX add foreign currency functionality that is needed for multinational/multicurrency environments.

Consolidation With MR

The new piece of functionality that now makes multi-currency consolidations possible in MR can be found in AX Main Account setup. Account behaviors can now be set up to ignore the default foreign currency settings: the “Exchange Rate Type” (which table of exchange rates) and the “Currency Translation Type” (closing rate vs. average rate, etc). Prior to this, MR would attempt to apply the same FX translation logic to both Balance Sheet and P&L accounts which is insufficient for financial reporting.

Below is a very basic example of a consolidating Balance Sheet in which the consolidation and elimination are both done solely in MR. You can see that intercompany accounts have been correctly eliminated, and an unrealized gain/loss has been reported in the foreign entity.

The important thing to note is that no transactions have taken place in AX for the Elimination or Consolidation. What you will see next is a column definition in AX; this is just an example of how MR might be leveraged to do a consolidation/elimination. You will see that columns C, D, F, and G in the column definition match to the report printed above.

To provide a little clarity for non-MR users, the columns are doing as follows:

A. Pull the description written in the Row Definition (not shown here, but is a very basic row definition)
B. Pull the account(s) from the Row Definition
C. Pull USMF base company data
D. Pull DEMF base company data (note: the base company is in Euro but the report dictates that Euros be transferred to USD)
E. Pull any accounts that should eliminate from both companies, but do not print this column
F. Take the opposite of column E – this is what your elimination journal entry would be
G. Add columns C, D, and F together – this is what your end result consolidation would be

This is a very slick way to quickly iterate on consolidating multi-currency financial statements without the need to go through a sometimes slow AX consolidation and elimination process. It also exposes detailed transaction data for drilldown purposes; if you were to drill into AX-consolidated transactions you would see that they are summarized by GL posting (main account + financial dimensions) There are, however, some cons:

From an audit perspective, you need to confirm with your auditor that this is an acceptable way to consolidate your books. There are no journal entries in this method; is that okay? We have seen both situations. One possible permutation of this would be to post-elimination entries in an elimination company but still do the consolidation piece in MR.
This does not easily accommodate for partial eliminations. The assumption in the column definition above is that your eliminating accounts should always have a zero value upon consolidation

Do you see other pros and cons in this new approach for your business? If so, please leave a comment or ask a question below. We are very interested to hear how others are approaching this new AX capability. In the next Reporting Series blog we will be looking at Atlas as a tool, pros and cons, and what the output might mean for your reporting needs.

Go back to part 1: Reporting Series Part 1 – Building a Microsoft Dynamics® AX Reporting Toolkit

Move on to part 3: Reporting Series Part 3 – Operational Reporting Using Atlas Add-In

Reporting Series Part 2- Consolidation with Dynamics AX Management Reporter

Dan McAllister

About Management Reporter

Consolidation With MR

Explore More Content:

Featured

Products

Resources

Our Company

Join Our Newsletter

Request a free Dynamics 365 demo