+ Reply to Thread
Results 1 to 2 of 2

Fifo costing based on item code

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel 2007

    Exclamation Fifo costing based on item code


    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.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    California, USA
    MS-Off Ver
    Excel 2010

    Re: Fifo costing based on item code

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

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