FIFO Inventory Formula Challenge

[Twifoo]

To revel my second anniversary as a member of this EXCELlent Community, I hereby proffer this challenge to season your array manipulation skills. Perhaps, @keenadvice @mathetes @Riny_van_Eekelen @Peter Bartholomew @Sergei Baklan and @lori_m could again spice our learning experience!

[#2]

@Twifoo 

Oh, my! It's an honor to be included among such august company, an honor I fear I don't deserve at all. I'm here to learn, and occasionally offer some more basic suggestions.

But, challenged, I'll give it a try later today.

[#3]

@mathetes An honor, yes; but undeserving, no! With your acceptance, I anticipate your valuable insights that will certainly augment our common quest for knowledge.

[#4]

@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.

[#5]

Well done @Riny_van_Eekelen !

I''m still working on it (in my head mostly)...later on today I will be home where I have access to two more reasonable screens, rather than just a small MacBook Air.... that should help.

It would be nice if one of us--it won't be me--were to come up with a LAMBDA based solution.

[#6]

@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.

[#7]

@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.

[#8]

@Twifoo 

Well, guys, if this were a chess match (which, metaphorically, it is) I would tip my king on its side, and reach out to shake your hands.

I've truly had fun learning how to nest FILTER within INDEX and COUNT (among others), have formulas that will get each of the salient numbers here, and could write a LET to define all the re-used array formulations, but my head is starting to feel as it it might explode just from contemplating putting them all into a single formula, as Robert has stipulated, to say nothing of actually doing it.

So it's been a learning experience for sure, and I look forward to applying my learnings. I'll keep looking over your shoulders in months ahead. I AM going to spend some time studying each of your solutions.

[#9]

@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).

[#10]

@Riny_van_Eekelen 

I would define array manipulation as the skillful control of formula results. Nonetheless, the challenge is to derive the correct results, regardless of the functions used thereby. 

[#11]

@Twifoo 

OK, if permitted, I'm going to withdraw my resignation from the match. I stuck it out after all (one thing that helped a lot: I learned how to do line breaks in the Formula Bar, which makes it a lot more readable, as all of you know). Here's the formula:

=LET(

PQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Purchase")),
SQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Sale")),
NQt,SUM(PQt)-SUM(SQt),
LPuR,COUNT(PQt),
ARQtPr,FILTER(TrnQty:TrnPrice,(ProductName=J18)*(TrnType="Purchase")),
LPuQ,MIN(NQt,INDEX(ARQtPr,LPuR,1)),
LPuP,MIN(NQt,INDEX(ARQtPr,LPuR,2)),
SLPuQ,MIN(NQt-LPuQ,INDEX(ARQtPr,LPuR-1,1)),
SLPuP,MIN(NQt,INDEX(ARQtPr,LPuR-1,2)),

(LPuQ*LPuP)+(SLPuQ*SLPuP))

I could not make SUMPRODUCT work with the last four "name values" -- any idea why?

[#12]

@mathetes 

Interesting! I noticed an error in the solution I posted earlier. It didn't adversely affect the result (although it might have under some other conditions), but it was wasted characters in that they added no value.

Here's the corrected (and more streamlined) version.

=LET(

PQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Purchase")),
SQt,FILTER(TrnQty,(ProductName=J18)*(TrnType="Sale")),
NQt,SUM(PQt)-SUM(SQt),
LPuR,COUNT(PQt),
ARQtPr,FILTER(TrnQty:TrnPrice,(ProductName=J18)*(TrnType="Purchase")),
LPuQ,MIN(NQt,INDEX(ARQtPr,LPuR,1)),
LPuP,INDEX(ARQtPr,LPuR,2),
SLPuQ,MIN(NQt-LPuQ,INDEX(ARQtPr,LPuR-1,1)),
SLPuP,INDEX(ARQtPr,LPuR-1,2),

(LPuQ*LPuP)+(SLPuQ*SLPuP))

[#13]

@Sergei Baklan 

Let me clarify the logic of the challenge, as follows:

1. Calculate the unsold quantity of the product by subtracting the sold quantity from the purchased quantity. For "Courage", the purchased quantity of 2,611 units minus the sold quantity of 2,430 units equals the unsold quantity of 181 units. Absent any contrary evidence of loss, the unsold quantity must be equal to the physical count. 

2. Determine the quantity of the product from the last purchase. For "Courage", such quantity is 101 units from Transaction 31 at 76.00 per unit. 

3. Determine the lower value between the physical count and the quantity from the last purchase. For "Courage", the lower value between the physical count of 181 units and the quantity from the last purchase of 101 units is obviously the latter. Thus, of the 181 units counted physically, 101 units originate from the last purchase of 101 units from Transaction 31 at 76.00 per unit while the remaining 80 units originate from the second to the last purchase, which is 760 units from Transaction 22 at 65.00 per unit. 

For "Integrity", the lower value between the physical count of 170 units and the quantity from the last purchase of 550 units is obviously the former. Thus, the entire 170 units originate from only the last purchase of 550 units from Transaction 40 at 20.00 per unit. 

4. Determine the value of the physically counted inventory under the FIFO method by multiplying the dissected units by their corresponding purchase prices. For "Courage", (101 x 76.00) + (80 x 65.00) = 12,876.00. 

For "Integrity", (170 x 20.00) = 3,400.00. 

Amazed by how I generate these formula exercises? These exercises are real business circumstances included in my textbooks on EXCELlent Financial Accounting and Reporting!

[#14]

@mathetes 

Congratulations! You have significantly improved your solution. Were you still unable to use SUMPRODUCT as you announced earlier? I did but I temporarily conceal its usage until I ultimately divulge my solution.

[#15]

@JMB17 

Even without LET, we can still derive the correct results with a formula that conforms to the rules of the challenge. If you have the courage to comply, you can perhaps devise an obedient solution.

[#16]

@Twifoo 

Yes, still unable to get SUMPRODUCT to work with the two variables named in the LET function. So I had to do it less elegantly.

It was definitely a learning experience, both in my own solution, but also, now, looking through how others have solved it. A great example of how Excel enables multiple routes to the same solution.

In the real world, however, I doubt I'd be an advocate of doing it all in one formula; definitely wouldn't recommend it without LET. The LET function does enable readability, both by shortening, and by ultimately using names that make sense in subsequent formulas. I like for formulas to be intelligible, not only be the developer, but also by the less experienced... So helper columns come in handy for that. It'd be interesting to hear what others think.

[#17]

@Twifoo 

Sadly, I'm still on office 2016 and have few, if any, functions besides sumproduct that natively accept array arguments. Not using CSE or named formulas makes for a lot fewer tools in the toolbox.

One point of clarification, though. Do you intend for the formula to only analyze the last two entries or dynamic? Say the Sales were 200, leaving 2411 in inventory? It appears the expected results formula(s) are hardwired to only look at the last two purchases (but I know they are overly simplistic and not your actual solution).

[#18]

@JMB17 

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.

[#19]

@Twifoo 

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)))

[#20]

@JMB17 

Your formula must not refer to any cell in the range G1:L15. Nonetheless, you should refer to J18 for your formula in L18.