Forum Discussion
Twifoo
Feb 08, 2021Silver Contributor
FIFO Inventory Formula Challenge
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 PeterBa...
Twifoo
Feb 10, 2021Silver Contributor
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.
JMB17
Feb 10, 2021Bronze Contributor
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).
- TwifooFeb 10, 2021Silver Contributor
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.
- JMB17Feb 10, 2021Bronze Contributor
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)))