Managing Inventory using a Transactions Ledger . Using FileMaker Pro . Forum . FileMaker Forums

Managing Inventory using a Transactions Ledger

Forum post posted February 13, 2013 by PhilModJunk

1196 Views, 42 Comments

Title:Managing Inventory using a Transactions Ledger

Post:

How to best manage inventory comes up from time to time and the thread I was using as a referral source for the following approach has gone AWOL so I am posting my own description of the method here so that I can refer others to it the next time the question arises.

Managing inventory is usually more than just answering the question: “How much of each product do I have in stock?” While it’s certainly the most immediate question you need answered, tracking how your inventory levels rise and fall over time is just as important if not more so in the long run. The ideal stock inventory ties up a minimum of your working capital in product sitting around waiting to be sold, delivered, consumed etc without ever actually running totally out.

Watching your levels change can tell you things about the frequency and size of your orders or production rates that  replenish your stock levels. If you see that the amount on hand is gradually increasing over time, you can reduce the size and/or frequency of your new orders. If you see that a given products stock level frequently gets close to zero, you can increase the quantity or frequency of your re-order to reduce the chance that you might have to tell a customer that you are unable to fill their order.

One way to manage inventory so that you can watch the levels change over time is to log each change in inventory in separate records of a table that functions as a “Transactions Ledger”.  Such a table would need a set of fields such as these:

_fkProductID The ID number of the item whose change is being logged in a given record. It typically is set up as a foreign key (_fk) that links to a table of products

TransactionDate Date of change

TransactionType Field identifying why the inventory has changed: Sales, Consumed, Received, Returned, Shrinkage, Error Correction are some of the possible reasons why you need to log a change in inventory.

QtyIN Number field for logging any increase in inventory.

QtyOUT number field for logging any decrease in inventory.

cBal calculation field, QtyIN – QtyOUT not shown on any layout but used to compute inventory totals.

sBalanceRunning Summary field, Total of cBal. Set up as a running total that restarts “when grouped by _fkProductID”.

sBalance Summary Field, Total of cBalance–but with no “running” option specified to show Qty on hand for a given item from a related table context such as a related products table.

_fkInvoiceID Number, serves as a foreign key to the invoice table. (More about this field later.)

Now you can set up an Inventory Ledger layout. Create a  List View layout with all of the above fields except cBal and sBalance in a single row in the body with sBalanceRunning as the far right hand field. You can include a Description field from a related products table to provide an easy way to see the name of each item, not just its ID number. This field could be put in a sub summary layout part to serve as a sub head that separates the groups of transaction records for each item in your inventory. You can also include a “reorder level” field from products to show the minimum balance before more product should be ordered to replenish stock.

via Managing Inventory using a Transactions Ledger . Using FileMaker Pro . Forum . FileMaker Forums.