Theory... Inventory Serialization

[Cobbler]

In my Inventory Database, I have purchasing functions and issuing function and inventory tracking functions. Because I have a PO Details table which includes lines for each inventory item and its current price on any given PO, I really have a defacto serial number for each part/PO combination. For job costing purposes, this would be very useful to develop. I can think of several directions to take in order to tie Part, Price, PO Line and Job together to establish a true cost but none of my thoughts seem to efficient to me.



On a theoretical basis, how would you do this???

[rpeare]

establish a 'true cost' for what? and what do you mean by 'true cost'? Can you give an example of what you want to attempt and perhaps an example of how your tables are arranged?

[Cobbler]

By "true cost", I am referring to what the end product cost to produce as opposed to what it would cost to produce today. If the 50th widget off the production line contains 5# of steel which cost $1.25/Lb the true material cost for that part would be $6.25 even though the last PO for steel was $1.50/Lb. In this scenario, I may still have some steel in inventory from my older $1.25/Lb PO and some from my higher priced $1.50/Lb PO.

For quoting purposes to the next customer, I would use the cost of $1.50/Lb... for cost accounting, I would use the "true cost" based on the actual price of the raw materials based on the cost of the actual raw materials used and their associated costs.

Also, for Inventory Valuation, it would be ideal to set my inventory value equal to the ammount of inventory times the actual cost of each pound of raw material.

At this point, I really don't have the tables set up as I am still trying to work through all of the requirements and set them up to meet those requirements. I see this as a golden opportunity to set the tables up correctly to handle long-term requirements as opposed to creating "work arounds" to deal with them later. Hence, the Theory level of this discussion...

[rpeare]

This is an interesting problem

Say you buy 100 pounds of steel at 1.50$ per pound

You use 50 pounds of that on a specific job so your true cost would be 75$ for that job.

Now let's say you receive another 100 pounds at a cost of 1.25$ per pound.

The average cost per pound of your inventory is 1.33$ (repeating) per pound

When you get the next job in do you charge the 1.33$ because it's the cost of what you have in inventory or do you charge the 1.50$ rate until you've 'used up' the first shipment of steel? Sort of like a FIFO system

Let's say the second job called for 75 pounds of steel would you charge 50 pounds at 1.50 and 25 at 1.25 or would you charge the entire amount at 1.50 because it was the most you paid, but on a subsequent charge you'd use the 1.25 amount because you've used up your inventory of the 1.50 steel?

[Cobbler]

From a business model standpoint, we would use latest cost when bidding a job (unless we were aware of a pending price increase or have an excess quantity of inventory with a higher cost) but for calculating sales comissions, we use actual cost.

I guess the theoretical side of this is taking a complicated task and complicating it further with programing. One question to ask before you even start would be where to store this info or to calculate it every time. Either approach seems less than ideal on some level. I'm having trouble deciding which is the lesser of two evils.

[rpeare]

well there are a couple of things you can do.

Let's assume that when you receive material into your warehouse that you record the cost per unit of the item. When you receive that item you can update your material costs with the most recent price per unit.

OR

if your costs don't change frequently you can just update your materials table whenever a change occurs.

Either one is not difficult it's just a little more maintenance which can be a little painful if you have hundreds of materials. Less so if it's just a couple of dozen items. Either way you could provide for a 'manual override' or at the very least display a warning if the new cost is lower than the old cost and ask if you want to apply the new cost.

Theoretically you could set up a FIFO system where when an order is placed it uses the oldest material first then uses the newer material, regardless of what product is taken of the shelf, the database would assume you're taking the older product first, then when you're figuring your actual cost you would calculate it based on how much product was taken from each receipt of material and figure the cost based on which product was received at which cost.