FIFO Inventory Formula Challenge

Twifoo 

I have had the audacity to work on SergeiBaklan 's version of the solution.  My reason for doing this was that I wanted to see how easy (or difficult) collaborative development might be.  My first finding was that a utility that steps through the local names within a LET function returning their values one by one would be of immense value.  The second conclusion is that working with someone else's LET formula is, indeed, easier than working with traditional formulas (such as we were meant to create for the present challenge).

I did make a change to Sergei's formula in that I replaced the matrix multiplication MMULT by a simpler SUM.  To take the work forward I then wrapped the LET with LAMBDA to improve the parameter passing (I detest direct cell references in general and was never going to find one buried in the middle of a multi-line function appealing, despite its being appropriate to the challenge).

= LAMBDA(prNm,
  LET(
    type, -(EVEN(TrnType="Sale")-1),
    prod, (ProductName=prNm),
    table, CHOOSE({1,2,3,4},TrnNum,TrnQty*type, TrnPrice, type*prod),
    total, SUM(INDEX(FILTER(table,prod),0,2)),
    two, INDEX(SORT(FILTER(table, prod*type=1),,-1),{1;2},{2,3}),
    price, INDEX(two,,2),
    lastQty, MIN(total, INDEX(two,1,1)),
    QTYs, CHOOSE({1;2}, lastQty, total-lastQty),
  SUM(QTYs*price))
  )(@Product)

I have yet to clarify thoughts on how the solutions to such problems might best be packaged.  One option might be to output the results to a table and create a rich data type with inventory quantities and values as attributes.  Another might be to use recursion to generate values for the five product lines as a single array.

I also look forward to Twifoo 's solution in due course.  It does me no harm to be shown that amazing solutions can be created with traditional techniques.