FIFO Inventory Formula Challenge

Twifoo 

Robert

I appreciated your description of the problem and your analysis of solution requirements.  I also learn from the detailed techniques that have been deployed to make a formula more concise, though that has never been an objective of mine.  My shortcoming is that I find 'concise' notations, such a regular expressions (RegEx), fiercely difficult to read.

I wish I could think up competition rules for 'the clearest top-down solution strategy' in which it is the number of overall operations that count.  In my solution, posted on 13 Feb, I used Charles Williams FastExcel functions to solve the problem as an accumulate/minimise/difference sequence.  That itself is simply a solution strategy to the generic problem of 'allocate an amount to consecutive bands' (e.g. the allocation of taxable income across tax bands).  Your challenge is the same problem and could also be solved without placing limits on the purchasing policy that ensure only the final two tranches are present within the residual stock.  Having got an ACCUMULATE function, I now need an ALLOCATE function.

Once the LAMBDA functions are released, one could imagine a challenge of:

Solve Twifoo 's year2 problem for all 5 products within a single recursion formula.

To return to the topic, I think the ingenuity required to create the problem is even more impressive than its solution; congratulations.

Twifoo 

Fully accepting that I am too late to the party here...

...(and desperately trying not to look too hard at others' solutions in an attempt to see what I come up with that's either different or congruent)...

...how does this solution (at 231 characters) do Twifoo ?

=LET(
Crit,
(TrnType="Purchase")*(ProductName=J18),
LPQ,
INDEX(SORT(FILTER(TrnQty,Crit)),1),
PC,
SUM(Crit*TrnQty)-
SUM((TrnType="Sale")*(ProductName=J18)*TrnQty),
SUM(INDEX(SORT(Crit*TrnPrice,,-1),{1,2})*
CHOOSE({1,2},LPQ,PC-LPQ)))

Twifoo 

One more... was trying to save characters, could be made clearer by using longer names:

=LET(
T,TrnType="Purchase",
P,ProductName=J18,
B,SORTBY(IF({1,0},TrnQty*-P*-1^T,TrnPrice),TrnType,1,TrnNum*P,-1),
S,SUM(INDEX(B,,1)),
MMULT(MIN(S,INDEX(B,1,1))*{1,-1}+{0,1}*S,INDEX(B,{1;2},2))
)

and a slightly shorter one based on the sample file:

=LET(
T,TrnType="Purchase",
P,ProductName=J18,
S,SUM(-1^T*-P*TrnQty),
MMULT(MIN(S,LOOKUP(2,1/T/P,TrnQty))*{1,-1}+{0,1}*S,
INDEX(TrnPrice,AGGREGATE(14,6,TrnNum/T/P,{1;2}))))

I couldn't think of any nice ways to do this in old style formulas, interested to see your formula...

Twifoo 

It was all going so well!  Because the formulae used in the calculation prevented the function being called with the array of 5 products, I set out to use recursion to build the solution array.

This involved a wrapper function FOREACH that builds the recursion stack and XSTACK that extracts results following the return from each level.

I returned the array of inventory values as required.  Then I tried to SUM the amounts directly.  It turns out that the result is not recognised as an array, despite the fact that it may be output as an array.

SergeiBaklan Riny_van_Eekelen 

Any ideas?

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.

Twifoo 

It's against the ground rules, but since I don't have LET I did use named formulas. Including the length of those it is 313 characters (264 if I had only used 1 character for each name, neither accounts for the characters LET would have required).

Although the challenge specifically called for getting amounts from the last two purchases, I modified it so the next N units for which to get the cost is variable (even though you have 181 in inventory, what is the cost of the next 5, for example). So, instead of pulling from the bottom up when N is less than the net inventory (LIFO), it should still pull from the top down (FIFO - and accounting for the units already sold).

Twifoo 

Robert, I was going to apologise for using your challenges to explore the developing product that is Excel and learning new techniques rather than addressing the terms of the challenge head on.  It appears that I may not be alone in this!

I did even consider using recursion to keep a running total of stock value transaction by transaction but came to the conclusion that it would be too heavy in terms of its computation and would represent overkill.  I also wondered whether there is opportunity for a greater level of nested Lambda functions to expose the overarching logic to a greater extent but getting my brain to shift gears is not coming easily!  I had a look at some theory but the function representing recursion 

λ(λx.x x) (λx.x x)

does not come easily to me.

Twifoo 

How do you generate these exercises? Amazing!

Here I again didn't understand the logic behind (From Last Purchase Qty), just imitate your formula. First attempt

=LET(
  type, -(EVEN(TrnType="Sale")-1),
  prod, (ProductName=J18),
  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),
MMULT(QTYs,price) )

If logic will be more clear perhaps will do another attempt in few days.

Twifoo Don't really now what "Array manipulation" is and I'm probably violating all the rules of the challenge. Sorry about that. I used your challenge to play around with LET, in stead.