Consolidate based on financial dimensions

(0) Share

Report

Posted on by Community Member

Hi,

I am trying to create a consolidated Profit and Loss statement for a client. In the group there are ten companies and I want to consolidate the transactional data from all ten companies into one report. All companies share the same chart of accounts. The problem is that they are not interested in seeing the data per legal entity but consolidated based on one or more financial dimension/s. For example, in one of the reports they want one column per financial dimension customer category (4 different ones) and in the same report they wish a tree structure based on the financial dimension products. A solution we’ve tried is to add the ten legal entities to each node in the reporting tree. For example customer category 1 on the first level and then legal entity 1, 2, 3, 4, 5, 6, 7.. and so on on the second level of the tree, then customer cateogory 2 continuing with the same logic. This makes a very long reporting tree but generates the desired results. This is not a plausible option when it comes to products, though, since they have several thousand products. Another suggested solution is to create a tree structure based on legal entity and filter on Reporting Unit (one column per legal entity) in the column definition. This is not applicable in this case since they want a tree structure based on product and not per legal entity.

Has anyone had a similar request from a client and has a solution for it?

Kind Regards,

Amanda

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (0)

André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi Amanda,

Have you considered using a consolidation company and then create a report from this consolidation company?

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi Amanda,

a consolidated profit and loss statement based on a product finance dimension does not make much sense to me. That is because of problems related to the allocation of overhead costs to the different products. As an example, how to you want to allocate sales and marketing costs, depreciation costs and other general overhead costs to the different items especially if your company has thousands of products. If you simply allocate them based on sales revenue or direct products costs, you might not be able to identify which products are "good" (profitable) for your company. Why don't you try to create a direct costing report for your products based on something that is already available in AX such as the sales analysis reports in the sales and marketing module? If you are able to identify and separate intercompany sales revenues in this report I guess this is the best that you can get. I would recommend talking again to your customer about the specific requirement as it seems de facto impossible to me to create a full profitl and loss statement at a product level. All the best, Ludwig

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Management Reporter is really a consolidation and financial reporting tool. What you want to do sounds like product analysis, which falls in the realm of management reporting. Personally, I'd be inclined to choose a different tool for this than MR. I'd go with OLAP.

As you have already discovered, MR is basically a reporting tool that allows you to report along only a few (and short dimensions) at a time (usually account, some legal structure, time and actuals vs budget). With the OLAP engine you're far less constrained in that respect.

Was this reply helpful? Yes No