Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 269 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
How would you make a proper Inventory Aging report?
Miklos_Hollender
Member Posts: 1,598
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.
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.
0
Comments
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
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.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
@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.