Add numbers backwards cumulatively until certain threshold is found

ORoxo

Board Regular
Joined
Oct 30, 2016
Messages
149
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

PeriodNumber of daysGross SalesARDSO
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!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
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.
 
Upvote 0
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
ABCD
1PeriodGross SalesA/RDSO
2Sep-21123059.4
3Aug-21192135.4
4Jul-21141328.8
5Jun-2136can't be determined
CountbackDSO
Cell Formulas
RangeFormula
D2:D5D2=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:
PeriodGross SalesA/RDSO
Dec-2385,853,15690,000,00032.25
Nov-2399,241,02481,511,30124.64
Oct-2389,090,45183,905,17929.20
Sep-2395,662,74887,647,13027.49
Aug-2389,739,01486,318,57229.82
Jul-2393,315,08988,047,88129.25
Jun-2390,573,45284,194,74827.89
May-2386,162,47283,217,55729.94
Apr-2399,297,33888,824,06726.84
Mar-2399,537,96181,974,92425.53
Feb-2394,571,36084,082,71424.89
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top