Reviews and corrects ledger-to-subledger alignment in D365 by fixing posting configurations, inventory profiles, reconciliation logic, GL mapping, and critical reporting procedures.
Identifying Inventory Discrepancies in D365 for Finance and Operations, Part II – Using the Inventory Value Report
Posted on: April 2, 2018 | By: Jarrod Kraemer | Microsoft Dynamics AX/365
Authored by: Rosey McAdams
In part one of this series, we explained how to identify what is causing a discrepancy between the balance in your general ledger inventory accounts and the inventory subledger in Dynamics 365 for Operations (and prior versions of Dynamics AX) using the Potential Conflicts Report. As promised, we are now going to introduce a second method that we use to help determine the cause of inventory discrepancies, utilizing the inventory value report.
While we saw how helpful the Potential Conflicts Report can be to identify inventory discrepancies between the general ledger and subledger, sometimes the problems can be so vast that the potential value report can be overwhelming, or sometimes the messages in the potential conflicts report can be confusing, or sometimes users can just feel more comfortable using the inventory value report to help with inventory reconciliation. Whatever the reason you may prefer the inventory value report over the potential conflict report, the process we will outline in this blog post will help you to understand sources of conflict between your subledger and ledger using the inventory value report.
To utilize this process, we must first pick a date in time that we want to use as our starting point. This will give you a basis to determine the true delta between your inventory value report and your general ledger no matter if the starting balances match or not (which they probably do not otherwise you would not be reading this blog!) For example – let’s say we would like to use 03/01/2018 as our starting point.
For this process we will also need to determine an amount of time we want to start with for defining our discrepancy date range: 1 period, 1 month, 1 week, 1 day? The larger the time frame we choose the harder it will be to figure out where exactly the discrepancy is coming from. The smaller the time frame we may not even find a discrepancy – however if you have a smaller time frame the easier it will be to run through all of the transactions in that time frame to find out where your issue could be coming from. In our example we are going to look at a 1-day time frame.
1) Navigate to General Ledger > Trial Balance and set your parameters to use 3/1/2018 as the From date and as the To date (only looking at one day) and click Calculate balances
2) Filter the accounts displayed to only show your inventory accounts – in my example I am only going to look at my finished good inventory account
3) Calculate the Closing balance – Opening balance for each account separately. This will give you the change in the inventory account since the beginning of 3/1/2018. And remember this number.
In our example we can see that the Closing balance – Opening Balance = $5,000, remember this number.
4) Navigate to the inventory value report:
Inventory Management > Reports > Status > Inventory Value > inventory value report
5) Run the inventory value report for 2/28/2018 to 2/28/2018 (the day before your starting point) to get our opening balances. Run separately for each resource group(s) (item group(s)) that represents a single inventory account. (they may not match what you got in GL because there was an imbalance before this).
*For more info on how to setup an inventory value report please visit our Understanding the Inventory Value Report Blog Series.
Opening Balance on 3/1/2018: $2,905,692.13
6) Next run the inventory value report with the to date as the starting point date to get our closing balances (3/1/2018). Again, run separately for each resource group (item group) that represents a single inventory account (they may not match what you got in GL because there was an imbalance before this).
Closing Balance on 3/1/2018: $2,905,692.1
7) Calculate the closing balance – opening balance that you got in the inventory value report for each resource group. In our example our closing balance – opening balance = $0.
The Δ between the opening and closing balance in both the subledger report (step 7) and the general ledger (step 3) should match! If they do not we have a discrepancy during our period.
In our example the deltas do not match, we have a discrepancy of $5,000! We must now go through our GL transactions and look for transactions equaling up to $5,000 that may seem out of place on the date that we ran our comparison on.
My GL research is showing that there is only one transaction in this account on this day, and it is for exactly $5,000, and it is a general journal entry directly to the account based on my voucher number. This is certainly the cause of my discrepancy! I will need to reverse this transaction!
Hopefully you found this blog series and this method for identifying inventory discrepancies useful. We have found this method especially useful in many, many inventory discrepancy resolution projects! For additional information or help using this process please feel free to reach out to us at info@loganconsulting.com or (312) 345-8817.
All the best!
Logan Consulting
www.loganconsulting.com
