Does Acumatica have an Inventory Aging report?
Well, not out-of-the-box, but we can build one.
Acumatica does have the Inventory Valuation (IN615500) report which looks like this:
But, what if we want to see the Total Cost broken out between different Aging Buckets? 30 days, 60 days, 90 days, etc.
We can list the aging buckets as separate columns going across the top.
In this post, I’m going to show you the end result of an Inventory Aging report in a Generic Inquiry in Acumatica, with a Pivot Table that puts the values into Aging Bucket columns.
If you’d like to learn how to build this example, note that there is a lesson my Building Generic Inquiries & Pivot Tables course (click here) which walks you through the steps.
We don’t have enough information on the Inventory Valuation (IN615500) report because we can’t see the inventory layers. The inventory layers are needed if we want to know how old each layer is.
Taking a look at the Stock Items (IN202500) screen, we can see that the are four options in the Valuation Method field:
Standard
Average
FIFO
Specific
Unfortunately, only FIFO and Specific store inventory layers. So, if you are using Standard or Average for your Valuation Method, then the rest of this post isn’t going to be that interesting to you because you won’t be able to use this approach for building an Inventory Aging report.
As long as you are using FIFO or Specific, please read on!
Here is the Inventory Aging Report Generic Inquiry:
Notice how the Inventory ID of 001 only has one row and the value in the Date Rcvd column is 1/1/1900 (which is basically empty). This is because the Valuation Method is Average. This item won’t be included in our aging.
However, the Inventory ID of 002 has multiple rows, one for each value in the Date Rcvd column, and the values in the Date Rcvd column are meaningful. This is because the Valuation Method is FIFO.
The Inventory ID of AAMACHINE1 has multiple rows as well, but there can be multiple rows on one Date Rcvd if there are different values in the Lot/Serial Nbr. column. This is because the Valuation Method is Specific.
The Days Old column calculates the number of days between Date Rcvd and today.
The Days Old Aging Bucket column takes the Days Old value and puts it into an aging bucket.
This Generic Inquiry gives us the data that we need.
Then we can add a Pivot Table called BY AGING BUCKET to the Generic Inquiry which puts the Qty. On Hand and Total Cost values into aging buckets as columns across the top. Here is the Pivot Table:
The QOH column ages the Qty. On Hand values and the Cost column ages the Total Cost values.
The 00 – 29 days, 30 – 59 days, and 90 days or more aging buckets across the top can be adjusted to whatever you want in the definition of the calculated column in the Generic Inquiry.
The Warehouse on the left-hand side can be expanded to see the aging buckets per individual item like this:
And the totals in the Pivot Table can be tied-out to the totals on the Inventory Valuation (IN615500) report:
The grand totals don’t match because the INTR Warehouse isn’t included in the Inventory Valuation (IN615500) report.
Taking the $11,787,683.92 grand total from the Pivot Table and subtracting the $250.00 value for the INTR Warehouse, $11,787,683.92 – $250.00 = $11,787,433.92 which is the grand total in the Inventory Valuation (IN615500) report.
Want to learn how to build this?
You’ll learn how to build the example above.