Calculate Cost Basis of Inventory

I need a macro to calculate the cost basis of the inventory. ( Field F3 and
F4 in Sheet1)


Please refer to attached sheet.
Sheet 2 shows Purchase and Sold data
Sheet 1 summarize the purchase and sold and need the currently Inventory Cost basis( F3 and F4).


Logic for Calculation:

First In First Out

Bought 1000 ItemA on 10/2/2008 for $2000
Bought 500 ItemA on 10/10/2008 for $1050

Since I sold 400 ItemA on 10/15/2008 and 100 ItemA on 10/20/2008…
that’s a total of 500 ItemA.
These 500 ItemA would come out of 1000 Apple purchase on 10/2/008 (FIFO)

So i now have 500 ItemA left>>>>>>>> $1000 ( =500*$2)
and 2nd lot 500 ItemA left>>>>>>>> $1050

So the answer would be F3= $1000+$1050=$2050

-------------------------------------------------------------------

Similar logic for ItemBs

Bought 100 ItemB on 10/2/2008 for $50
Bought 200 ItemB on 10/3/2008 for $110

Since I sold 50 ItemB on 10/15/2008 ...
these 50 ItemB would come out of 100 ItemB purchase on 10/2/008

So i now have 50 ItemB left $ 25 ( = 50*.5)
2nd lot 200 ItemB left $ 110

So the answer would be F4=$110+$25= $135



Please help and if you have question's let me know.


Riz Momin