Fifo costing based on item code

Hi,

My name is Mohamad and i'm trying to figure out a way to calculate FIFO cost for inventory based on date of purchase and sale, item code (or category), and the movement of the items i.e. incoming purchase and outgoing sale.

Attached you can find my excel model.

I've done some research and somebody did post an interesting formula in array I2:I10 where formula is IF($G2>=0,G2*H2,-(MAX(IF($K$2:$K2<-SUMIF($G$2:$G2,"<0"),$M$2:$M2))-(SUMIF($G$2:$G2,"<0")+MAX(IF($K$2:$K2<-SUMIF($G$2:$G2,"<0"),$K$2:$K2)))*INDEX($H$2:$H2,MATCH(MIN(IF($K$2:$K2>=-SUMIF($G$2:$G2,"<0"),$K$2:$K2)),$K$2:$K2,0))+SUMIF(OFFSET(I2,-1,0,-ROW(I2)+1,1),"<0")))

The formula is a bit complex and it took me some time to decipher the details... the resulting summary formula is something like this:
cost = -(a-(b+c)*d+e)

I improved the model by adding ending stock levels and related cost, but i was unable to determind FIFO cost for items' movement based on item code.

The problem:
I need a formula or a VB UDF that can calculate FIFO cost in column J2:J10 whilst taking into consideration (1) date, (2) item code, (3) purchase quantity (in), (4) sales quantity (out).

If that is possible, please share your info.

Hi Mohamad,

Did you ever get a solution to your inquiry.

If you did, please post your complete solution. You are on the right track but I can't figure it out either.

Thanks.