Within the previous blog post I showed you several problems of the inventory value report and the potential conflicts report that can cause you some “headache” if you try reconciling ledger and inventory balances. As I do not want to leave you on your own with those problems, I thought it might be useful to show you some alternative approaches for reconciling ledger and inventory values.
Because of the problems with the inventory value report, (as described in the previous blog post), I setup a completely new Dynamics AX demo environment and repeated the previously shown transactions for two newly created test items “L2000” and “L2100”. As before, all item transactions for the first test item “L2000” are recorded in January 2015 and all item transactions for the second test item “L2100” are recorded in February 2015. The next screenshot gives you once again an overview of the inventory transactions executed for the first test item in January 2015.
After recording those transactions, I run the inventory value report without any date restriction and got the following result.
What you can identify from this screen-print is identical to the result for the inventory transactions that I showed you in the previous blog post. Against the background of those transactions let’s now have a look at some alternative inventory reconciliation approaches.
Approach 1: Financial dimension “item”
A first alternative approach for reconciling inventory and ledger balances is setting up all items with a financial item dimension and using standard General Ledger features, such as the trial balance list page, for reconciliation purposes. Example:
What you can identify from the screen-print above (that filters on the first test item “L2000”) is
(a) the total inventory value of 14250 EUR recorded on ledger account 140200, and
(b) the financial inventory value of 14525 EUR that can be calculated by adding the green highlighted lines / values.
Prerequisites:
In order to apply this approach you have to make sure that your account structures incorporate the item financial dimension as exemplified in the following screenshot and…
… that all your items are setup with their corresponding financial dimension.
Note: To make sure that each newly setup item automatically gets linked to its item financial dimension, a minor code adjustment, as illustrated in the next screenshots, might be smart. In my example, I first generated a new method (“SetFDIT”) in the InventTable …
… and made the following adjustment to the insert method.
Note: If you setup inventory posting based on an item group level, it might be sufficient using the item group financial dimension for reconciliation purposes as illustrated in the next screenshot.
Yet, this approach has the disadvantage that it does not help you identifying errors/problems e.g. if a specific item transaction was recorded on a “wrong” ledger account. That is because using the item group financial dimension for reconciliation purposes does not allow you identifying which item caused the problem. For that reason, in an “ideal world”, it would be best working with both, an item group financial dimension and an item financial dimension as they give you the possibility to do your reconciliation at the item group level and “drill down” to the item level if you identify problems.
Advantages/Disadvantages of this reconciliation approach:
This first approach has several advantages and disadvantages. Let’s start having a look at the advantages:
- People working in the finance & controlling department are familiar with using and analyzing financial dimension values via the trial balance list page and other forms/reports in the General Ledger. They are thus familiar with this reconciliation approach.
- The trial balance list page provides some standard drill down functionalities that help identifying problems quickly.
- As Dynamics AX is a fully integrated ERP system, differences between inventory and General Ledger are by definition not possible. That is because each debit transaction requires a corresponding credit transaction. Reconciling inventory and General Ledger is thus a mere question of understanding the Dynamics AX posting framework and having a look at the “right” ledger accounts. As the trial balance list page does possibly give the best and fastest view at ledger accounts, this first alternative approach seems predestined for the inventory-ledger reconciliation job.
Now let’s have a look at the disadvantages of this approach:
- Depending on your system setup and number of items used in your company, the trial balance list page might need more time to open up and is limited to a single year. If you need to analyze data quickly for several years, the Management Reporter might be the better choice for doing the reconciliation job.
- You “lose” at least one financial dimension for reconciliation purposes,
- This approach works best if a code adjustment is implemented that makes sure that the financial item dimension is automatically filled once a new item is setup.
Approach 2: Inventory transaction inquiry forms
A second alternative approach for reconciling inventory and ledger balances is using inquiries forms that allow you analyzing inventory transactions. An example of such an inquiry form is the transactions form that can be found in the inventory management module. Please see the following screenshot.
Prerequisites:
A major disadvantage of this and other standard Dynamics AX forms is that information on ledger accounts cannot be personalized in a way that allows analyzing the transaction data quickly and easily. Please have a look at the next screenshot which illustrates that the inventory transaction form does only show the ledger information for the first record.
For the following illustrations, I thus created a new inventory transaction form denominated “LRE INVRE” that is based on the InventValueTransView that is also used for the inventory value cube. (Please note that you can use several other standard views, queries or tables for setting up this form). As you can identify from the next screenshot, this form allows you identifying the account and offset account for each inventory transaction.
Note: What is important in this respect is that you implement a form that allows you filtering those inventory transactions before the form is opened. Otherwise you might experience some performance issues due to the large number of transactions that need to be loaded when opening the form.
Once the form is implemented you can either analyze the inventory data directly in the form or export the results to Excel for a more detailed reconciliation analysis. Example:
Advantages/Disadvantages of this reconciliation approach:
After illustrating you the underlying principle of this second reconciliation approach, let’s have a look at the advantages and disadvantages of this approach.
Advantages:
- As shown above, this second approach allows you analyzing the ledger account and offset account for each inventory transaction recorded (provided that the transaction is already posted on ledger accounts).
- Different from the first approach, this second inventory reconciliation approach does also allow you analyzing inventory transactions that have not yet been posted to ledger accounts. This is a major advantage as you can e.g. quickly identify items that have been picked or registered but not packing slip updated and/or invoiced.
- A third advantage of this approach is that it can be implemented quickly and easily with a simple system modification. Moreover, users can make use of the standard Excel export functionalities that allow a detailed analysis of the inventory transactions.
- A final advantage of this approach is that you don’t necessarily “lose” a financial dimension for reconciliation purposes.
Disadvantages:
- A major disadvantage of this approach is that transaction level data are analyzed. Analyzing those data can consume lots of time unless you are able to filter those transactions e.g. by date, item or other features.
- When using this approach, users will most likely copy the data to Excel for a detailed analysis. Please remember that also Excel does have its limitations as exporting more than 1.1 mio. data at a time is generally not possible requiring incremental data exports.
- A final disadvantage of this approach is that it is in general more difficult identifying errors, as the data are not pre-aggregated. This means that you have to “slice and dice” the data in a way to identify inconsistent / incorrectly entered transactions. This last disadvantage can be overcome by using the next alternative approach for inventory reconciliation.
Approach 3: Inventory value cube
The third and last alternative method to reconcile inventory and General Ledger balances that I want to present in this post is using cubes. (Please note that I do not make a differentiation between the many BI instruments available for Dynamics AX, such as SSAS cubes, the various Power BI instruments and alike and simply call this approach “cube” solution to keep things as easy as possible).
For the following illustrations, I simply used the inventory value cube that ships with the standard Dynamics AX application. The next screenshot shows the result of an analysis that I did for the demo item “L2000” by browsing the cube in the SQL server management studio.
You can, of course, do the same in MS Excel simply by establishing a connection to this cube.
Example:
Note:
If you compare the data from the inventory value cube and the trial balance list page you will notice some differences in the detailed amounts that make up the total inventory balance of 14250 EUR. In my example you can identify ledger balances on the balance sheet accounts 200100 and 200140 in the Dynamics AX General Ledger that sit on the profit and loss account 600180 in the inventory value cube.
The underlying reason for this outcome is the “design” of the inventory value cube, which currently does not allow reconciling the physical inventory amount shown in the inventory value report. For that reason, you need to make some adjustments to the standard inventory value cube if you want to use it for reconciling purposes in your Dynamics AX environment.
Advantages/Disadvantages of this reconciliation approach:
Now, let’s also have a look at the advantages and disadvantages of using this approach.
Advantages:
- The cube solution is probably the most powerful and fastest solution for reconciling inventory and ledger balances as users can easily analyze the data they want e.g. in Excel.
- Different from the previous approach, the cube solution can easily handle large data volumes as transactional data are pre-aggregated. At the same time, cube “drill through” features allow you taking a detailed look at the transactional data simply by doing a double click on the data.
Disadvantages:
- A first disadvantage of this approach is that you need a cube expert for implementing cubes, as ordinary finance & controlling users regularly do not have sufficient system rights and the expertise to implement those cubes.
- Another disadvantage is that not all the standard inventory value cube measures, calculations and KPIs can be used “out of the box” and need some modification before they can be used in a live environment.
Summary
Because of the many problems of the inventory value report it is prudent to make use of alternative inventory reconciliation approaches.
Within this blog post I showed you three possible alternative reconciliation approaches that can help you overcoming the weaknesses of the inventory value report.
From a finance and controlling perspective, the first approach seems to be the one that should always be implemented as it can be setup by ordinary finance & controlling users without making a system modification.
If differences between inventory and General Ledger arise, using the second and/or third approach seem to be inevitable.
Due to its importance and ease of implementation, I will show you a detailed step-by-step example on how to reconcile production vouchers by using the first “financial dimension approach” within the next post.
