Forum Discussion
FIFO Inventory Formula Challenge
Don't look so sad, it's not yet over! We could derive the correct results using functions available since Excel 2010. So, your Excel 2016 version has certainly available functions to solve the challenge.
The formula is limited to only the last two (2) purchases; otherwise, the accounting policy of buying products only at reorder point would be violated. Moreover, buying additional products despite not yet reaching the reorder point would be a foolish business decision.
Thus, the calculation of the reorder point is crucial in determining whether to buy additional products now or defer the purchase decision to a later date.
LOL - I missed the instructions in bold font. I think the first book is what you're looking for, the second includes some named formulas that may make it more readable.
Edit: Twifoo - I corrected the workbook. With LET, I think it would look like this:
LET(P,"Purchase,
S,"Sale",
COND,(TrnType=P)*(ProductName=J18),
INV,SUMPRODUCT((TrnType=P)-(TrnType=S),--(ProductName=J18),TrnQty),
LP,LOOKUP(2,1/((TrnType=P)*(ProductName=J18)),TrnQty),
PCT,INV/LP,
SUMPRODUCT(((MAX(0,PCT-1)*{1,0}+MIN(1,PCT)*{0,1})*LP)*((TrnNum=LARGE(COND*TrnNum,{2,1}))*TrnPrice)))