Debtor Days Countback method

[Trax]

Hi, I am looking for some help trying to calculate debtor days using the countback method.

I have a spreadsheet that shows invoices and outstanding debtors month by month and need to calculate the how many days the outstanding debt represents using the countback method.

My figures are contained in columns representing the months (B7:O7), with the month end debtor totals in row 8 and invoiced amounts in row 9. I would like to include the debtor days in row 10.

Grateful for any help.

Thanks

[Richard Buttrey]

Hi,

What do YOU mean by countback method. It would help if you were to manually add the results you expect.

Surely dividing the Debtors balance by the Billed amount each month will give you the average number of months the debt represents.
You could multiply that by say 365/12 to get an average number of days

[Trax]

Thanks for your response Richard.

The countback method can be shown in these steps:

To calculate your DSO there are some key steps to the process, which we’ll outline for you here:

Calculate your open ledger – start by calculating the outstanding accounts receivable figure (open_ledger) at the end of the period. Let’s assume we’re looking for January 2018’s DSO, so your period end date will be 31-01-2018. To find the balance of each invoice we need to account for partial payments and credit notes applied to invoices up until 31-01-2018. By doing this we can see what our accounts receivable balance looked like at the end of the January period.
Calculate your gross sales – next, we calculate the total amount of sales for the period (gross_sales). This is done by calculating the total amount of all receivable invoices between 01-01-2018 and 31-01-2018.
Compare your open ledger to gross sales – We now have have two figures – our ‘open ledger’ number and our ‘gross sales’ number. To continue the calculation, we must compare these two variables. If your open ledger figure is larger than your gross sales, DSO is increased by the number of days in the period – it’s that simple. The open ledger number is then reduced by your gross sales figure. Note that the starting value of DSO is zero when beginning your calculation.
Counting back for prior periods – Having calculated the figure for our first period, we then need to ‘count back’ for the preceding periods. To do this, steps 2 and 3 are repeated for the preceding periods (i.e. for the December and November periods), checking that your open ledger is bigger than your gross sales number.
Apply the countback method formula – if your gross sales figure becomes larger than the open ledger number, DSO is increased by taking the ratio of the open ledger and gross sales numbers and multiplying this by the number of days in that period

[Richard Buttrey]

...and using those rules are you able to manually add the results you expect so that we can check any solution we might come up with?

[FredKer91]

To all,
i have the kind same issue to deal with ...here is the same file with my guess, i know the calculation but for the starting month i don't know how to manage, basically we have to represent how many days the remaining turnover (or oustanding balance) based on the number of days in the month, and then go back in the time if there is still an amount oustanding.
looking forward to have your feedback.
regards,
Fred

[jeffreybrown]

Hello Fred,

Please take a moment and read our Forum Rules. Please do not hijack another's thread, but rather create your own.

[FredKer91]

Hello Jeffrey,
this is the same topic and concerning the same spreadsheet? so why would i post another thread for this?
let me know as i didn't get so much this rule.
regards,
Fred

[jeffreybrown]

Because you are not the original poster. Not only that, many people do not look at threads a year old.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread