FIFO Inventory method balance tracking

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!!!

Maybe like this:

Please Login or Register  to view this content.

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) ) ) )

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))))

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

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

Chacha, please both read & adhere to the Forum Rules before posting any further.

Please don't post questions in someone else's thread -- start your own.

Paste either my formula or DO's first formula verbatim into your worksheet -- both work fine. I have no doubt that DO's more compact formula works equally well in 2007, but have not tested.

@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.