Advice on Inventory management system

[djlane]

I'm making an order / invoice / inventory system for internal use in my company. Orders and invoices are easy, I'm having difficulty deciding how the inventory part should work. In this case the items are shoe boxes.

What I'm doing now is as follows, but I don't like it and would like to do it better.

The "inventory" table stores the item numbers (and other details) and a calculated balance. The calculated balance is as follows:

Opening balance (based last physical count)

+ incoming (Sum of incoming qty from inbound file)

- outgoing (sum of delivered qty from invoice file) + / - Adjustments = calculated balance.

This will give me an accurate (ie system vs actual) picture of what is in stock, but there is no real data integrity - the balance is always calculated starting from the last physical count, which may or may not have agreed with the calculated balance at that date.

I could abandon the last physical count idea, and have the calculation run forever based on the original opening balance, but I'm sure that would give me a very sloooow database.

I'm looking for the right way to set up the system so that I have monthly opening and closing balances, data integrity and an audit trail.

Any suggestions please ?

[David Jondreau]

I'd say you're on the right track. But why are the two counts different? Is everything calculating correctly? Is there employee theft? What's going on? And why would you prefer a calculated count over the actual true physical count?

If your only reservation is that the physical count differs from the calculated balance, then the issue isn't your basic set up. That's fine.

[Ender]

I'm not sure why this is in "Chit Chat". Inventory management is an important challenge that comes up in our discussions periodically. We came up with some interesting solutions in the first half of this thread:

http://fmforums.com/forum/showtopic.php?tid/171928/

Edited April 13, 2007 by Guest

[Fenton]

I moved it to Relationships. That was a great discussion. The idea of looking up the Inventory ID (or timestamp?) into every line item is a new idea to me. I like it. It not only limits the masses of records a counting relationship would hit, but also provides a real tie between line items and Inventory (in case it would ever be needed).

The items tables would be looking up the current inventory id for that product. But that count would already have occurred. So the relationship, at that point, would be used to count "new" line items (since the count), which would be combined (+ or -) with the fixed count to show a dynamic "quantity at hand" for data entry to see.

At counting time, scripted when users are not creating line items, the above combined quantity would be set into a new Inventory record for the product, and the whole thing begins again.

Either the totals above could be captured into script Variables, then set into the new Inventory record (probably easiest). Or the new record could auto-enter the Last(self-Product_ID::InventoryID) into a PreviousID field, and Commit Records; its relationship would then match the new line items.

That's my take on it anyway. But I haven't done it.

[djlane]

I'd say you're on the right track. But why are the two counts different? Is everything calculating correctly? Is there employee theft? What's going on? And why would you prefer a calculated count over the actual true physical count?

If your only reservation is that the physical count differs from the calculated balance, then the issue isn't your basic set up. That's fine.

The item is shoe boxes, millions of them. So there is always a difference between physical count and calculated qty. Its not theft as shoe boxes are not a desireable commodity! Mostly it would be human error. Could be incorrect qty delivered, could be incorrect item number encoded on incoming deliveries etc.

It is important to know both the physical and calculaed stock. The physical count tells me what is actually in inventory and available to customers, and if there is a discrepancy between physical and actual, then I know something has gone wrong.

If I base the calculated balance calculations on the last physical count, then the system should run faster (I assume...)as I'm only looking at transactions since the last count date. BUT it seems to me that I have lost data integrity, because if the actual balance at the last count date does not equal the calculated balance, then that fact is simply ignored in the newly calculated balance. And I can't just make an adjustment because..well because the current calculated balance has been based on the last actual physivcal count.

But discrepancies need to be investigated and resolved. Since tha balance has already been calculated, I guess I could do a plus & minus adjustment. I mean if I make a negative adjustment, then I make also a positive adjustment for the same amount. That way I get to record (ie document) the adjustment wihout affecting the calculated balance. Does that make sense ?

It just does not seem to me to be a very "professional" way to do things ?

[djlane]

At counting time, scripted when users are not creating line items, the above combined quantity would be set into a new Inventory record for the product, and the whole thing begins again.

Yes, I have identified counting time as an issue. I can't have stock movements on the same date as the physical count, otherwise the calculated balances wont work. Anybody got an idea on how to allow stock movements (eg deliveries) on the same date as physical counts?

Either the totals above could be captured into script Variables, then set into the new Inventory record (probably easiest). Or the new record could auto-enter the Last(self-Product_ID::InventoryID) into a PreviousID field, and Commit Records; its relationship would then match the new line items.

Could you please explain this a little more Fenton. Thanks

[David Jondreau]

Yes, I have identified counting time as an issue. I can't have stock movements on the same date as the physical count, otherwise the calculated balances wont work. Anybody got an idea on how to allow stock movements (eg deliveries) on the same date as physical counts?

Depends on your particular workflow.

I've personally handled it two different ways. One is to count after hours/weekends when no transactions will take place. The other is to tweak the date of the delivery to the day after the count (and not count the delivery).

You could also have a time-based system, where you track the hour of the delivery and hour of the count.

[Fenton]

The basic system proposed, as I understood it, has 2 parts to the "dynamic" amount available, on hand, visible to salespeople:

1. The last count, a number

2. The currently ordered items, not yet included in a fixed count, a related Sum() of line items not yet counted.

The 2 above are differentiated by looking up the last Inventory ID for a product into the line items. These will be items not yet included in a fixed count. These need to be added to the next count.

So you would gather them into a Variable, as well as the current count, create a new Inventory record for the product, and set the result into the fixed count field. Line items created after this moment would lookup this new ID.

What I was saying (unnecessarily perhaps) was that you could alternatively create the new Inventory record for a product, auto-enter it's Last ( self_ProductID::InvenID), Commit Record, then use that to Sum() the Line items. It's just an alternative way to do it, a slightly different process.

This is a bit abstract, as I've not actually built the above; I've always either just used a simple relational dynamic method, or reset the Inventory upon "submitting" an invoice or receiving transaction. But I like the above method, theoretically.

[djlane]

2. The currently ordered items, not yet included in a fixed count, a related Sum() of line items not yet counted.

The 2 above are differentiated by looking up the last Inventory ID for a product into the line items. These will be items not yet included in a fixed count. These need to be added to the next count.

I'm not sure what you mean by "The currently ordered items" or by "items not yet included in a fixed count, so I think I need to explain better what I was planning.

The physical count gives a snapshot of actual physical inventory on a certain day. I now need to look at stock movements since that date.

In my item file I have a calculation for the current balance which looks into (SUM()) the deliveries line item file, the adjustments file, and the incoming line item file to see if there are transactions since the count date.

By using a similar calculation I can also predict the inventory at any future date. My incoming file has ETA date for every item in an inbound container, and my planned deliveries file shows pending deliveries. So I can calculate predicted balance on a future data as the current balance less pending deliveries until that date, plus incoming items with an ETA until that date.

I'm just surprised that you guys think this is a good way to do this. I thought you would be suggesting an "end of month" routine.

eg. (I'm just thinking of this now..) At the end of the month do a physical stock check. Resolve any discrepancies and make adjustments as necessary. Then run a "month end job" which locks all the supporting transactions and creates new opening balances for every item.

Use a "cycle number" which increases by 1 each time you run the month end, and place the cycle number on each of the transactions in that cycle.

Then use the same calculated balance formula, but instead of starting with the last physical count, we start with the opening balance from the last month end cycle.

Is that a better way to do it ?