Using Excel to capture Inventory

[Anne K]

Has anyone used excel as an inventory tracking tool? I've set one up and
every time something comes out of inventory in a given month, just add an
additional plus to the formula on that line item. I've set up a reorder
number and an if statement when a number drops below a certain number, it
alerts me to reorder. However, a few items need removal from multiple places
and people forget to note it in all, thus throwing off my numbers incredibly.
ie.: 20 manuals ship means deduct 20 binders, 20 sets of tabs, 20 tapes.
Someone can easily forget to deduct the tabs and I'm in a crunch. Any
suggestions?

 

[JLatham]

In my experience it's almost impossible to create anything close to a robust
inventory management tool in Excel without a lot of VBA code (macros) that
either run automatically based on certain events such as shipping 20 manuals,
or on demand from the user. There just is no accounting for human error
without very strict control and monitoring of the activities within the
workbook.

I almost hated to try to answer this one because invariably it ends up
leading to a month-long project to get a working product. It's also
difficult to give more than general ideas without seeing the specific
workbook.

One relatively simple thing you might consider is just putting comments in
the cells where you record shipments or other activities for items that
require more actions. Such as putting a comment like "Do not forget to
account for binders, tabs and tapes upon shipping this item" in the cell
where you enter the number of manuals shipped. When the click in the cell or
even just hover over it, at least they'll get the reminder and no VBA code
required. This takes a little more setup initially, but the payback in a
more accurate inventory may be worth it.