Management Reporter – Excel Import

23 Monday Nov 2015

Posted by Ludwig Reinhard in Management Reporter

≈ Comments Off on Management Reporter – Excel Import

Tags

Excel Import, Management Reporter, Microsoft Dynamics AX 2012

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.

Construction in progress & project module

13 Friday Nov 2015

Posted by Ludwig Reinhard in Fixed Assets, Project

≈ 6 Comments

Tags

Construction in Progress, Fixed Asset, Fixed asset statement, Project

This blog post illustrates how you can use the Dynamics AX project module to track the costs of constructions in progress and transfer those costs later on to a fixed asset once the construction is finished.

Before you are able to track the costs of your construction in progress you have to ensure that an investment project group is setup that accumulates all costs on balance sheet accounts as illustrated in the next screen-print.

Once your investment project group is setup, you need to create an Investment project…

… that you use for recording all construction costs. In my case, I recorded hour, expense and item costs that add up to a total of 17300 EUR as shown in the next screenshot.

Once your construction project is finished and provided that all costs have been recorded on your project, you can transfer those costs to a fixed asset by using the estimate function.

The first step required in the estimate form is creating an estimate that accumulates all costs recorded. This can be done by selecting the “set cost to complete zero” cost to complete method.

After the estimate is created you can identify the total costs accumulated in the WIP tab of the estimate form. Example:

The next steps required are (a) specifying the fixed asset to which you want to transfer all costs and (b) posting the estimate.

Please note that posting the estimate does not create any ledger transaction and does not transfer any costs to the fixed asset selected. To do the actual transfer of the costs to the fixed asset, you need to eliminate the posted estimate by using the corresponding functionality.

After the elimination is done, you can identify the costs on the fixed asset selected before.

Note 1:
In the example used, the construction started and finished within a single month. If you are working on long-term construction projects and want to update your “construction in progress” fixed asset in the fixed asset module you need to:

Post the additional costs that arise in the following months as usual,
Reverse the elimination recorded previously,
Create and post and additional estimate,
Eliminate the estimate.

Those steps need to be repeated every month as long as your project runs.

An alternative to reversing and repeating eliminations recorded before, you can separate your investment project into subprojects that are eliminated successively as the construction progresses.

Note 2:
If you are eliminating your construction projects on a regular basis, those costs are included in your fixed asset module and thus in your fixed asset statement.

If, on the other hand, you are not eliminating your project construction costs on a regular basis, all costs are accumulated in WIP (balance sheet) accounts only and are not included in the standard Dynamics AX fixed asset statement. In order to get your fixed asset statement right, you thus need to include the WIP accounts in your fixed asset statement which requires that you either use the Management Reporter for your fixed asset statement (see the previous post) or that implement a system adjustment.

Note 3:
If you are transferring all construction costs initially to a “in construction” fixed asset, you can move all costs from this “in construction” fixed asset to an ordinary fixed asset by using the standard AX fixed asset reclassification functionality. Example:

Fixed asset statement report issues (Appendix)

07 Saturday Nov 2015

Posted by Ludwig Reinhard in Fixed Assets, Management Reporter

≈ Comments Off on Fixed asset statement report issues (Appendix)

Tags

DATEV, Dynamics AX, Fixed asset statement, German commercial code

The following screenshots provide you some additional information on the fixed asset posting profile setup used for the examples illustrated in the blog posts. Please not that separate ledger accounts are used for each combination consisting of (a) the fixed asset transaction type, (b) the fixed asset group and (c) the value model.

Please note that the sales disposal parameters illustrated in the following screenshot could be setup in more detail. Yet, the major point that I want to make here is that the ledger accounts that are setup in this section differ from the ones used for recording the original fixed asset transaction. As an example, fixed asset acquisitions are recorded on ledger account 180500, while the offset transaction that is recorded when the fixed asset is sold is recorded on ledger account 180502.

As for fixed asset sales, separate sub-ledger accounts have been setup for recording the scrap of fixed assets. For details, please see the next screenshots.

The last screenshot included here shows some additional details of the ledger account setup. The term “PKW” included in square brackets represents the fixed asset group.