How would you make a proper Inventory Aging report?

Miklos_Hollender

I.e. to be able to tell how old is your inventory. Used by auditors to devaluate old stock, used by banks to see if you sell your stuff well enough to give you a loan to buy more stuff.

Aging as of today would be easy: list of Item Ledger Entries that have Remaining Quantity. Value = Cost Amount (Actual) * Remaining Quantity / Quantity. Then just put these in age buckets per posting date. Easy.

But it neve happens this way. More like someone tells you in November make an aging report for the end of September. Thus not only filter for Posting Date, you also should some figure out how much the Remaining Quantity was back then and it seems downright impossible.

I was trying to solve it by item application entries, such as take only positive Item Ledger Entries and see what is applied out of them. Filter for Inbound Item Entry No. Sum up. Then there is the additional difficulty of tracking back transfers for the original purchase because only this gives the correct age, but that is solvable the problem is that many Item Application Entries don't make sense. Consider this: a sales and a sales return applied from a purchase. The purchase is 20 quantity and has 0 as a remainign quantity so the aging value should be 0.

Entry No. Item Ledger Entry No. Inbound Item Entry No. Outbound Item Entry No. Quantity
27022 24291 24291 24287 20
27025 24294 24291 24294 -20

The second is a sales applied out of this purchase, the first is the sales return, a return of the same sales applied back. What?

I see no way to consistently say how much this purchase had on stock at a given time back.

If you ever found any solution other than praying IT did not yet throw out the backup made that day please tell.

jglathe

Hi Miklos,

we have a "kinda" inventory aging report. It is calculating net change until date for quantity, and the last movement dates for shipment and receipt (and consumption). We also have the value at date, it is simply the sum of all value entries (cost amount (actual) + cost amount (expected)) until date. Barring some posting oddities, this value amounts to the inventory value at date, including revaluations.

This report is the base for the devaluation calculations.

Your calculation seems to be somewhat different... remaining cost at date, what for? Isn't it more important if an item is shifting, meaning when was the last consumption or sales shipment?
If you need your number, there is a method to get it. The costing rollup update of november 2012 contains a modified item ledger entry table. It has built-in functions for remaining quantity at date.

with best regards

Jens

Alex_Chow

It's not impossible. We've done this and it works fine.

Basically we took the Inventory Valuation report (the US version) and combined it with the A/R Aging report.

Basically, each bucket would be calculated separately. The report is slow, but it works perfectly.

Miklos_Hollender

@jglathe because from the viewpoint of an auditor or bank or manager an Inventory Aging is the same logic as Customer Aging. Spot half a million euros 6 months old, look into it. That is how they work. Your report gives a hint about slow moving stock all right, but it does not really determine its aging accurately, just hints on it. Just because its last purchase was 3 months ago there could be another purchase a year old sitting there. Thanks for the hint about costing update I will ask about it.

@Alex can you tell me if the version list of the US version indicates a localization in the Item Ledger Entry / Application Entry tables? Some kind of "remaining quantity as a flowfield" or a function like "remaining quantity at date" because without this it either cannot be accurate or must be ran at the date when it is needed but cannot be ran going back. I know because I tried it in W1 and there are no ways to determine much of a given purchase was sold up to a past date without some really complicated Application Entry magic. W1 Inventory Valuation is really simple not much more than just a Value Entry summing with a really simple Application Entry lookup merely for determining the sign. It does not match purchases and sales, it does not determine how much of a given purchase was still on stock at a given date back in time.