Limitations for Inventory Aging Report
The Inventory Aging Report is a very valuable tool. It is primarily used to determine the aging of the inventory for all locations for the current date, but you could use it for a specific location or use it to determine the inventory on a certain date in the past. However, we derive Inventory Aging reports based on the Inventory Transaction Audit Trail and there may be certain limitations where the result may not be as expected. The following are known limitations of Inventory Aging reports that you should be aware.
Inventory Aging Report for a Single Location
Inventory Aging reports determine inventory based on the date when the inventory was received. This would be receiving from your Purchase Order module or from the Inventory Management module. For a company that has multiple locations, the inventory is usually received at one location and transferred to another location. So when you run inventory aging for all locations, the inventory age is determined based on the receiving date regardless of which location. But when you run inventory aging for a single location, the inventory in that location may have come from a transfer. If we use the transfer-in day to determine aging, obviously the aging information is distorted. You may not have a choice since you may not receive inventory in that location. See the following KB article that discusses why inventory aging for a single location will be different than multiple locations:
In some situations, the inventory for a location may come from a COP credit memo (i.e., service location). When you run the Inventory Aging report for that location, it will not show correct information. Here is a support question from one user:
I ran the Inventory Aging Report for our customer service warehouse to see how long certain items have been there and received strange results. Maybe I just don't understand the value of this report. Take the following item 15045...
According to the report, it has been there over 999 days if I am reading this correctly.
When I look at the stock status on it, I get this...
Shouldn't the report reflect that this item has been in on 6/27/2018 which is less than 60 days? I'm not sure how it works.
The reason for this confusion is because this is a special condition. In an aging report, we try to determine when the inventory is received to determine the age of the inventory. We look at the inventory received from PO, not the inventory returned from COP for credit. This is a reasonable assumption if you look at the following transaction:
90 days ago, you received qty 1 for item A
5 days ago, you sold this qty 1 for item A
1 day ago, you entered a credit memo and received the item A back
Now you run an aging for this item. Should it show 90 days or 1 day? The answer is obviously 90 days. To do this, we have to ignore the receiving from the COP side due to the credit memo adjustment.
In this case, the service warehouse would be receiving through a credit memo. But we have no way to know that we should not ignore the credit memo like the scenario above. So we ignore the credit memo. Now, since we don’t see any PO receiving for this item at this location, we just assume the inventory aging is the longest, which is 999 days.
Again, the aging report tries to figure out the data the best it can. Generally the Inventory Aging Report does not work well for a single location because the receiving information is often missing with a single location perspective.
90 days ago, you received qty 1 for item A
5 days ago, you sold this qty 1 for item A
1 day ago, you entered a credit memo and received the item A back
Now you run an aging for this item. Should it show 90 days or 1 day? The answer is obviously 90 days. To do this, we have to ignore the receiving from the COP side due to the credit memo adjustment.
In this case, the service warehouse would be receiving through a credit memo. But we have no way to know that we should not ignore the credit memo like the scenario above. So we ignore the credit memo. Now, since we don’t see any PO receiving for this item at this location, we just assume the inventory aging is the longest, which is 999 days.
Again, the aging report tries to figure out the data the best it can. Generally the Inventory Aging Report does not work well for a single location because the receiving information is often missing with a single location perspective.
Inventory Aging Report for A Previous Date
Since the inventory aging report can show the inventory quantity balance of a certain date, some users use it as a stock status report for a past date. They may use this to reconcile with the GL inventory balance, but users have to realize there is a certain limitation with this approach:
- In order to show the inventory balance of a past date, the inventory aging report must derive its data from the Inventory Transaction Audit Trail table. The inventory balance is based on the data we have in the Inventory Transaction Audit Trail. It is not always 100% accurate. For example, inventory transaction audit trail records could be purged.
- Unlike the inventory aging report for the current date, the process to figure out the inventory balance of a past date needs to go through the Inventory Transaction Audit Trail table. This can significantly slow down the report.
If you wish to get stock status valuation for a past date, the best way to do it is to use "Stock Status Snapshot."
Here is a support question from one of our users on this subject:
Once again the Inventory Aging Report is resulting in some crazy results, including items at locations that haven't existed for over a decade. Is there any other way to get an inventory by location for a previous date? I think I could take the IMINVTRX file, filter out all transactions after the date in question and for each item_no and location take the last record recorded and use the New_Qty_OH and New_Avg_Cost.
Here is our response to this question:
1. Inventory Aging Report
The Inventory Aging Report was originally designed for aging current or frozen dates of inventory at “All” location environments.
After that, users requested to expand the features to allow entering a specific date and single location. That would benefit the accounting department and allow them to get period or year end Inventory value.
It is a challenge to determine the QTY-ON-HAND and COST on a particular date and calculate aging as well. In most situations, IMINVTRX does not record “Date” sequence. Users might enter the transaction back to previous date or previous month.
We would like to make any necessary improvements if you can provide the example to us.
2. Stock Status Snapshot
Elliott provides a Stock Status Snapshot feature in which the user may print stock status report + Snapshot (daily or monthly) from Deferred Processing. You may print the report back to any Snapshot database.
Please see link as http://support.elliott.com/knowledgebase/articles/835167-feature-inventory-snapshot
EMK