Formula for Calculation of FIFO testing

[Rafa100]

Helle everyone. I have such an issue. I need apply standart advanced formula for calculation of FIFO. I have attached an excel. I need advance formula to consider that there might be a number of issues or purchases like 1st issue- 2nd issue then purchase, rather that one issue, one purchase, one issue one purchase.

QTY unit cost Amount
Inevntory Balance 20 4 80
Purchased 40 5 200
issued -50 -230 =(20*4+30*5)
Remanined Balance 10 50

[jason.b75]

Have a look at this http://www.sumwise.com/blog/fifo/

[Ochimus]

Rafa,

The attached example shows you how stock is used on a FIFO basis when you add "usage" into C9.

Ochimus

[Rafa100]

Thank you both for your replies. Jason the formula works, but if I want to test a new item with Opening balance and movement during year with the sam excel sheet the formula does not work due u know why ?

[Rafa100]

I have started calculation another transaction with this formula, pls see excel attached for problem, but it did not work, I wonder why

[CplSmudge]

Rafa - which formula specifically is not working?

I just downloaded the spreadsheet and it seems like some of the array formulas might not be right
The cell I13 is correct because you have 2 minus figures which results in an addition

[Rafa100]

Cpl Smudge- you this the 1st calculation is working. But second calculation is not working. The second calculation works only if we shift it one coloumn right. To wrap up, array formula works only for the 1st calculation, for the 2nd different calculation it need to be shifted one coloumn wright. I really need this formula to work which will facilitate my work.

[JohnTopley]

Change formula in D12

=IF($B12>=0,B12*C12,-(MAX(IF($E$12:$E12<-SUMIF($B$12:$B12,"<0"),$F$12:$F12))
-(SUMIF($B$12:$B12,"<0")+MAX(IF($E$12:$E12<-SUMIF($B$12:$B12,"<0"),$E$12:$E12)))
*INDEX($C$12:$C12,MATCH(MIN(IF($E$12:$E12>=-SUMIF($B$12:$B12,"<0"),$E$12:$E12)),$E$12:$E12,0))
+SUMIF(OFFSET(D12,-1,0,- ROWS($D$11:D12) +1,1),"<0")))

The original formula used ROW(D12) and if you work through EVALUATE FORMULA you will see the SUMIF refers to Range starting D1 as a result of using ROW() rather than the change above.

First calculation changed as well

=IF($B2>=0,B2*C2,-(MAX(IF($E$2:$E2<-SUMIF($B$2:$B2,"<0"),$F$2:$F2))
-(SUMIF($B$2:$B2,"<0")+MAX(IF($E$2:$E2<-SUMIF($B$2:$B2,"<0"),$E$2:$E2)))
*INDEX($C$2:$C2,MATCH(MIN(IF($E$2:$E2>=-SUMIF($B$2:$B2,"<0"),$E$2:$E2)),$E$2:$E2,0))
+SUMIF(OFFSET(D2,-1,0,-ROWS($D$1:D2)+1,1),"<0")))