Inventory Aging Report in Enterprise

jbacher · ‎March 08, 2018

We've used Enterprise for nearly 10 years now. The biggest issue that we've come across is finding out how old our individual inventory items are. I've just discovered that I can run an Physical Inventory Worksheet and customize it with columns for Purchase Date and Date Received. However, the fields are completely blank. What use is it to have the option if there's no data?

As a small retail shop, it's imperative to move 'dead wood'. I've asked this question in the past and have gotten nowhere. But, here goes again. Is there a way create a listing of in inventory so that we can see how long items have been here? And, if not, why not?

We enter all of our inventory when the invoice is received. Why is the invoice date not connected to the inventory item? Somehow, I think the capability is there, since an individual item can have a Quick Report done and the dates of any transaction are shown - sales or receipt of new stock. So, why not a report for all inventory?

Certainly, we are not the only business which would benefit from such capability. Or, does everyone just look at their stock and guess on its age? I look forward to a lively discussion on this.

(Title has been edited by moderator for clarity)

Rustler · ‎March 09, 2018

QBDT reporting has always been that frustrating, they allow you to select fields as column headers but will not display info. But in your report there is a valid reason. The report is a summary report, total qty on hand now - purchase dates are details, and there may be many purchase dates contributing to the present qty on hand.

QBDT uses average cost for inventory, so there is no real reason to worry about date per purchase, which is part of FIFO or LIFO typically. Yes purchase date is in the file though, as you intimated in the quick report.

I have not used this work around in a long time, but I expect it will work for inventory, I usually use it for customers
use menu file>utilities>export>lists to iif files, select inventory list and save

open excel, and open the iif file you saved

the time stamp field in the iif list export is a column, usually column D
insert a new column e
format that column as date, 3/14/01 1:30pm (format string shown in my excel version)

if the first time stamp is in cell D37, in E 37 enter this formula
=(D37/86400)+25569
and hit enter

copy that formula down the column

the time stamp field is the first created date usually

View solution in original post

WineGuy · ‎March 09, 2018

Hello jbacher. I would suggest you create different skus/item codes for inventory purchased at different times. You could make the codes month or year specific as needed. If all the inventory is unperishable and identical it doesn't really matter when you sell it. QB Enterprise has the FIFO feature so you can track the oldest inventory. Good luck!

View solution in original post

Rustler · ‎March 09, 2018

QBDT reporting has always been that frustrating, they allow you to select fields as column headers but will not display info. But in your report there is a valid reason. The report is a summary report, total qty on hand now - purchase dates are details, and there may be many purchase dates contributing to the present qty on hand.

QBDT uses average cost for inventory, so there is no real reason to worry about date per purchase, which is part of FIFO or LIFO typically. Yes purchase date is in the file though, as you intimated in the quick report.

I have not used this work around in a long time, but I expect it will work for inventory, I usually use it for customers
use menu file>utilities>export>lists to iif files, select inventory list and save

open excel, and open the iif file you saved

the time stamp field in the iif list export is a column, usually column D
insert a new column e
format that column as date, 3/14/01 1:30pm (format string shown in my excel version)

if the first time stamp is in cell D37, in E 37 enter this formula
=(D37/86400)+25569
and hit enter

copy that formula down the column

the time stamp field is the first created date usually

jbacher · ‎March 09, 2018

Thank you, Rustler. I'll try this out. I still believe that Intuit ought to build some inventory status reporting into QB, rather that expecting its users to create workarounds. But, this may be the temporary solution until that happens.

Much obliged.

WineGuy · ‎March 09, 2018

Hello jbacher. I would suggest you create different skus/item codes for inventory purchased at different times. You could make the codes month or year specific as needed. If all the inventory is unperishable and identical it doesn't really matter when you sell it. QB Enterprise has the FIFO feature so you can track the oldest inventory. Good luck!

amyp · ‎December 31, 2018

I couldn't agree more.

The inventory reporting is HORRIBLE in QB.

The information HAS to be there!

I can't get a slow moving inventory report.

My fix:

run a sales report of items sold within a given time period-export to excel

run the inventory valuation report as of a certain date or physical inventory count-export to excel

use funtions (conditional formatting or index or other) in excel to compare the two reports and identify items that HAVE sold within the given time frame thus what has NOT sold should be a slow-moving item.

Another frustration-why can't I get a physical count that shows the inventory on hand valuation? Don't want to count ALL the inventory but want to count 80-90% of inventory value.

Instead I have to export to excel and use excel to calculate same. UGH.

Amy

aoksales · ‎January 28, 2019

We use the serial number feature and have not found a way to generate an aging report even exporting it to excel. This should be a standard report and crucial if your items have year and serial number associated with them.

rajawattoo · ‎May 29, 2019

It is very easy if you entered inventory with serial number

Just go to report to company and financials to Balance sheet then open inventory stock

you will see purchase stock as well sold stock customize serial numbers

export it on excel

select serial number column and highlight duplicate then filter with color no fill

you will find the unsold closing stock serial numbers. now write todays date at any place and apply formula today date with dollar sign minus bill date and drag down then select numbers you will see the correct aging

contact at [email address removed]

bgoodrud · ‎February 06, 2020

This didn't work because that date stamp in the export file was the very first purchase date of the item not the most recent or the oldest on the floor. The weird thing is the very last column in the export was PurchaseDate but there is nothing in that field on any of my items.

MirriamM · ‎February 06, 2020

Hello there, @bgoodrud. Thanks for the details.

Right now, the type of scenario that you have is unavailable in QuickBooks Desktop (QBDT). Since the information that can be track is only the quantity which we can see they we're purchased but unable to identify which is which.

To ensure your idea is heard, I'll gladly pass your feedback along. They review ideas on how to design QuickBooks around your needs.

Moreover, I also suggest to directly send a message to @Rustler, to get tips if the workaround used was the processes you've followed.

In case you need tips in the future, visit our QuickBooks Help Articles site.

Reach back out to me by commenting below if you have any questions concerning QuickBooks. I'm always here to help.

Topics

Training

Community

Resources

Inventory Aging Report in Enterprise