An Alternative For Distributing Financial Reports
05 Thursday Mar 2020
Posted in General Ledger, Management Reporter
Tag Archives: Management Reporter
Financial Report Distributions
01 Friday Mar 2019
Posted in Management Reporter
In this webcast I will show you different options how one can generate and distribute financial reports to managers. A special focus is made on security issues and things that you have to take into consideration when distributing financial reports in the way presented.
Parallel accounting & Financial statements (Part 1/2)
25 Tuesday Sep 2018
Posted in Fixed Assets
This webcast illustrates how one can create financial statements in situations where fixed asset postings are made on different posting layers to incorporate different GAAP, such as IFRS, local GAAP, tax GAAP and alike.
What can make things difficult in this context are situations where non-fixed asset related postings are made in the form of so-called delta postings that account only for the difference in the amounts required by the different GAAPs.
Join me in this webcast and see how one combine full fixed asset related postings and delta postings made outside of the fixed asset module when creating financial statements for the different GAAPs.
Attachment: SampleDataFA_Final
Link multiple Management Reporter reports
20 Friday Jan 2017
Posted in Management Reporter
Linking different Management Reporter (MR) reports is a common finance scenario when for example the profit/loss from the Income Statement (IS) is linked to the equity section of the Balance Sheet (BS).
Microsoft provided a detailed description, which exemplifies how this linkage between the IS and BS reports can be established. For details, please see the following website.
Some time ago I was confronted with the requirement of linking multiple MR reports to a newly created one. As Microsoft already provided a detailed description of the necessary setup steps, I initially thought that linking multiple MR reports just follows the same principle, which is required for linking a single report.
For that reason (and based on the Microsoft guideline), I simply added two instead of a single row link in my MR row definition to get the data from the BS and IS report loaded into my newly created report. The next screen-print exemplifies this setup, where the first row link (‘BS’) links to the Balance Sheet report and the second one (‘IS’) links to the Income Statement report.
The row definition of my newly created report consequently includes two rather than a single column that reference the cells in the already existing MR reports through the ‘@WKS(…)’ fields.
The column definition used was identical to the one used in the Microsoft guideline and included only a single FD (financial dimension) column. The next screen-print illustrates the setup of this so-called column definition.
After having done all the setups that I considered important, I was very much disappointed once I noticed that the report did not include the IS data.
Some additional investigation of the report creation process lead me to the report queue status form where I noticed the following message:
Based on this warning message, the report setup with the two row links obviously seemed to be the wrong approach to incorporate data from multiple other MR reports into my newly created one. To get this corrected, I first changed the link type for the Income Statement data to ‘Management Reporter Worksheet’.
In line with this change, the cell reference was changed from ‘@WKS(B=C35)’ to ‘C35’ and a column restriction was incorporated into the row definition setup, which is exemplified in the next screen-print.
In addition, a reporting tree was setup, which linked to the different financial dimensions/worksheets specified in the row definition setup form. Example:
It is important that all financial dimensions/worksheet references, which are specified in the row definition form are included in the reporting tree. Otherwise, the report generation will interrupt with an error message.
The last setup required relates to linking the different reporting tree elements to the report columns. How this linkage can be established is illustrated in the next screen-print.
With those report modifications in place, the report could finally be created and showed all the data retrieved from the other MR reports. The report could of course be further refined by including the values shown in the FD and WKS column into a single one. For reasons of brevity this exercise is, however, skipped here and left as an exercise for the reader.
In order to allow you a direct comparison with the Microsoft guideline referenced in the beginning, all setups and reports exemplified in this post have been created with the MR version CU12. In more recent MR versions, the link type ‘Financial dimension + Worksheet’ has been removed. As a result, linkages to multiple MR reports can only be realized through the ‘Management Reporter Worksheet’ link that has been used for incorporating the IS data above.
Management Reporter – Reports based on different Chart of Accounts
03 Thursday Dec 2015
Posted in Management Reporter
When doing a financial consolidation, financial data from different companies need to be combined in a single “consolidation” report. As the data that need to be consolidated are often based on different Chart of Accounts (COA), the question arises how ledger accounts from different COA’s can be combined into a single consolidation report. This question will be answered in the following based on a simplified example.
Example:
The financial data of company DEMF and BRMF need to be consolidated. While the first company (DEMF) uses a “shared” COA, the second company (BRMF) uses a local Brazilian COA. What makes things even more complex is that company DEMF records all transactions in EUR-currency, while company BRMF keeps all its books in Brazilian reals. The next screenshot shows you the COA’s and currencies setup for the two companies.
In order keep the example as simple as possible, only the bank accounts of both companies are considered for the following illustrations.
In company DEMF only one single transaction with a total amount of 10000 EUR has been recorded on bank account (main account) “110200”.
In company BRMF two local currency transactions have been recorded on the bank account (main account) “1.1.1.2.02”.
The exchange rate used for consolidation purposes is 300 BRL / 100 EUR.
Against the background of those data, a financial consolidation report should show a total balance of 50000 EUR or 150000 BRL in the bank accounts line.
To realize this, the following setup has been done in Management Reporter.
Step 1: Setup of the row definition
The first step in setting up the consolidation report is mapping the different main accounts in the row definition of the report. This mapping in done by specifying separate row links that hold the main account information of both companies. Please see the following screenshot for an example.
Step 2: Setup of the reporting tree Definition
The next step is setting up a reporting tree. When doing this setup, you have to make sure that a reference is made between the different companies and the row definition / row links established in the first step.
Step 3: Setup of the column Definition
The third step is setting up a column definition as illustrated in the next screenshot.
Please note that you need to link the different columns with the reporting tree elements if you want to report on the different legal entities.
Step 4: Setup of the report Definition
The last step in the report generation is the combination of all previous elements (row definition, reporting tree and column definition) into a report. Please note that you need to select the reporting currency check box before running the report if you want to do a currency conversion once the report that gets generated.
Result:
Once the report is generated you can have a look at the data in the various currencies setup in Dynamics AX. The next two screenshots show you the EUR-currency and BRL-currency report.
Management Reporter – Excel Import
23 Monday Nov 2015
Posted in Management Reporter
≈ Comments Off on Management Reporter – Excel Import
Finance experts often need to generate reports that combine data that are recorded in Dynamics AX with data from external data sources. An example for those reports are consolidation reports where financial data from subsidiaries are delivered in Excel spreadsheets that need to be combined with data out of Dynamics AX. Another example are Actual-Budget comparison reports where actual data are retrieved from Dynamics AX and need to be combined with budget data that are recorded in Excel.
This blog post illustrates two different possibilities how data from Dynamics AX and data that is recorded in Excel spreadsheets can be combined into one single Management Reporter report by using the aforementioned Actual-Budget comparison example.
Importing data from Excel into Management Reporter reports can be done either by using the so-called “combined link approach” or by using the “separate link approach. Both approaches will be presented in the following.
Option 1: Combined link approach
The first option to import Excel data into Management Reporter reports is using the combined link approach. This approach is characterized by the fact that financial dimension values out of Dynamics AX and external Excel data are combined into one single column in the Management Reporter row definition window. The following screenshot illustrates this setup in detail by highlighting the references to Dynamics AX data in yellow color and the references to external Excel data in green color.
Unfortunately, Management Reporter does not allow combining references to Dynamics AX data and to external Excel data into one single row.
This is why three single lines – two non-printing lines and one total line – are used in this example for reporting on each element, such as for example Sales, COGS, etc.
In addition to the row definition, a column definition needs to be setup with a column type “WKS” that incorporates the external worksheet data.
The last step required is combining the row definition and column definition into a report and to generate the report. Result:
Please note the following when using the combined link approach
- The reference to the external worksheet data defined in the Management Reporter row definition window links the information of the column definition window to the data included in the Excel spreadsheet. As an example, the reference “@WKS(G=C3)” defines that the value included in cell C3 of the Excel spreadsheet is included in column G of the Management Reporter report. Please have a look at the following illustration.
- The references used by the combined link approach to the external worksheet data are static. That is, if you make a change to your report columns, all “@WKS”-rows need to be adjusted. Otherwise, values from the wrong Excel cell are picked up and reported. This fact limits the use of the combined link approach to situations where a small number of external Excel data need to be incorporated into Management Reporter reports. If large amounts of external Excel data need to be included in Management Reporter reports, the separate link approach appears to be the more suitable option.
Option 2: Separate link Approach
The second approach for importing external Excel data is using the separate link approach. The major difference to the combined link approach is that the references to Dynamics AX data and to the external Excel data are established in two (or more) separate columns in the row definition window. This is illustrated in the following screenshot.
The second major difference is that the link to the external Excel document is not made in the row link window illustrated above but in a reporting tree as illustrated in the following screenshot.
As before, the last step required is combining the row definition, the column definition and the reporting tree definition into a report and to generate the report. Result:
Please note the following when using the separate link approach
- References to external Excel data are made by using the keywords “CPO” and “RPO” to define the direction in which the external Excel data are read during the import (CPO => column by column, RPO => line by line, see the following illustration).
- Different from the combined linked approach, the separate link approach uses the period value in the column definition to reference the respective Excel cell. In the example used, the budget sales value reported in the first budget column is picked up by starting at cell B3 and by moving one cell to the right. The budget sales value reported in the second budget period is picked up by starting at cell B3 and by moving 2 cells to the right and so on. This relative cell reference makes the separate link approach very robust. That is, even if you add or remove columns in the column definition window, your report will still pick up the correct values from Excel.
- Due to the relative simple and robust setup framework, the separate link approach is most suitable in situations where large numbers of data need to be imported from Excel into Management Reporter reports.
