Forum Discussion
DSO count back method
Hi all,
I'm in need of a formula for calculating the DSO (daily sales outstanding) with the use of the count back method.
Calculation:
Substract periodic sales (p5->P4 etc.) from outstanding money -> for every substraction count 28 days (1 period)
Do this until it cant be substracted without going negative
Divide remaining Outstanding by Periodic sales
P5 Outstanding : 12 000 000
P5 Sales : 5 000 000
= 12 000 000 - 5 000 000 = 7 000 000 (28 days)
P4 sales : 6 000 000
= 7 000 000 - 6 000 000 = 1 000 000 (28 days)
P3 Sales = 6 000 000
= 1 000 000 / 6 000 000 * 28 = 4,66... days = 5 days
28 + 28 + 5 = 61 days
Is it possible to fit this in one formula
Yes, it is possible using 365. Anything you can do in traditional spreadsheets by using helper ranges can be achieved with dynamic arrays using LET local variables.
To provide a formula, though, we would need to know the periods and their associated sales figures run from left to right (normal time series) or right to left (reversed), or similar considerations apply if written vertically.
Written newest to oldest one might have
= LET( opening, SCAN(0, sales, SUM) - sales, closing, DROP(SORT(HSTACK(opening + sales, outstanding),,,1),,-1), 28 * (closing-opening) / sales )- Hi Peter,
Could you give a broader explanation? I haven't used the LET function before and can't get it to work.Firstly, do you use Excel 365 (or 2021)?
Are you happy to proceed with the sales in reverse order (recent to historic)?
[it is probably possible to reverse the order within the formula but makes it even more complicated]
Have you applied the defined name 'sales' to the sales row and named the value 'outstanding'?
From there the calculation first generates a running total showing the value which is than added to the current sales at each step. The value reached after the value is added is capped by 'outstanding'. The difference is the sales breakdown you require.
