Inventory tracking in Excel

[Bill Postlewait]

Hello everyone,
I thought I was doing pretty well in Excel, but after
reading a few pages of posts I can see that I have a lot
to learn.
I have developed a workbook to do job estimating and job
costing. Next step is to add inventory tracking.
I use a form I've created to do job costs which uses
Drop-down boxes in column "A" to get part numbers from
the database, and enter quantity used in
column "B".
In my parts database, column "A" has part numbers, and
cloumn "H" will contain inventory quantities.
I'd like to update the inventory by subtracting the
quantities used on the job cost form from existing
quantities in the inventory column.

Any ideas?
Thanks, Bill

 

[Harlan Grove]

I have developed a workbook to do job estimating and job
costing. Next step is to add inventory tracking.
I use a form I've created to do job costs which uses
Drop-down boxes in column "A" to get part numbers from
the database, and enter quantity used in
column "B".
In my parts database, column "A" has part numbers, and
cloumn "H" will contain inventory quantities.
I'd like to update the inventory by subtracting the
quantities used on the job cost form from existing
quantities in the inventory column.

What you want is a database. You will learn what grief really is if you
insist upon tracking inventory in any spreadsheet.

If you insist on doing this in Excel, at least use database techniques.
Inventory is best handled by adding transaction records to a parts table.
Each purchase from suppliers you add a record with a positive unit count to
the table. Each use or sale of any part and you add a record with a negative
unit count to the table. You figure current inventory by conditionally
summing all unit counts for the part in question.

If you don't keep transactions separate, when errors happen (and they will!)
you'll find out what a nightmare it can be to create an audit trail for
spreadsheet cells.

 

[Guest]

Harlan,
Thanks for the response.
We're using Peachtree for book keeping, and up until now, inventory has been
done
there, but due to lack of experience on the part of the book keeper
they've asked
me to do that part.
I'll look into Peachtree and find out just how it needs to be done.
Keeping track of inventory there would be cleaner any way.
Again, thanks for your reply.

Bill