Daily Sales Outstanding - Debtor days calculation- Count back Method
Hi,
I want to calculate Daily Sales Outstanding month wise from two Data tables. First Data set gives total Receivables and 2nd data set table gives Monthly Sales. Monthly DSO count back method starts by deducting latest month's Sales from Receivables. And keep deducting receivables with Sales until the balance is negative or zero and for each month we add 30 days.
Formula in excel = if(Receivables<Zero, Zero, if((Total Receivables-July'21 Sales)>Zero,30days, Receivables/July'21Sales*30,0)
For the next month the Receivables would be the balance which is Total Receivables minus July'21 sales
Sales Month
Sales
Receivables
DSO Calculation
JUL 2021
13,217.00
1,871,000
30
MAY 2021
1,116,310.00
1,857,783
30
APR 2021
885,353.00
741,473
25
FEB 2021
226,654.00
(143,880)
-
Total DSO days
85
I am struggling to get the formula in set analysis.
