FIFO Inventory method balance tracking

[artinj]

Hi,

I've been trying to come up with either a formula or a macro (I'm a novice in VBA so it would probably take me forever to figure it out) to track the FIFO balance (First In-First Out) of items at any given point in time. I have attached a sample spreadsheet with the example and all the notes that might help figuring this out.
I want to stress that I'm not trying to calculate any cumulative balance or any FIFO pricing whatsoever; just separate tracking of the purchase balances at any point in time after varous sales using the FIFO balance.
Any help is greatly appreciated.

Thanks!!!

[shg]

Maybe like this:

       ------A------- ----B----- --C-- -D-- E ----F-----
   3   AVAILABLE INFO                           As Of   
   4        Item         Date     Buy  Sell   03/01/1993
   5   Product 1      01/01/1991  100                30 
   6   Product 1      03/01/1992   10                10 
   7   Product 1      03/01/1993   15   70           15 
   8   Product 1      09/30/1994   60                 - 
   9   Product 1      12/31/1995   24                 - 
  10   Product 1      06/30/1996   10   50            - 
  11   Product 2      02/01/1991  100                80 
  12   Product 2      09/01/1992        20            - 
  13   Product 2      04/01/1993        20            - 
  14   Product 2      06/30/1994   10   10            - 
  15   Product 2      12/31/1995  100   60            - 
  16   Product 2      11/30/1996  100    5            - 
  17   Product 3      01/01/1991   20    5           10 
  18   Product 3      03/01/1992         5            - 
  19   Product 3      03/01/1993   10                10 
  20   Product 3      09/30/1994   20                 - 
  21   Product 3      12/31/1995                      -

The formula in F5 and down is,

=IF($B5 > $F$4, 0, MIN(N($C5), MAX(0, SUMPRODUCT( --($A$5:$A5=$A5) * (B$5:$B5<=$F$4), (C$5:C5)) - SUMPRODUCT( ($A5:$A$21 = $A5) * ($B5:$B$21>=$B5) * ($B5:$B$21 <=$F$4) * $D5:$D$21) ) ) )

[DonkeyOte]

Or given use of XL2007 possibly:

G5:
=IF($B5>$F$1,0,MAX(0,$C5+SUMIFS($C$4:$C4,$A$4:$A4,$A5,$B$4:$B4,"<="&$F$1)-SUMIFS($D$5:$D$21,$A$5:$A$21,$A5,$B$5:$B$21,"<="&$F$1))-SUMIF($A$4:$A4,$A5,$G$4:$G4))
copied down

where F1 holds end date of interest

EDITS:

1 - revised above in line with shg's which highlighted flaw in my original in that it did not set balance to 0 wherever date exceeded criteria (ie was missing the initial IF)

2 - added below which simply translates shg's more elegant approach using MIN(C,balance) into a SUMIFS approach

=IF($B5>$F$1,0,MAX(0,MIN(N($C5),SUMIFS($C$4:$C5,$A$4:$A5,$A5,$B$4:$B5,"<="&$F$1)-SUMIFS($D$5:$D$21,$A$5:$A$21,$A5,$B$5:$B$21,"<="&$F$1))))

[artinj]

shg and DonkeyOte,

Thank you so much for your help and quick reply on this!!! Didn't expect it that fast! This formula is awesome. Greatly appreciated!

Thank you!

[chacha]

i have a same problem what you are facing, my problem my inventory consist of expiry date

STOCK NAME EXPIRY DATE UNIT PRICE
A 01-08-2008 100 $ 1,158/-
A 02-09-2009 120 $ 1,268/-

STOCK CONSUMED 120
CLOSING INVENTORY 100 WHAT FORMULA I USE?

Similarly
STOCK NAME EXPIRY DATE UNIT PRICE
B 01-08-2008 100 $ 1,458/-
B 08-09-2009 80 $ 1,368/-
STOCK CONSUMED 120
CLOSING INVENTORY 60 WHAT FORMULA I USE?

CAN YOU HELP ME OUT?

[shg]

Please take a few minutes to read the forum rules, and then start your own thread.

[chacha]

[kINDLY RESPONSE THE ABOVE THE MAIL

THANKS
CHACHA

[karthi1727]

Dear All,

I am Karthikeyan. I am coming with up with new query. I would like to calculate the Long or short of my positions using FIFO method. If i sold the shares less than 365 days then its short.

The problem is i am not selling lot wise. for ex if i bought 1,000 shares of Axis bank on 01.01.2005 and sold 500 shares on 30.06.2005.Again i bought 1125 on 08.08.2005 and sold 1500 share on 15.01.2006. in this case 500 taken from 01.01.2005 lot and 1,000 taken from 08.08.2005.

How to calculate the Long short classification for the above. I have attached the excel sheet.

Please help me with some formula.

Thanks in Advance

Karthikeyan.

[DonkeyOte]

@karthi1727, welcome to the board, however, please note:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.