stock management database

Question

I am working on a stock management application Here is the scenario:

-the user enters the items purchased with the following information (Id of the item, quantity purchased, purchase price, minimum price for wind, date of purchase)

each operation, the user enter the items sold with the following information (Id of the item, quantity sold, the final price of wind, date of the transaction) and each operation I have alerted the user if the the final price wind is less than the minimum price of wind

each operation, a wind I have recorded the benefit of the transaction (the final price wind - purchase price)

The problem is: you can buy the same product in a different time with a different price then we can calculate the common benefit,

for example if I have the following case

items purchased (t-shirt), 10, £ 20, £ 23, 10/08/2012 items purchased (t-shirt), 10, £ 22, £ 25, 19/08/2012

In this case, the operation of wind what I have to do to recover the Article with the correct purchase price and calculate profit and recorded the good information in the sales table?

Answer 1

In finance, computing the profit/loss is always (well mostly) done with the FIFO rule.

That is: BUY 10 for $20, SELL 5 @ $22, BUY 5 for $21, SELL 10 @23, has to be recorded as

BUY 10 for $20 date1
BUY 5 for $21 date3

and separately

SELL 5 @ $22 date2
SELL 10 @ $23 date4

Now what you have to do is pair these according to the FIFO rule and write the intermediate portfolios into the database:

portfolios AFTER date
date1  10  profit/loss $0     avg-open $20
date2  5   profit/loss +$10   avg-open $20
date3  10  protit/loss +$10   avg-open $20.50
date4  0   profit/loss +$35   avg-open $0

The operations one by one: After date1 we bought at an average open price of $20. From date1 to date2, we sell half the stock, making a profit of 5 * ($22 - $20 <- avg-open) = $10 and after that transaction there's still 5 securities left with an average open price of $20.

From date2 to date3 we stock up by 5 securites, profit/loss remains the same, but the average open price goes up to $20.50 (5 for $20 + 5 for $21).

Then finally, you sell them all for a profit margin of 10 * ($23 - $20.50) = $25 plus the $10 from before, that gives you $35.

Summing, this up, you can either record single transactions (BUY/SELL) and then compute the portfolio, the average open price and the profit/loss on the fly.

Or you can store the portfolio along with the profit/loss so far and an incrementally updated average open-price.

I wouldn't recommend to do both (except for debugging purposes) because that could give you inconsistencies at the data level, which is nasty. If you do, elect one of the representations as master, and (re)compute the other in case of inconsistencies.

Just a suggestion.