After explaining details of the inventory value report in the previous posts, I will now put a focus on the major pitfalls of the inventory value report and the related potential conflict reports based on the following sample transactions that have been entered for a specific inventory item (“L1000”) in January 2015:
After recording the sample transactions in Dynamics AX, I run the inventory value report and got the following outcome:
The total inventory quantities and values shown in the inventory value report (highlighted in yellow color) can be explained by the following yellow-highlighted inventory transactions:
As you can identify from the yellow-highlighted lines in the previous screenshot, not all item transactions are included in the inventory value report. The on-hand inventory form possibly provides a better view on the sample item transactions and allows a straightforward reconciliation with the inventory value report data. Please see the following screenshot.
As you can identify from the previous two screenshots, only physically and/or financially updated inventory transactions are included in the inventory value report. Inventory transactions that do not have a related physical and/or financial value, such as purchase order registrations or sales order picks are excluded from the inventory value report.
To demonstrate the major problems of the inventory value report and the related potential conflicts report in the following sub-sections, I recorded the very same transactions that have been recorded for item “L1000” in January 2015 for a second test item “L1100” in February 2015 and got the following result:
Problem 1: Date interval
One of the first things that I tried to do was analyzing the transactions of the first test item (“L1000”) only. As all transactions for this item have been recorded in January 2015, I run the inventory value report for the following date interval.
Outcome:
With this date restriction in place, the inventory value report showed the following data:
What you can identify from this inventory value report is that the ledger transactions that are shown at the end of the report are limited to January 2015 and that a variance of 14250 EUR arose. This variance is caused by the inventory transactions for the second test item “L1100” that were recorded in February 2015.
This outcome is very important for reconciliation purposes as only the General Ledger transactions but not the inventory transactions are influenced by the date interval entered for the inventory value report. You can identify this by the blue-highlighted line in the upper part of the inventory value report. This line shows the item transactions for the second test item “L1100” that have been recorded in February 2015.
A direct result of this outcome is that you have to run the inventory value report at month-end before any future inventory transactions have been entered for the next month if you want to reconcile your General Ledger with your inventory balance for the end of a specific month. That is because the inventory value report does not show inventory values as of a specific date in the past.
Please note that I tried to get around the date interval problem by applying the following additional filter on the inventory transactions:
Unfortunately, this second filter on the inventory transactions is not picked up by the inventory value report and results in the same outcome as before.
Remark:
All transactions and results presented in this blog post were executed on a Dynamics AX 2012 R3 “Contoso” demo system that can be downloaded from Partnersource. Numerous additional tests showed that the identified first problem with the date interval does not exist in each and every system but rather depends on the Dynamics AX 2012 version used and the hotfixes implemented. Whether or not you will face the first problem described in your system can thus not be answered conclusively here.
In order to understand the identified problem better, I executed a search for problems with the inventory value report on the Lifecycle website and got the following result:
What you can see from the previous screenshot is that several unresolved issues with the inventory value report seem to exist and that you have to take utmost care when using this report.
Problem 2: Setup summary ledger accounts
A second problem that I observed with the inventory value report is that no guidance/white paper or something alike is available for the setup of the summary ledger accounts that are linked to the inventory value report. As a result, you always run the risk that you miss the one or the other ledger account resulting in a reported variance that actually does not exist.
Example:
For testing purposes I setup a new inventory summary account (“149999b”) where I excluded the ledger account that is used for recording packing slip transactions for the item group “Audio” and that is included in the original summary account “149999”. Please have a look at the next screenshot.
Outcome (1):
With the newly setup inventory summary account in place, the inventory value report shows a discrepancy of 900 EUR between the inventory and ledger balance. This amount is identical to the ledger balance of account 140201 that was explicitly excluded for testing purposes. Unfortunately, the inventory value report does not give any hint that this account is missing.
Outcome (2):
As the inventory value report did not include any hint on the missing ledger account, I run the report on potential conflicts between inventory and General Ledger and got the following result:
As you can identify from the previous screenshot, the potential conflicts report provides some additional information on the underlying transactions that caused the discrepancy. Yet, this report does also not give any hint why the discrepancy arose; that is, which ledger account is left out.
Problem 3: Report filters
The next test that I did was trying to filter the inventory value report on a specific item (“resource”) as shown in the next screen-print.
Outcome:
The inventory value report does indeed apply the resource (item) filter but does now show a discrepancy between General Ledger and inventory that does not exist and is only caused by the fact that the ledger transactions are not filtered correspondingly.
This outcome and the previously identified problem with the date filters illustrate that filtering transactions in the inventory value report based on date intervals, items, item groups and alike needs to be done with utmost care and does not necessarily provide you with the outcome you expect.
Problem 4: Report filters potential conflicts report
After trying to filter transaction in the inventory value report, I tried the same with the potential conflicts report. Please see the next screenshot.
Outcome:
The result of this test was the following report that shows a discrepancy between inventory and General Ledger but does not provide any additional information.
I find this outcome very confusing as the discrepancy illustrated indicates that something must be wrong but that no additional details for the identified discrepancy can be provided. The issue here seems to be once again that the report filter for the item “L1100” did not similarly filter the corresponding ledger transactions.
Problem 5: Subcontracting service items (1)
The next issue that I experienced with the inventory value report and the potential conflicts report is related to service items used for subcontracting. According to the Dynamics AX manuals and TechNet, subcontracting items shall be setup as “stocked service items”. See for example this Website.
To test how subcontracting service items are incorporated into the inventory value report, I setup a basic finished product “L6000” that consists of an item “L6001” and a subcontracting service item “L6002”. The next screenshot provides an overview on the finished product and its components.
Please note that the subcontracting service item is setup with an item model group that has the physical and financial ledger integration parameters activated. If those parameters are not activated, the ledger transactions generated when recording production postings are wrong / incomplete from a finance & controlling perspective.
With this setup in place, the inventory value report shows a variance of 10 EUR after the production order is ended but before the purchase order for the subcontracting item is invoice updated.
The underlying reason for the discrepancy shown in the inventory value report is because service items are by default excluded from the inventory value report. As long as those items are not invoice updated, the ledger postings are incomplete with the result that the inventory value report shows a discrepancy between the General Ledger and inventory values. Please note that the potential conflicts report shows the same discrepancy.
Once the purchase order invoice for the subcontracting item is posted, the discrepancies illustrated in the inventory value and the potential conflicts reports disappear.
Note that the difference illustrated here does not arise if the purchase order invoice for the subcontracting item is invoice updated before the production order is ended.
Irrespective of the last qualification, the finding that the inventory value report shows a variance unless the purchase order invoice for the subcontracting item is invoice updated is a major disadvantage – especially in companies with a continuous production – as the inventory value report and the potential conflict report will most likely always show a discrepancy between the inventory and General Ledger balances.
Problem 6: Subcontracting service items (2)
A second issue with subcontracting service items is that the difference between the ledger balance and the inventory balance does not disappear if (a) the production order is ended before the purchase order invoice for the subcontracting item is invoice updated and (b) the original purchase order price for the subcontracting item differs from the price the vendor finally charges. The following illustrations exemplify this problem.
Example:
The subcontracting item vendor charges our company a 2 EUR higher price than originally calculated.
After the production order is finished and the purchase order is invoice updated, the 2 EUR price variance remains as a difference in the inventory value report as illustrated in the next screenshot.
This difference disappears with the recalculation of the inventory.
Note that the difference illustrated here does not arise if the purchase order invoice for the subcontracting item is invoice updated before the production order is ended.
Summary:
The various problems exemplified in this blog post allow concluding that the inventory value report and the potential conflict report are not suitable for the reconciliation of inventory and ledger balances especially in companies with a permanent “around the clock” production. That is because of the identified problems with the report filters and the problems identified in relationship to subcontracting service items.
What is more, even if the inventory value report is run in batch mode, the report often runs – depending on the number of items you use in your environment – around 10-15 minutes. This fact and the lack of drill-down functionalities do not allow executing detailed inventory value analyses.
In the next blog posts I will provide you some hints on how to avoid that differences between inventory and ledger arise in the first place and what other reconciliation tools exist that you might use for reconciling ledger and inventory balances.
