Understanding the Inventory Value Report in Microsoft Dynamics AX 2012: Part 2
Posted on: May 20, 2015 | By: Jarrod Kraemer | Microsoft Dynamics AX/365
Glad to see that part one of this blog was just so interesting you had to come back for more! In this half of our two part blog on the AX 2012 inventory value report, we are going to take a deeper dive into what the inventory value report is actually telling you as well as show you how to use the inventory value report to reconcile your inventory with your GL. For this explanation we are going to look at an inventory value report with the setup parameters pictured below (for more information on setting up inventory value report IDs see part one of this blog).
In this setup we have chosen to print cumulative accounting values for comparison for the inventory account 149999. You can see in the screenshots below that account 149999 is a total physical inventory account that is made up of the total amounts of the raw materials and finished goods inventory accounts. Just wanted to make a note of this because it will be important when we look at how to use this report for reconciliation purposes later in this post.
Running the Inventory Value Report
So, what is the inventory value report actually telling us? We are going to dissect this report to understand exactly what information it is giving us. To run the inventory value report click on Inventory Value in the Reports section of the Inventory Management area page. The following form will appear.
Select from and to dates (if the from date is left blank it will automatically be set to the same date as the to date). Note that the inventory value report gives you the value of inventory that is specified on the to date and the from date will not change your actual results unless you want to see opening balances or view transactions. I would recommend setting the from and to dates to be the same so that the report will take less time to populate. You can view the report for specific resources, resource groups, or dimensions if you so choose. Click OK to create your report. Running the inventory value report with the above setup gives us the results pictured below. (This report is based on demo data, so will not look like real life data.)
We are going to go through this report piece by piece to explain what it is telling us. In the top right corner you can see that the From is blank and the To is set to 12/2/2014. This means that the report is giving us the value of the inventory at the end of the day on 12/2/2014 – so this should be reconciled with end of day trial balances for 12/2/2014. We have run the report for resource groups and totals. As we can see the report gives us three rows – Audio ending balance, audio resource group, and material totals. The ending balance and the column below it will always be the same amount. Since audio is the only resource group (item group) with any posted inventory values the material totals is equal to this amount, if there were more resource groups displayed with values the material totals row values would be the sum of all resource group values.
Now let’s take a look at what all of our columns are telling us…
Inventory: Financial Quantity – this is telling us the quantity (with a unit like LBs/ea) of inventory we have that has been invoiced (for example 5 pieces).
Inventory: Financial Amount – this is telling us the dollar amount value of inventory that has been invoiced (for example $10).
Inventory: Physical Quantity Posted – this is telling us the quantity (with a unit like LBs/ea) of inventory we have that has been received or shipped but not invoiced. (Could also be picked for order or reported as finished but not production ended.)
Inventory: Physical Amount Posted – this is telling us the dollar amount value of inventory that has been received or shipped but not invoiced. (Could also be picked for order or reported as finished but not production ended.)
Inventory: Physical Quantity Not Posted – this is telling us the quantity of any inventory that has transactions but was never posted to the GL.
Inventory: Physical Amount Not Posted – this is telling us the dollar amount value of inventory transactions that were never posted to the GL – this value will not reconcile with your GL values – DO NOT INCLUDE VALUE FOR RECONCILIATION!!
Inventory: Quantity – this is telling us the TOTAL quantity (with a unit like LBs/ea) of all of the columns included in the report. This includes the physical amount not posted. This value should be used for comparison in physical inventory counts.
Inventory: Amount – this is telling us the TOTAL dollar amount value of all columns included in the report. IF YOU INCLUDED PHYSICAL AMOUNT NOT POSTED SUBTRACT THIS FROM YOUR TOTAL BEFORE USING VALUE TO RECONCILE.
Average unit cost – this is simply the total quantity (sum of all three quantity columns) divided by the total amount (sum of all three amount columns) to give you a simplified average unit cost per resource group.
*One may pose the question – why do we have the columns physical quantity/amount not posted – these columns will have value if your company does not post receipts, picking lists, or report as finished journals to the ledger. If for any reason you do not have these parameters selected there will be value in this column. This is not recommended, so if you have value in these columns you may want to consider changing your setup.
On the bottom of the report you see a row for report summary. This will give us the total values of each column for all of your inventory. Beneath the report summary you see a section for inventory ledger accounts. This displays the values for both the raw materials inventory account and the finished goods inventory account with their total even though we only specified one account to be displayed. These two account balances are printed because we specified to display an account that is equal to the total of these two accounts. This is very useful for reconciling by resource group.
Using the Inventory Value Report Effectively
1. To state the value of our inventory before performing an inventory count – use the values in the report to use as a baseline to compare with your counting journals-can be used to state the accuracy of your inventory before a count. Use the following columns for this purpose: Physical qty posted + financial qty posted + physical qty not posted.
2. To reconcile physical inventory values with GL balances. Compare financial value with GL inventory accounts, and physical value with un-invoiced inventory accounts. I.E. If you have separate accounts for picking lists and issues – compare the physical value with whatever account picking lists are being posted to, and the financial value with whatever account the issues and receipts are being posted to. If you only have one account that both are posting to use the total of the two columns (the Amount column) to compare. Do not include physical amount not posted for reconciling with accounts! If you have selected to print inventory accounts at the bottom of your report you will not need to jump to the GL module and print out a trial balance, you can see everything right from this report. Since we have created a total inventory account that combines the raw materials and finished goods inventory accounts we can reconcile each resource group separately – this way you can pinpoint if one resource group is creating more problems. If you have major issues with your reconciliation I would suggest using this report in conjunction with the Potential Conflicts report. I also find it useful to go back and run this for each week in the period that you are trying to reconcile, so that you have a smaller data set to see where your issues are coming from. This way you can see more clearly which week the problem started-you can even go deeper and run it daily.
The inventory value report is a very powerful tool, IF you understand what it is telling you. It can be helpful not only for cost controllers, but for inventory controllers and financial controllers as well. Hopefully you found this information helpful and will start to use this report more effectively. For additional information please feel free to contact your Chicago based Dynamics AX Partners here.
25 Brilliant Ideas to Outsmart Your Competition with Microsoft Dynamics
Top 10 Inventory & Operations Decisions Distributors Are Making Blind
2020 Nucleus Research Report on ERP Technology