Receivable Aging Formula Challenge

%3CLINGO-SUB%20id%3D%22lingo-sub-2234379%22%20slang%3D%22en-US%22%3EReceivable%20Aging%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234379%22%20slang%3D%22en-US%22%3E%3CP%3EForty-five%20days%20after%20I%20posted%20my%20%3CA%20title%3D%22Link%20to%20FIFO%20Inventory%20Formula%20Challenge%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Ffifo-inventory-formula-challenge%2Fm-p%2F2117589%22%20target%3D%22_blank%22%3EFIFO%20Inventory%20Formula%20Challenge%3C%2FA%3E%26nbsp%3Band%20my%20solution%20thereto%2C%20here%20is%20another%20challenge%20to%20harden%20your%20%3CEM%3Earray%20manipulation%20skills%3C%2FEM%3E.%20As%20usual%2C%20we%20anticipate%20the%20valuable%20insights%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F898063%22%20target%3D%22_blank%22%3E%40keenadvice%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%26nbsp%3Band%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3Bto%20enliven%20our%20learning%20journey.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Receivable%20Aging%20Formula%20Challenge.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266919iC2F625229EFACF97%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Receivable%20Aging%20Formula%20Challenge.PNG%22%20alt%3D%22Receivable%20Aging%20Formula%20Challenge.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2234379%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2234905%22%20slang%3D%22en-US%22%3ERe%3A%20Receivable%20Aging%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234905%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BAgain%2C%20not%20going%20for%20the%20shortest%20one%20with%20485%20characters.%20Just%20a%20working%20formula%20%3A)%2C%20though%20the%20end%20result%20has%20to%20be%20copied%20down%20and%20across.%20So%2C%20that%20might%20disqualify%20my%20contribution.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0Asales%2CFILTER(TrnAmt%2C(CtmrName%3DK%2422)*(TrnType%3D%24J%242))%2C%0Asalescount%2CCOUNT(sales)%2C%0Acollections%2CSUM(FILTER(TrnAmt%2C(CtmrName%3DK%2422)*(TrnType%3D%24J%243)))%2C%0Amatrix%2CIF(SEQUENCE(salescount)%26gt%3B%3DTRANSPOSE(SEQUENCE(salescount))%2C1%2C0)%2C%0Ainterim%2CMMULT(matrix%2Csales)-collections%2C%0Anet%2CIF(interim%26lt%3B0%2C0%2CIF(interim%26gt%3Bsales%2Csales%2Cinterim))%2C%0Adates%2CFILTER(TrnDate%2C(CtmrName%3DK%2422)*(TrnType%3D%24J%242))%2C%0Aage%2CCutoff-dates%2C%0Abracket%2CXLOOKUP(age%2CLowerBracket%2C%24J%2423%3A%24J%2426%2C%2C-1%2C-1)%2C%0Aresult%2CSUMPRODUCT(net*(bracket%3D%24J24))%2C%0Aresult%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-03-25%20at%2012.30.36.png%22%20style%3D%22width%3A%20349px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266972i0A52AF6EEEBB6139%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-03-25%20at%2012.30.36.png%22%20alt%3D%22Screenshot%202021-03-25%20at%2012.30.36.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2235021%22%20slang%3D%22en-US%22%3ERe%3A%20Receivable%20Aging%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2235021%22%20slang%3D%22en-US%22%3EYour%20solution%20is%20an%20admirable%20combination%20of%20multiple%20functions!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2235037%22%20slang%3D%22en-US%22%3ERe%3A%20Receivable%20Aging%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2235037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThanks!%20And%20it's%20the%20best%20I%20can%20do%20using%20what%20I%20would%20call%20%22out%20of%20the%20box%22%20functions%20and%20some%20tricks%20inspired%20by%20what%20I%20found%20on-line.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2248937%22%20slang%3D%22en-US%22%3ERe%3A%20Receivable%20Aging%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2248937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20explanation%20of%20my%20legacy%20formula%20can%20now%20be%20read%2C%20and%20my%20solution%20file%20can%20also%20be%20downloaded%2C%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Froberthontoriagascon.medium.com%2Fthe-array-manipulation-technique-in-this-receivable-aging-formula-is-mind-blowing-361fa3bdad8c%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Froberthontoriagascon.medium.com%2Fthe-array-manipulation-technique-in-this-receivable-aging-formula-is-mind-blowing-361fa3bdad8c%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2249101%22%20slang%3D%22en-US%22%3ERe%3A%20Receivable%20Aging%20Formula%20Challenge%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2249101%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThanks%20Robert!%20Looked%20at%20your%20solution%20and%20admit%20I%20would%20never%20have%20been%20able%20to%20come%20up%20with%20such%20a%20formula.%20It%20is%20only%20thanks%20to%20the%20modern%20Excel%20functions%20that%20I%20have%20been%20able%20to%20solve%20problems%20like%20in%20your%20challenge.%20So%2C%20where%20does%20that%20put%20me%3F%20I%20like%20to%20think%20I'm%20pretty%20OK%20with%20the%20new%20tools%2C%20but%20I'm%20not%20so%20good%20with%20%22old%20style%22%20Excel.%20Like%20you%2C%20I'm%20also%20surprised%20that%20nobody%20else%20responded.%20Quite%20unlike%20your%20previous%20challenge.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Trusted Contributor

Forty-five days after I posted my FIFO Inventory Formula Challenge and my solution thereto, here is another challenge to harden your array manipulation skills. As usual, we anticipate the valuable insights of @keenadvice @mathetes @Riny_van_Eekelen @Peter Bartholomew @Sergei Baklan, and @lori_m to enliven our learning journey.

Receivable Aging Formula Challenge.PNG

15 Replies

@Twifoo Again, not going for the shortest one with 485 characters. Just a working formula :), though the end result has to be copied down and across. So, that might disqualify my contribution.

=LET(
sales,FILTER(TrnAmt,(CtmrName=K$22)*(TrnType=$J$2)),
salescount,COUNT(sales),
collections,SUM(FILTER(TrnAmt,(CtmrName=K$22)*(TrnType=$J$3))),
matrix,IF(SEQUENCE(salescount)>=TRANSPOSE(SEQUENCE(salescount)),1,0),
interim,MMULT(matrix,sales)-collections,
net,IF(interim<0,0,IF(interim>sales,sales,interim)),
dates,FILTER(TrnDate,(CtmrName=K$22)*(TrnType=$J$2)),
age,Cutoff-dates,
bracket,XLOOKUP(age,LowerBracket,$J$23:$J$26,,-1,-1),
result,SUMPRODUCT(net*(bracket=$J24)),
result
)

 Screenshot 2021-03-25 at 12.30.36.png

 

Your solution is an admirable combination of multiple functions!

@Twifoo Thanks! And it's the best I can do using what I would call "out of the box" functions and some tricks inspired by what I found on-line.

@Riny_van_Eekelen 

The explanation of my legacy formula can now be read, and my solution file can also be downloaded, here:

https://roberthontoriagascon.medium.com/the-array-manipulation-technique-in-this-receivable-aging-fo...

@Twifoo Thanks Robert! Looked at your solution and admit I would never have been able to come up with such a formula. It is only thanks to the modern Excel functions that I have been able to solve problems like in your challenge. So, where does that put me? I like to think I'm pretty OK with the new tools, but I'm not so good with "old style" Excel. Like you, I'm also surprised that nobody else responded. Quite unlike your previous challenge. 

@Twifoo 

I have worked through the problem (yet to look at other solutions).  At the moment, I would say my solution is overweight and lacking elegance!  It will produce a column array for each customer (I try to get one formula for an entire table but it is often not possible without Lambda recursion).

=LET(
   receive, LET(
      ac, SUMIFS(TrnAmt, CtmrName,@CName, TrnType,Type),
      MMULT({1,-1},ac)),
   crit, (CtmrName=@CName)*(TrnType=Sale),
   Age, Cutoff - FILTER(TrnDate, crit),
   Amt, FILTER(TrnAmt, crit),
   Age′, TRANSPOSE(Age),
   Accumulate, SIGN(Age′<=Age),
   Difference, MINVERSE(Accumulate),
   Accum, MMULT(Accumulate, Amt),
   capped, IF(Accum<receive,Accum,receive),
   outstanding, MMULT(Difference,capped),
   Assign,  (Age′>=LowerBracket)*(Age′<=UpperBracket),
   byBand, MMULT(Assign, outstanding),
   byBand)

image.png

I appreciate your humble admission. I am certain you could improve your formula by perhaps avoiding the repetition of LET.
Now as I understand the problem, I think I have an idea for an improved solution making better use of the initial SUMIFS. I won't look at your solution yet!

@Twifoo 

Nearly got there.

image.png

Just need to replace Charles Williams's DIFF function.

@Twifoo 

Made it.  I think this is my best endeavour.

=LET(
a,SUMIFS(TrnAmt,CtmrName,N,TrnType,"S*",TrnDate,">="&Cutoff-UpperBracket),
b,INDEX(a,4,)-SUMIFS(TrnAmt,CtmrName,N,TrnType,"C*"),
D,{1,0,0,0;-1,1,0,0;0,-1,1,0;0,0,-1,1},
MMULT(D,IF(a<b,a,b)))

The formula produces the 4x4 result as a dynamic array.

image.png

I suspect that I have used your problem as a learning exercise rather than strictly following the rules but thanks anyway.

I also suspect that your formula use of the array constants will become inapplicable when the data is modified.
With your admitted comfortability with modern functions, I anticipate that, based on my explanation, you could perhaps reduce and convert my legacy formula to its modern version.

@Twifoo 

Yes, you are perfectly correct.  Two of the formulas are clearly dependent on there being 4 terms in the bracket.  If 'I' were the name given to your bracket index and 'J' were its transpose, the matrix used for differencing would be

=(I=J)-(I=J+1)

Interestingly, but at the expense of more characters, pre-multiplying by

=--(J<=I)

would cause the columns of the second array to accumulate, whilst

=MINVERSE(--(J<=I))

would form the term within the DIFF operation.

In this instance, I chose to hard-wire the array constant

D, {1,0,0,0;-1,1,0,0;0,-1,1,0;0,0,-1,1},

and use 4 in place of MAX(I) to return the final row of the accumulated account array.

 

@Twifoo , thanks for another interesting exercise. I didn't take part since is quite busy these days and answer only on posts which takes not more than 5-10 minutes of my time. This one is definitely requires much more. Perhaps some later will try to train my brains even if I know some answers.

@Twifoo 

May I second @Sergei Baklan's vote of thanks for some innovative challenge problems.

 

The attached contains a version that is meant to work using standard Excel.  It uses a strategy I employed before dynamic arrays came to Excel; that is, I used named formulas to hold the array formulas that I used all the time (this allowed me to avoid the effect of implicit intersection).  Counting character length was not scientific; I tried to substitute the formula lengths to account for each occurrence of a name.