by Hugh Johnson, Posted on 10/12/2018 10:23:58 AM
A Baker's Dozen of KPIs for Accounts Receivable tracking
If you have objectively tried to measure the performance of your collections team, you will know just how difficult this is to do well. In my opinion, usefulness has often been traded for measurability as getting the answers out of standard accounting software systems has been too difficult. In this blog list some of the most common KPIs for Accounts Receivable, and discuss their uses and limtations.
Days' Sales Outstanding (DSO) is calculated over a given time period as (Accounts Receivable) / (Average Daily Sales).
It is a very popular KPI for Accounts Receivable that is useful, but often misused. Please refer to this discussion about Days' Sales Outstanding for more details.
The main decision to take when calculating DSO is over what time period you calculate the average daily sales. Some people take 30 days, , some 90 days, some 12 months. Personally, I think that 30-days is too short for most situations as this will generate an Average Daily Sales number that will fluctuate wildly from one day to the next relative to the Accounts Receivable number. I think that 12-months can be a bit long since you are comparing today's Accounts Receivable number, so my tendancy is to go for 91 days. I chose 91 days rather than 90 as it is a multiple of 7 and so will generally include the same number of working days.
As I mentioned in my notes about DSO, the way that DSO is normally calculated is to take today's receivables figure and divide this by the average daily sales over some period (for example a year). This can lead to big daily swings in the DSO figure where your recent daily sales are very different from the average. If you are using the DSO KPI as an indicator of the workload facing your collections team then this is fine. If however you are using it as an approximation of your average days to pay, then looking at the Average DSO would give a much more robust and meaningful result.
To help to put your DSO number into the context of recent sales activity, we can calculate the Best Possible DSO (BPDSO). The purpose of the Best Possible DSO is to remove the effect of recent sales activity (or lack of it) on your DSO figure. The idea is to exclude current "within due" receivables from the DSO figure.
In this context the "Current Receivables" value is the sum of all the within-due receivables. In other words, it is saying the the Best Possible DSO value is that where there are no overdue invoices.
DSO minus BPDSO will then give you a much better indication of how well your collections team is performing. The closer to zero this number is, the better. This is not fully robust though, but it it is much more robust than just DSO on its own. Let's take the example of a company whose sales are steadily increasing and measures its DSO by taking average daily sales over the past year. The recent sales values will be higher than the average over the year. This would still mean that for the same collections performance a growing company will have a higher "DSO minus BPDSO" number than one in steady-state or decline.
The Overdue Ratio is the ratio of overdue debt to total debt.
The Overdue Ratio is very simple to calculate at an individual customer or aggregate level. It is is also very easy to misinterpret though. A recent spike in sales will drive up the total receivables and cut the Overdue Ratio, potentially masking an underlying problem. The status of a customer with an Overdue Ratio of 100% is clear - to an extent. Just looking at the Overdue Ratio will not let you distinguish between a customer with one small invoice that is one day overdue and another with one large invoice that is more than 90 days overdue.
You can see an example on page 1 of this report. In the report it is by default calculated for all customers, but by selecting one particular customer it is dynamically recalculated.
Note: for the Overdue Ratio to be correct you will need to have allocated all of your customer receipts against the corresponding invoices. For example if you take a payment on account from a customer it will decrease the amount outstanding from that customer but will not reduce the overdue amount, so, ironically, although the Overdue Ratio for that customer may have reduced, your KPI will show the opposite.
The Average Age for your receivables is simply the sum of the age of all of your outstanding invoices divided by the count of all of your outstanding invoices. It does not take into account the value of your receivables.
The Weighted Average Age of your receivables takes into account the value and the age of each invoice. For example a £1,000 invoice that is 100 days old will have the same Weighted Average Age as a £10,000 invoice that is 10 days old. The weighted average value of both combined would be 18.18 days.
This is the sum of all the days to pay each invoice, divided by the count of all fully-paid invoices.
This is a very useful KPI but can be difficult to measure well depending on how your accounting software stores invoice data. For example, Xero stores the final settlement date for each sales invoice with the invoice record. Sage 50Cloud Accounts does not (though you can retrieve this information from the ODBC tables), but provides this value pre-calculated for each customer on the customer record.
One thing to consider would be the time period you are analysing. If you want to do any kind of trend analysis you will need to be able to select the time period that you analyse (in which case the precalculated value in Sage 50 won't be enough since this is the average for all time for that customer).
The Weighted Average Collection Days will give you the true cost of financing your customer credit by taking into account the value of the debts. In other words, it costs you the same to finance £100 for 100 days as £1000 for 10 days.
A nice side-effect of the Weighted Average Collection Days KPI is that it can automatically take into account the situation where you have multiple payments at different times against the same invoice. To calculate the WACD you will need full details of all receipt allocations, so for each customer receipt you have the receipt date, the amount allocated to a particular invoice and the invoice date. In so doing, the invoice does not have to be fully paid (unlike the situation for Average Collection Days) since you are only including the proportion of the invoice that is being paid off by that receipt.
The Weighted Average Terms will give you the true cost of the outstanding credit granted to (as opposed to taken by) your customers. For example if you issue two invoices, one for £1000 on 30-day terms and one for £2000 on 60-day terms, then the Weighted Average Terms (WAT) will be 50 days and not 45 days which would be a simple Average Terms. It is calculated as follows:
Average Days Delinquent (ADD)
The Average Days Delinquent figure is a measure of on average how many days overdue your invoices are. Many references on this subject give the formula as DSO minus Best Possible DSO (BPDSO). I don't agree with this. This formula will give you an approximation for ADD, but since both the DSO and BPDSO numbers are affected by recent sales patterns, you cannot rely on this formula. It is just that DSO minus BPDSO can be calculated very quickly and easily with very little data. All you need to know is the total receivables, total overdue and average daily sales. This is a quick calculation that can be done in seconds with a calculator or pen and paper.
A more accurate way to measure Average Days Delinquent is to calculate the Days Delinquent for every outstanding invoice (an invoice within due will have negative Days Delinquent), add all of these and divide by the number of outstanding invoices.
For each invoice, the Days Delinquent is simply Invoice Age minus Invoice Terms.
Rather like DSO, the Accounts Receivable Turnover Ratio is a measure of the proportionality of your receivables to your sales. Except that it is the inverse - instead of looking out how many days' sales will go into the current receivables value you are looking at how many times the receivables value goes into your annual sales value.
Power BI if you have your current receivables balances and all the historic receivables transactions for the period in question.
If your business were trading steadily and consistently with customers who always paid exactly on 30 days you would get an Accounts Receivable Turnover Ratio of 12 (give or take). If they paid exactly on 60 days then the ratio would be six (give or take). Anything inbetween would be, well, inbetween.
What I like about the Accounts Receivable Turnover Ratio is that, unlike DSO, you are comparing sales and receivables across the same time period. Unlike DSO, the ratio is not going to go screwy on you if you have an unexpectedly good or bad last-month's sales (except in extreme cases).
The Collections Effectiveness Index (CEI) gives a meaure of the effectiveness of the collections team over a period of time. It is calculated by dividing the cash collected in a given period by the amount that would have had to be collected to eliminate all overdue receivables.
To calculate the CEI for a month all you need is the Beginning Receivables, Sales for the Month, Ending Receivables and Ending Current Receivables. In this case we define "Current" as within-due.