Reporting Series Part 2 – Consolidation with Dynamics AX Management Reporter

Reporting Series Part 2 – Consolidation with Dynamics AX Management Reporter

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.

Management Reporter Consolidations

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.

Management Reporter Consolidating Balance Sheet

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.

Management Reporter Consolidations

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

About Dan McAllister

Dan McAllister has created 10 entries.

Post A Comment

YOUR CAPTCHA HERE

Contact Us

All fields required

First name

Last name

Company

Your Email

Subject

Your Message

×

Meet Us

All fields required

First name

Last name

Company

Your Email

Available Date & Time
AMPM

Your Message

×

Subscribe to our mailing list

* indicates required
 

×
Learn More

Contact Us

All fields required

First name

Last name

Company

Your Email

Subject

Your Message

×

CRM Test Form

×

Influence

All fields required

First name

Last name

Company

Your Email

Proposed Webinar Date & Time
AMPM

Your Message

×

Contact Us

All fields required
×

Contact Us Here! 

* indicates required

×

We want to hear your feedback

∗ indicates required

×

Let us keep you updated!

 ∗ indicates required

×

Meet Us

All fields required

First name

Last name

Company

Your Email

Available Date & Time
AMPM

Your Message

×

Contact Us Here! 

* indicates required

×

Contact Us Here! 

* indicates required

×

Schedule your meeting with us!

* indicates required

×

Schedule your meeting with us!

* indicates required

×

Book Your Time

×

                        RAMPUP SUBSCRIPTION PLANS

                    Choose a plan below, and one of our

              Support Consultants will contact you shortly.

×

                           PACKAGES START AT $4,999

                  Choose a package below, and one of our 

               Support Consultants will contact you shortly.

×

 Performance Review Details

 

Diagnostic Review of DyanmicsAX Server (AOS) & SQL performance and stability.

 

Written summary of findings and recommendations.

 

Up to 15 hours of issue resolution time to target your most immediate performance issues.

 

Review completed by our 100% onshore support team averaging more than 10+ years of DynamicsAX experience.

 

Get the help you need now, with no long-term commitment.

×

Functional/Department Help Pick 3

 

Help with up to 3 functional or department issues such as: invoicing, costing, closing the books, AR, AP, inventory, warehousing, etc.

 

Talk with our 100% onshore functional experts with over 250+ years of combined AX experience to define your issues and find recommended solutions.

 

Up to 20 additional hours of issue resolution time included.

 

Presentation of solutions and next steps from our expert team.

 

Get the help you need now, with no long-term commitment.

 

×

Reporting / BI Help

 

Choose up to 3 standard or custom reports for assistance.

 

Solve typical issues like: Report too slow, does not show what we need, need an additional field/column, etc.

 

Talk with our 100% onshore functional experts with over 250+ years of combined AX experience to troubleshoot report issues and recommend solutions.

 

Up to 20 additional hours of configuration or development time included.

 

Presentation of solutions and next steps from our expert team.

 

Get the help you need now, with no long-term commitment.

×

Please fill in the form to RSVP

 

×