Forum Discussion
FIFO Inventory Formula Challenge
Just checked and your formulas match expected results for first few but last two don't match for me.
lori_m you are right
(I am sure there is a lesson there about testing regimes and actually testing thoroughly!)
This version is now 206 characters:
=LET(
A,(ProductName=J18),
B,(TrnType="Purchase")*A,
C,TrnQty*B,
D,(TrnType="Sale")*A*TrnQty,
E,SORT(((TrnPrice:TrnQty)*B),2,-1),
F,MIN(INDEX(E,1,1),SUM(C-D)),
INDEX(E,1,2)*F+INDEX(E,2,2)*(SUM(C)-SUM(D)-F))
Twifoo Nice technique and explanation!
Bearing in mind ABS(x) = x^2^0.5, our methods are not too dissimilar. For 'Courage' the second formula I posted evaluates to:
=SUM({76;65}*{141,-40;40,40})
where for the sake of brevity an extra MMULT was omitted that would have resulted in:
=SUM({76;65}*{101;80})
Aside: It also occurred to me to use MDETERM({76,-80;65,101}) which returns the same result of 12876. Maybe not so useful in this case but could save looking up prices and quantities individually for calculating total cost =65*760+76*101.
- Here is the link to my detailed explanation of my two solutions:
https://roberthontoriagascon.medium.com/these-fifo-inventory-formulas-will-challenge-your-array-manipulation-skill-129a8f680ad?source=friends_link&sk=915b52e8696e1cf06fad1baff7563a21 Maybe this is a better version of the SORT
That helps iron out the potential bugs you identified?
=SORTBY(TrnQty:TrnPrice*(TrnType="Purchase")*(ProductName=J18),
TrnNum*(TrnType="Purchase")*(ProductName=J18),
-1)New formula becomes:
=LET(A,ProductName=J18,B,TrnType<"Q",C,SORTBY(TrnPrice:TrnQty*B*A,TrnNum*B*A,-1),D,
INDEX(C,{1,2},2),MIN(SUM(D*{1,-1})*INDEX(C,1,1)*{0,1}-D*SUM(TrnQty*-1^B*A)))Combining your SORT formula with previous techniques (148 chars):
=LET(A,ProductName=J18,B,TrnType<"Q",C,SORT(TrnPrice:TrnQty*B*A,2,-1),D, INDEX(C,{1,2},2),MIN(SUM(D*{1,-1})*INDEX(C,1,1)*{0,1}-D*SUM(TrnQty*-1^B*A)))Edit: looking again although these last two formulas return expected results additional assumptions are being made relating to sort order that may not hold in general. Awaiting Twifoo's solution...
