Dear Feeman:
You say you have a transaction type which is delivery or order. Let's start
there.
Making an order does not directly affect the level of stock. However, if
you see the stock is low you may want to generate an order. Knowing whether
you have some already ordered, and how long it has been pending, may be
important. So is knowing the rate of inventory turns (how much you
typically sell of this item, perhaps seasonally adjusted). Keeping track of
the orders for each item is best done by relating each item to every order
placed, assuming you track the orders on your system.
You may have ordered some of this item from a supplier who has recently gone
out of business or who is slow to deliver. Keeping track of all orders
individually allows this. You must then track when these orders are
received as well, removing them from being "on order" and making them "on
hand". You need a mechanism to allow you to remove all the orders from a
company that has gone out of business, while leaving all the other orders
for those same items intact. By aging the remaining orders, you can develop
a way to predict when the orders may arrive. You may even want to track not
only the average time for each vendor to deliver on any order, but even
track how long it takes to get a specific order. This is helpful if certain
items are frequently back-ordered from a given vendor.
If you can predict the likely date of arrival of an order and also the rate
at which you are likely to be selling that item at this time of year, then
you can predict just when you are likely to run out of that item, perhaps
with a built in margin of error. If you put into the system a time value of
your money (considering the terms of each vendor) you can calculate the
relative cost of these items. If you want to buy a certain item from a
lower cost vendor who is slower to deliver, then you must order that item
earlier in order to save money. If you wait too late to order, you may
either run out of the item, or need to order it from a higher cost vendor.
The whole project of managing purchasing to maximum benefit is a very
considerable piece of work. I've just begun to delve into the intricacies.
You can also consider the cost or limits of storing stock against the
benefits of buying in bulk, keeping in mind also the time value of your
money and possible shrinkage due to longer term storage.
Just how fancy you should get is a balance between the cost of the software
and the volume of business to be managed. In a good sized business, these
things are rarely well managed. A few have got it right, and they have
excellent margins as a result. Such companies are prosperous when things go
well if they don't screw up in some other way (such a choosing the best
kinds of inventory for their area, season, and demographics).
Your post does not even mention tracking sales. Without knowing what is
being deducted from inventory, what purpose would there be in this.
Besides sales, purchases, and orders, your system should probably track
known shrinkage (products know to be damaged in handling, defective products
returned) as well a the ability to count the initial inventory to bring the
system on-line and the ability to take inventory and enter the known
quantities.
It may also be valuable to track where the inventory is. Even if you have
only one stock room, there is still some inventory there and some on the
sales floor.
Do you have any related stores with which you exchange inventory, or for
which you also keep stock?
There are many complexities that should be modeled if your efforts to create
a database are to be successful.
Tom Ellison
Microsoft Access MVP
