[AccessD] Inventory Database

Hi Arthur,

I have some experience with inventory systems (been working for an Industrial
Distribution company for the past 7 years) and here are suggestions to your
questions. 


Q: Suppose customer X wants 20 of item Y and there are only 10 in stock with 30
on order, due to arrive next week. How do you handle that?

A: The 10 on hand gets allocated to that line-order, 10 is put on BO for that
line order. When the 30 come in, scan all orders for that item and allocate to
the oldest orders. A line order contains the following fields (QtyOrder,
QtyAllocated, QtyBO, QtyCanceled, QtyShipped and QtyInvoiced).


Q: Have you dealt with situations in which X vendors supply the same item? If
so, given insufficient inventory how do you decide which vendor to order
from? What if their prices are different?

A: Complex and multiple solutions depending on what you want to do but here is
my suggestion... In your inventory DB, you set 2 tables "tblItem" and
"tblAltItem" (like tblOrder and tblLineOrder). 
At order entry, the order taker selects an item from tblItem and can select an
alt-item from tblAltItem depending on price, availability etc... At order entry,
the price is determined by the item or alt-item selected and pricing schedule
for that customer. 
If you carry this item in inventory, then the cost of the item in the tblItem is
an average (ex: You have 5 item A at 5$ and you buy from another supplier
(alt-item) 10 at 10$, in inventory you will have 15 items at 8.3333$ ([5*5 +
10*10]/15).


A: Do you make any attempt to factor in the cost of shelf-life (i.e. on-hand
inventory unsold for say 30 days)?

Q: Shelf Life is difficult to handle from a db perspective because you have to
keep track of multiple shelf lives for the same item. Ex: You can have 10
buckets of paint with 10 different expiration dates. To keep track of this, you
must have another table that will keep track of this info and when you allocate
to an order you automatically allocate the oldest shelf life (but you must have
a mechanism to override this, because you can have a customer that asks you that
the product be good up to xx date...). You must also have a very, very good
warehouse procedure to always take the oldest item first... 

As for factoring the cost in, you can use a landed cost. On an item, we have 3
types of cost: Moving Average Cost, Replacement Cost and Landed Cost. 
Moving Average Cost: Discussed Above.
Replacement Cost: The true cost that the suppliers sells us at.
Landed Cost: The cost we use to calculate the selling price. This will include
the freight to bring the goods in, shelf life, etc... The selling will be a
multiple of this cost.


Steve 


-----Message d'origine-----
De : accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] De la part de Arthur Fuller
Envoyé : 6 juillet, 2003 21:35
À : 'Access Developers discussion and problem solving'
Objet : RE: [AccessD] Inventory Database

In the spirit of promoting a good discussion, let me ask:

How do you carry your on-hand, on-order &c. counts?
Suppose customer X wants 20 of item Y and there are only 10 in stock with 30
on order, due to arrive next week. How do you handle that? Split it into two
line items each with a delivery date? Split the order into two? Neither?
Have you dealt with situations in which X vendors supply the same item? If
so, given insufficient inventory how do you decide which vendor to order
from? What if their prices are different?
Do you make any attempt to factor in the cost of shelf-life (i.e. on-hand
inventory unsold for say 30 days)?

Arthur


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin -
Beach Access Software
Sent: July 6, 2003 7:53 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Inventory Database


Bill:

I've done a lot of inventory systems, but mostly in manufacturing vs.
warehouse/distribution.  Be glad to answer any particular questions you have
on or off line.


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com