Need inventory control help

[watrout]

WARNING: LONG POST
I know there have been plenty of discussion over inventory control but I have a situation with some additional variables and I need some heads to bounce ideas off of.

I work for a custom cabinet manufacturer and I have a database that I developed that we’ve been in use since 2001. There are several different components to the database, but the last one to be completed (and the one I’m working on now) is job costing.

PROBLEMS/CONSIDERATIONS
Problem #1: Since we are a manufacturing company we use many different materials to make any one product. So far I have all items ordered (since 2001 anyway) in the database but to date I don’t have these items allocated to any particular project – hence the need for what I’m talking about here. One of the unique problems I’m having is the fact that some items are ordered for a particular job (I.E. you know what job they are for at the time of ordering) and some items are “stock” items, meaning that we always keep a certain number of units on hand.

Problem #2: Another unique problem (relating mainly to “stock” items) is some products we use are ordered from different vendors (depending on availability, price, ect.) These products are essentially identical and need to be grouped with other like products at the time of assigning materials to a particular job. On the surface this problem sounds like an easy fix just by grouping the same product name across all vendors. However, it’s not quite that easy because we have many different stock items and product ids are different from vendor to vendor (and I always tell users to use a vendors identification for the product ID and they can use the product description to clearly identify it for our users). Another problem is I have users that are not disciplined enough to keep track of how they named a particular product previous ordered from a different vendor. Therefore I’m convinced I need another method of identifying like products across vendors.

Problem #3: Lastly, I’m still trying to figure out if I should subtract current stock or calculate it anytime “on hand” numbers are needed. Any thoughts on the best method (keeping in mind the variables listed above) would be greatly appreciated.

MY SOLUTION IDEAS
Concerning Problem #1: I want to allocated materials to a job at the time a work ticket is written (this would be the most logical input location since any given work ticket deals with a fairly small portion of an overall job and a material summary at that point is typically already given by another program or would be easy to figure by the person writing the ticket). I would like to do a material input form that would list only materials that have been ordered for the job and then either call another form for stock materials or have a switch to show all stock materials. What I’m unsure of: will I run into problems selecting both from the same form (since they’ll most likely run from different queries) and how will I pull stock items from multiple vendors (i.e. I used 10 sheets of particle board: We have 5 sheets left from vendor #1 and 7 sheets left from vendor #2). – Any thoughts on better methods and/or ways to improve database structure?

Concerning Problem #2: My main thought on combining like products from different vendors without requiring that the product ID match exactly is to enable a “Universal Product Code” anytime a product is tagged as a “stock” item. In the product entry form this would be a combo-box so the user has to select from a list. – With the amount of data I have so far I think I could create a fairly comprehensive list and furthermore my hope would be that if a user had to go to the extra step of adding an item to the list – he/she might think a little harder about properly coding it. – Thoughts/suggestions?

Concerning Problem #3: Given the considerations previously listed, I would ultimately like the database to accurately track inventory so when I order a laminate (or anything else) for a job and we had some left over from a previous job I could see that at the time of ordering. This would also save us time while entering work orders because we could see at the time of release that we are short of materials instead of waiting for the ticket to get into production. Whether I use a subtraction method or calculation method, I still need to figure out how to allocated or indicate which work order the item was designated to. Any thoughts?

Attached is a PDF showing the relationships to my existing tables. Keep in mind that additional tables may be needed to implement the changes I’m talking about here. Also, I’d be glad to email my database to you if you’d like to take a look at it.

I apologize in advance for the naming conventions used in my database. This was my first database and some of my earlier work was done without the foresight of proper naming.

 

[The_Doc_Man]

Due to site security rules, I cannot look at your attachment.

My thoughts:

Your problem #1:

some items are ordered for a particular job (I.E. you know what job they are for at the time of ordering) and some items are “stock” items, meaning that we always keep a certain number of units on hand.

ALL items, custom or not, are stock items. Some happen to have inventory on hand; others require ordering when a demand is made for them. The usual way this works in a big-scale inventory system is a field called "Target Inventory Level" - i.e. how much you keep in stock whether you need it or not. If the level is 0, it is a demand-order item. If the level is >0, it is a regular stock item.

The way you handle this is the same way you handle any other order, EXCEPT you need a "fake" work order number for the items that are regular stock items. Now, a fine detail of this is whether you charge the contract requesting a stock item for the material you order to USE... or to REPLACE what you USED. (Hope you see the difference.)

Therefore I’m convinced I need another method of identifying like products across vendors.

Yes, you do. In general, you need a concordance table in which your INTERNAL stock number is a field and the VENDOR CODE and VENDOR STOCK NUMBER are other fields. Then you would have one entry in this table for each VENDOR NUMBER & VENDOR STOCK ITEM and the INTERNAL STOCK number would not be unique. But it could tie back to whatever internal table fully (and accurately) describes YOUR stock items.

Any thoughts on the best method (keeping in mind the variables listed above) would be greatly appreciated.

Never, ever, EVER make quantity on hand a table item.

Consider the following (and look up UNION queries if you don't know about them already):

You might have: a table of your company's purchase orders that are actions to increase inventory and a table of your company's material assignments (work order materials lists?) that are actions to decrease inventory and (possibly) a table of actions in which you have to write off material that is damaged or otherwise becomes unusable while still in inventory (shrinkage table) and even perhaps a restocking table showing that a project returned some of its materials for full or partial credit. As a trick (not to be used lightly), some folks will add a table of inventory on hand when the new inventory system was started. (Everything has gotta start somewhere!)

OK, each of these will have in common your internal stock item number, a quantity, a date, and possibly a net value of each transaction. Imagine if you will a UNION query in which you form the union of four SELECT queries that pull stock number, amount, date, and transaction type. PLUS one other field - the net effect of the transaction on the inventory of the item with that stock number. Which will be equal to the amount for transactions that add to inventory and minus the amount for transactions that take from inventory.

If you do that, your inventory today is a SUM query of the amounts, grouped by the inventory numbers. Your inventory on any previous day is simply the same SUM query where all dates are before or on a given date.

Now, the next step is trickier.... You could ALSO include "demand" entries for each item, treating the amount requested separately from the amount actually used. What value is this? In the SUM query for demand, if your inventory (counting the demand) is NEGATIVE, you are back-ordered on the item. You can do the "demand" query either as a separate field tracked in the queries mentioned before, or by building a variant query that includes all of the above SELECT queries plus separate demand entries in a table that doesn't show up in the "inventory on hand" query. (I vote for the latter, but what the hey, it is your DB App)

 

[neileg]

I agree with most of The Doc Mans comments, but here's my view.

Most costing systems do deal with the concepts of stock items and special orders and I don't agree that you need to treat everything as a stock line (though you could). If you do use the twin track approach, you should consider the following:

With special orders, it is essential to identify the job number at the time of ordering. Thereafter, the detail of the order becomes relatively unimportant, as the whole cost, and whetever description you use is uniquely attached to the job

When you order for stock, you must identify with an internal stock code what you are ordering. The fact that different items come from different manufacturers is irrelevant. If the items are functionally identical, then they are the same for stock and costing purposes. For example, you may be able to tell by eye or by manufacturers coding, that two sheets of particle board come from different sources. But if they are completely interchangable in use, then they are the same stock item. If there is a functional difference, and you would prefer one type to the other in some circumstances, then they are different stock items.

When you need to value stock issues, you need to decide how to calculate the value. Since the stock on hand will not be identifiable by individual unit, you need to adopt a convention.
Accountants (like me) prefer FIFO (first in, first out). This values stock by assuming the oldest stock is used first and so stock in hand is valued according to the prices that the most recently purchased items came in at. Issues to jobs are valued at the price of the oldest stock. This is difficult to program!
Average price is easier to program and is widely used.
The easiest convention to program is last invoice price. This can introduce wild swings in valuations but is never the less very common.

The practical problem that the twin track approach introduces is the matter of left over special purchases.
Yopu may need to buy in a particular product for a job, and the minimum quantity you can buy leaves you with a surplus. I would argue that you need to apply the cost of the whole order to the job. However, unless you know you will never use the surplus, you may well hang onto this. You need to decide if you are going to bring this into stock, and if so, at what price.

You definitely need to calculate stock balances and not store them which is why a uniform coding structure is essential.

You need to consdier the concept of preallocated stock. Stock would be allocated at the time of the the works order, but not issued until the work ticket hits the shop floor. Thus you may have stock on hand of 10 units, 3 units preallocated, and 7 units free.

Good luck