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

Hi

Using your example file as the basis for structure etc.

1) Create a new Class module, called Class1 (this is the default) and enter the code

Please Login or Register  to view this content.

2) Enter the code below into a general module

Please Login or Register  to view this content.

There is some output on sheet2 columns I-K that is used in the new sumproduct formulas.

HTH

rylo

Thank you so much for the help...

Imagine I have Items with Qty with 3 decimal point like in Stocks..
Bought 500.974 Stock...

Please show me which part of the code needs to edited to accomodate these...



Once again thank you for yoru help...

Riz Momin

Riz

in the class module change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

rylo

Thank you so much for your help...works great..
Will inform you if needed any further help on the same macro

This will save me tons of time...

Riz Momin

I thought i would be able to implement the macro in my original sheet which is hereby attached (Sample2)

I am having some difficulty.

In Sample2 worksheet, Brokerage sheet has all transaction.

i have created CostAve sheet in the same format as Sheet2 of Sample1 worksheet.

The calculated cost basis in need to go is in ShareCalc sheet of Sample2 column I..

Please see if you help me accomplish...


Help greatly appreciated...

Thank you,

Riz Momin

Riz

some things to try

1) Brokerage!F865 - Should be 1000
2) Brokerage!G865 - should be Microsoft
3) Turn on the sorting - this is a necessary step to getting the FIFO action working
4) Change the autofill line (second last row in the code) to be

Please Login or Register  to view this content.

See how that goes.

rylo

I think this is working..

Once again thanks a lot for the help and will inform you for any furher needs..

Thank U,,

Riz Momin