Thoghts on inventory Management Module?

Forum: Microsoft Access Posted By: jdostie Post Time: 06-01-07 at 14:22.

OK, so this is a little complicated, so please bear with me. My company uses a cash accounting method in Quickbooks, but I have a division where we need to keep track of inventory for multiple sites, primarily for keeping track of quantity on hand and being able to research historical transactions. I am thinking that as long as I am building this, I might as well build in a valuation method. So:

I am thinking of having the following: (I’ll attempt to include images of what I’ve already got [with some artifacts of some stuff I was toying with in the past])
tblItemMaster (contains information about the SKU)
tblWarehouseMaster (contains information about Quanitities for the site, and authorized stock levels)
tblInventoryTransactions (contains historical transaction of SKU Items) - SKU level
tblInventoryDocuments (will be modified to allow for multiple trans types) – Document level
tblInventoryFIFOLIFO – contains historical information about items placed in inventory and sold- the intention is to “check off and record when sold” items from inventory. If FIFO, always use the first item first, if LIFO then always use the last item first, if “average cost” then always subtract the first item in inventory. Also if average costing, update the cost of all related SKU’s to update the average when a new item is purchased. This approach allows the accounting method to be changed in the future – although an update might be required from going from one accounting method to another.

In general, I am thinking that I need to:
1. Order inventory – so update the “on order” for whichever warehouse
2. Receive inventory – so update on order, add to inventory for specific warehouse, insert into the FIFOLIFO table, and somehow “check off” the Order document as received.
3. Transfer Inventory – decrease inventory from one warehouse and add it to another
4. Sell Inventory – Reduce stock on hand from warehouse and check off/use item from FIFOLIFO table
5. Adjustments – variations of order or sell to correct inventory discrepancies or return defective product to vendor (and possibly receive defective produce back from customer)
6. Manage “core returns” basically by creating an order to a returns warehouse, and creating a returns document for clearing inventory from the returns warehouse.

I had started this, then adjusted it for a much more ambitions project (some of the artifacts of the more ambitious project still show in the existing tables, and now want to abandon the more ambitious in favor of “simple” inventory management. But before I do, I wanted to see if anyone here had thoughts on the approach.

Please recognize that I know that a true accounting system would be better, but it’s not in the cards with my company at the moment. AND, I need to manage inventory quantities somehow, AND I need to request and list items used on work orders from my existing database anyway, so given all of that, it makes sense to try to do something along these lines.