How to calculate trade debtor days

The trade debtor days measure allows you to calculate how long it is taking a business to collect its debts. If you have trade debtor days of 45 but offer your customers terms of 30 days, then it is generally taking you longer to collect your debts from customers than it should.

Comparing the average trade debtor days in the current period to the prior period average allows you to consider whether the business is becoming better or worse at collecting its debts. Of course, a change in days could also be driven by a myriad of other factors – see ‘what can impact trade debtor days’ section below.

There’s a lot of misinformation online about the best way to calculate trade debtor days, so before we delve into the best calculation method, we will first revisit the basics.

What are trade debtors?

A trade debtor is effectively a customer who has been invoiced for products/services but who has not yet paid you for goods/services.

The opposite side to a trade debtor posting will either be:

• Deferred income (where billing in advance)
• Trade debtors (where recognising revenue at the point of invoice)
• Accrued income (where you have already recognised/’accrued’ revenue before billing).

Balances leave the trade debtor balance when they are paid or written off:

• Paid (Cr Trade Debtors, Dr Cash)
• Written off (Cr Trade Debtors, Dr P&L Bad Debt Expense, or Cr Trade Debtors Dr Bad Debt Provision if the debt has already been provided for)

If we raise a product invoice to a customer for £100 excluding VAT at the standard rate of 20%, our trade debtor balance would be £120. This is because the gross figure, including VAT, would be recognised in trade debtors. The full accounting entry would be Credit Revenue (P&L) £100, Debit Trade Debtors (BS) £120, Credit VAT (BS) £20.

This consideration of VAT is important but often forgotten. We must remember to consider VAT when calculating trade debtor days. Remember: not all businesses sell products with the standard VAT rates applicable, consider whether zero rated or reduced rate products/services are sold.

How to calculate trade debtor days

There are a number of methods to calculate trade debtor days.

The basic method of calculating trade debtor days

Most books and websites cite the basic method which takes the trade debtors balance divided by annual credit sales times by 365.

There are a few problems with this methodology, notably:

• Revenue (sales) does not drive trade debtors, billing (invoicing) does. This an important distinction because of the accruals basis of accounting. What do I mean by this? If you invoice a customer £1,000, that does not mean that you can recognise £1,000 of revenue straight away. For example, if you are billing in advance for a service that will take a year to deliver, the revenue should be recognised over the period to which the service relates. This is called ‘deferred revenue’. Taking the opposite example, accrued income is revenue which has been earned and recognised but where the customer has not yet been billed. With accrued revenue, you would have revenue but no trade debtor balance at all. Revenue should therefore only be used as the driver when it is a good proxy for billing (i.e. where there is an insignificant amount of accrued/deferred revenue).
  
• This ignores the fact that sales are unlikely to have been constant throughout the year (e.g. sales may have been lower at the start of the year and higher at the end of the year). Sales at the start of the year may not still be having an impact on the trade debtors balance if those invoices have already been settled.
  
• This example ignores the fact that if you do use revenue, you should gross up for VAT. This is because revenue is shown on the P&L net of VAT, trade debtors on the balance sheet are gross of VAT.

So if that method isn’t great, what’s better?

The count back method of calculating trade debtor days

So what is the count back method, and why is it better?

Well, the count back method for calculating trade debtor days using billing data involves considering billing in the most recent month first rather than factoring in sales across the whole year. It then works back to look at the prior month, and then the month before, until the full debtors balance has been accounted for. It sounds complicated but it’s really not when you work through it methodically.

Lets look at an example:

The above example shows you how you would calculate trade debtor days at March 2020 using the trade debtor balance and gross billings for the prior three months. Whilst this gives you an example for one month, you would typically drag the same formula across all months to create a runner. This allows you to compare how the debtor days balance changes over time. You can then compare the average across FY20 to the average across FY19, for example.

• M0 (looks at current month) – If current month trade debtors balance (3600) is more than current month billing (2400), then take current month days (31). If it’s less than current month billing, take trade debtors/billing * days in month. In this case, it is greater and thus 31 is returned.
• M1 (looks at prior month) – If (current month trade debtors balance minus current month billing) is more than prior month billing, then take prior month days. However, if it’s less than prior month billing, take (current month trade debtors balance minus current month billing) divided by prior month billing * days in prior month. In this case, 1,200 (3,600-2,400) is lower than prior month billing of 3,200. Therefore, the formula takes the 1,200, divides by prior month billing of 3,200 times by prior month days of 29. I.e. (1,200/3,200*29 = 10.9).
• M2 (looks at 2 months ago) – The formula does not need to cycle through to M2 because the billing in M0 and M1 already accounted for the full trade debtor balance at March 2020.

If you’d like to receive a copy of the Excel file, please leave a comment and I’ll be happy to send you a copy.

Calculating billings

If you don’t have billings data readily available, then remember you can calculate billings in a particular month using revenue + (opening deferred income – closing deferred income) + (opening accrued income – closing accrued income).

To prove this calculation method, lets imagine you recognise £10 revenue but don’t bill, the double entry would be Cr Revenue Dr Accrued income. In this scenario, you haven’t billed. Billings would be calculated as 10 (revenue) + (0 – 0) + (0 – 10) = 0. However, if you’d billed that £10 you would (Dr Trade Debtors, Cr Accrued income). Now your billings calculation would be 10 (revenue) + (0 – 0) + (0 – 0) = 10.

Should I include bad debt provision?

For the purposes of this calculation, you should use the gross trade debtors balance (i.e. gross of any bad debt provisions).

What about VAT?

The trade debtors balance includes VAT as it represents the total amount due from customers. However, billings will not include VAT. Billings should therefore be grossed up for VAT when calculating trade debtor days. This is because accrued income, deferred income and revenue are all recorded net of VAT, which is posted into a separate VAT account when the invoice is raised.

What can impact trade debtor days?

If trade debtor days have increased, cash is being collected slower. Conversely, if they have decreased, cash is being collected faster.

Either way, it’s important to understand what is driving the trend. Factors which could impact trade debtor days include:

• Longer or shorter terms being offered to customers
• Customer disputes leading customers to withholding funds
• Changing customer mix (as different customers may be offered different terms)
• Changing product mix (as a business may sell a variety of products, some of which are offered to customers with longer or shorter terms)
• Holiday periods (those responsible for paying at customers may be on leave)
• Early payment discounts
• Greater or reduced focus on cash collection by the business
• Industry practice – certain industries will withhold payment (e.g. building industry is well known for delayed/late payments and retentions)

Whilst seeking to improve your trade debtor days is important, you must factor in commercial considerations and relationships. For instance, customers may go elsewhere if competitors are willing to offer longer, more flexible terms.