Solved

Inventory Aging Report in Enterprise

Forum|Forum|8 years ago
March 8, 2018
4 replies
59 views

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)

Best answer by Rustler

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

RustlerAnswer

Level 15

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

J

jbacherAuthor

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.

W

WineGuy

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!

A

amyp

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

A

aoksales

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.

R

rajawattoo

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]

C

collinsm246

If you are using lot number then add the date at the end of the lot number you received the inventory lot number 12345 can become lot number 12345-011526 indicating you received this lot on January 15, 2026.

when you invoice you will remove by lot number and voila remaining inventory will be dated.

Sign up

Login with SSO

Let's get you signed in

Login with SSO