inventory problem / economic data

[gchron]

Hello, I am trying to find an inventory system that can show also the profit of something sold.

Here is my idea for the problem. I think that it does not work as I like and that is the reason I as you here.

When something is bought it is written to the inventory table with its price and quantity.

When something is sold it is written also to the same table with the sold price and quantity.

At every sold Item I need to have the purchased price of the product and so I search the original purchased item I marked it as sold take the price and write it to the sold item. So I have the profit of the sold item.

This was easy.

The problem now

What if I parchase a product named "test" from a supplier with quantity 5 and price 10. Two days later I purchased some more "test" with quantity 10 and price 12.

Then I have a customer that needs 3 "test" with the price of 20 and at the same day an other one who needs 6 "test" with the price of 20

I wrote a script that for the first customer takes the 3 "test" and finds the first not sold "test" it is the first 5 we bought. The script duplicates the record and makes the first one to have 3 and the second to have 2 items of the "test" product. so the customer has its 3 and so the cost of the sale is 20 - 10 = 10

For the second customer who bought the 6 I find the older free product (It is the duplicated record we created before that has 2 items) I mark it as sold take the price (10) and go to the client purchased record and duplicate its record, so to have 2 items in the fist one and 4 items to the second. So I write the 10 price to the first to sold and gives me profit 20 - 10 = 10 and also to the second record of the 4 is doing the same job but it finds a record that has more items available so the second purchased items is also duplicated so the 10 items are splitted to 4 and 6. The record that has the 4 items is marked as sold and its price is added to the remaining 4 items of the second purchase. So the profit of this is 20 - 12 = 8.

So I have the hole profit of the first purchase with is 10 * 3 = 30 profit for the 3 "test" products and the second customer has 10 * 2 + 8 * 4 = 52 for the 6 "test" products

The script I wrote is working but I think that the hole solution is a little bit wrong. Is there any idea I could use in order to clean the hole process??

Thanks in advance.

[André]

-post deleted for not making sense- :

Edited July 13, 2009 by Guest

[Fitch]

Your method seems workable. It's hard to say what would make this a "cleaner" process: that's largely dependent on how clean your code is. But there's nothing inherently wrong in keeping the In/Out records in the same table, it may arguably be the best approach.

What you've described is FIFO (First In, First Out) inventory management. Search these forums and the Google for that and it may give you some inspiration.

@André: I agree, the solution would involve math. :

[bcooney]

This really is a question for the firm's CPA. How do they want to value their inventory: FIFO, LIFO, or weighted average?

Do they barcode the items? That'll help with a true cogs.