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.
