Add numbers backwards cumulatively until certain threshold is found

[ORoxo]

Hi everyone,

I am trying to calculate DSO on a column based on Gross Sales and Accounts Receivables. Below you will find a sample dataset

Period	Number of days	Gross Sales	AR	DSO
Dec-23​	31​	85 853 156​	90 000 000​	32.25​
Nov-23​	30​	99 241 024​	81 511 301​
Oct-23​	31​	89 090 451​	83 905 179​
Sep-23​	30​	95 662 748​	87 647 130​
Aug-23​	31​	89 739 014​	86 318 572​
Jul-23​	31​	93 315 089​	88 047 881​
Jun-23​	30​	90 573 452​	84 194 748​
May-23​	31​	86 162 472​	83 217 557​
Apr-23​	30​	99 297 338​	88 824 067​
Mar-23​	31​	99 537 961​	81 974 924​
Feb-23​	28​	94 571 360​	84 082 714​

The formula on the DSO column works should work as follows:

Dec-23: Is AR > Gross Sales? If so, return number of days from Dec (31) and move on to the following check "is (AR from Dez - GS from Dez) higher than Sales in Nov"? If so, add the number of days for Nov (30). Otherwise do 31+ (90 000 000 - 85 853 156 ) / 99 241 024.

In case the answer to the question in bold above is yes, the test should be carried forward until the answer for one of the months is no, in which applies the test underlined.

I have been trying to wrap my head around this, but can't find a solution.

Maybe you can help me please?

Thank you so much in advance!

 

[KRice]

Interesting…what version of Excel are you using? If it’s Excel 365, a formula using recursion could be used, otherwise you’d need helper cells. Alternatively, VBA could be used. You can update your account details to include the Excel version.

 

[ORoxo]

Interesting…what version of Excel are you using? If it’s Excel 365, a formula using recursion could be used, otherwise you’d need helper cells. Alternatively, VBA could be used. You can update your account details to include the Excel version.

Hii KRice! I have access to Excel 365, yes. I never heard about recursion - let me look into that.

 

[KRice]

I'm not familiar with Days Sales Outstanding (DSO), so I needed to read a little more about it. While it appears that there are several different methods for computing it, I believe you're describing the Countback method. As a metric for estimating the number of days of average sales that are currently outstanding, for any given month, we need to look at that month's gross sales and accounts receivable, and then possibly look back sequentially at the preceding month's gross sales until the sum of gross sales (from the current month until some earlier month) offset the current month's account receivables balance. For whichever month the offset is encountered, the average daily sales that month establishes the number of days from that month that should be used, as well as the number of days for any intervening months.

Here is one approach that I believe will work for your purposes. I'm not sure how far back in time the records might go, so I've used dynamic ranges that assume we can determine the bottom of the data block by finding the last non-blank cell in the Period column (column A)...and the last row number is assigned to the "lrow" variable. The "periods" are assumed to actually be dates--the first days of the month/year associated with the financial numbers, or actually any date in the month-year to be displayed, and then the cell is formatted to show the date in the format "mmm-yy". This array of dates (periods..."pd") begins in A2 and extends down to row "lrow". The advantage in using a date in this column is that a formula can be used to return the number of days in that month, so a days columns isn't necessary. Similarly, the gross sales "gs" begin in cell B2 and extend down to row "lrow". The current month's accounts receivable balance is variable "ar", assumed to be in cell C2. Then we establish the array of the number of days in each month ("ndm") shown in each period "pd". Note that all of these arrays do not have a fixed cell reference for the beginning of their ranges, but they all terminate at row "lrow". So as the main formula is copied down the table, the DSO for each month will be based on financial data that begins in that month and potentially extends back in time to the bottom of the table.

Then the more complicated part: we form an array of the cumulative sum of gross sales "gs" for each period and subtract from those sums the "ar" amount to create the difference "d" array...the difference between the cumulative sums of "gs" and "ar". Based on the Countback methodology, we know that when this "d" array first becomes >=0, then we have gone back far enough (in time) such that the sum of gross sales back to that earlier period will be sufficient to offset the current "ar" balance. We find the array index of this earlier period and assign it to variable "r". Then we compute the number of days required from each period in the "e" array, and then in the "f" array, we limit the maximum number of days to be taken from the "e" array by considering the maximum number of days that can be contributed by that period based on "ndm". Finally, the sum of the first "r" results in the "f" array is taken. In some cases an error will be the result if the financial data do not allow for the "ar" balance to be offset (MATCH never finds the "r" value"), so an error trap returns a message.

MrExcel_20240308a.xlsx
	A	B	C	D
1	Period	Gross Sales	A/R	DSO
2	Sep-21	12	30	59.4
3	Aug-21	19	21	35.4
4	Jul-21	14	13	28.8
5	Jun-21	3	6	can't be determined
CountbackDSO

Cell Formulas
Range	Formula
D2:D5	D2	=LET(lrow, LOOKUP(2, 1 / (A:A <> ""), ROW(A:A)), pd, A2:INDEX(A:A, lrow), gs, B2:INDEX(B:B, lrow), ar, C2, ndm, DAY(EOMONTH(+pd, 0)), d, SCAN(0, gs, LAMBDA(acc,val, acc + val)) - ar, r, MATCH(TRUE, d >= 0, 0), e, (1 - d / gs) * ndm, f, IF(e > ndm, ndm, e), IFERROR(SUM(TAKE(f, r)), "can't be determined"))

Same formula applied to your original data:

Period	Gross Sales	A/R	DSO
Dec-23	85,853,156	90,000,000	32.25
Nov-23	99,241,024	81,511,301	24.64
Oct-23	89,090,451	83,905,179	29.20
Sep-23	95,662,748	87,647,130	27.49
Aug-23	89,739,014	86,318,572	29.82
Jul-23	93,315,089	88,047,881	29.25
Jun-23	90,573,452	84,194,748	27.89
May-23	86,162,472	83,217,557	29.94
Apr-23	99,297,338	88,824,067	26.84
Mar-23	99,537,961	81,974,924	25.53
Feb-23	94,571,360	84,082,714	24.89