Posting and inventory costing problem

[Greg Fong]

I am trying to get a better handle on the value of inventory on hand. The problem is that I have multiple suppliers for the same product each charging a different price and of course the price of of these products have changed over time (generally up!). How does one go about using the subtract operation in this circumstance? For example: If I bought 100 units from ABC Company at $1.00 in January and 100 units at $1.50 in February my inventory value would be $250.00. If I sell 150 units in February how much is my inventory worth? It could be $100.00 ($250.00-(150units*$1.00)or $100.00 ($25.00-(150units*1.50) or more correctly if I base my valuation on a First in first out basis $75.00 ($250.00-(100units* $100)+(50units*$1.50)).
All the examples including the invoice sample DB assume a fixed inventory item cost.
Has anyone gotten past this problem, is there another way to structure things? To make things worse what do I do about multiple suppliers?

[Robert Adler]

RE: Posting and inventory costing problem

Greg:


There is no simple solution if you are looking for 100% accuracy. You have to subtract sales based on the fifo counts. Using your example:

Units Cost
100 $1.50
150 $1.50
The first 100 units should be subtracted at $1.00 and the next 50 at $1.50. You must add a field to the inventory field that permits you to compare sales count to your inventory count. When the two field match then you go to the next record and add the sales in to the control field. This process is repeated until the items sold are accounted for. You can put a flag in the inventory record that will permit you to by pass records purchases and sales are balance.

I hope this helps. It is not a precise process but might help point to a solution.

Bob adler

[csda1]

RE: Posting and inventory costing problem

Greg,

The easiest way to do this is to store with your inventory it's current worth. e.g.

Purchased 100 units @1.00 on 2/1/2001 - Inventory qty =100, Total Cost=$100
Purchased 50 units @1.50 on 3/1/2001 - Inventory qty =150, Total Cost=$175

By having a running inventory cost, each time you sell a unit, you decrease the inventory's value by the average cost (currently $175/150 units) for each unit sold. To handle returns, you must store this average cost with the purchase. If it is returned, you add the item back to inventory and the average cost back to the inventory value. Over the long haul, you may have some minor roundoff errors, but it won't amount to much.

This method has the advantage that you don't need to know when you purchased items and at what cost in order to process the transaction.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Greg Fong]

RE: Posting and inventory costing problem

Thanks for the help!