# Fifo costing based on item code

1. ## 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).

2. ## Re: Fifo costing based on item code

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1