Power BI with your accounting data

KPIs for Accounts Receivable Tracking

Posted by Hugh Johnson on 15-Oct-2018 08:20:38
Hugh Johnson
Find me on:

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.

  1. Days Sales Outstanding (DSO)
  2. Average DSO
  3. Best Possible DSO (BPDSO)
  4. Overdue Ratio
  5. Average Age
  6. Weighted Average Age
  7. Average Days to Pay
  8. Weighted Average Collection Days (WACD)
  9. Weighted Average Terms (WAT)
  10. Average Days Delinquent (ADD)
  11. Weighted Average Days Delinquent
  12. Accounts Receivable Turnover Ratio
  13. Collections Effectiveness Index (CEI)
Many KPIs are in this demo

   

 

Days Sales Outstanding (DSO)

Days' Sales Outstanding (DSO) is calculated over a given time period as (Accounts Receivable) / (Average Daily Sales).  

DSO Formula-3

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.

What period should be used to calculate Average Daily Sales?

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. 

Back to top

 

Average DSO

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.  

Average DSO

Back to top

 

Best Possible DSO (BPDSO)

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.  

BPDSO Formula

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.

Back to top

 

Overdue Ratio

The Overdue Ratio is the ratio of overdue debt to total debt.

Accounts Receivable Overdue RatioThe 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.

Back to top

 

Average Age

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.

Average Age for Receivables

Back to top

 

Weighted Average Age

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.

Weighted Average Age

Back to top

 

Average Days to Pay

This is the sum of all the days to pay each invoice, divided by the count of all fully-paid invoices.

Average Days to Pay

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).

Back to top

 

Weighted Average Collection Days (WACD)

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.  

WACD formulaA 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.

Back to top

 

Weighted Average Terms (WAT)

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:

WAT formula

Back to top

 

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.

Average Days Delinquent Formula

For each invoice, the Days Delinquent is simply Invoice Age minus Invoice Terms.

Back to top

 

Weighted Average Days Delinquent

Once you have calculated Weighted Average Terms (WAT) and Weighted Average Age (WAA), then the Weighted Average Days Delinquent (WADD) is simply WAA minus WAT.

Back to top

 

Accounts Receivable Turnover Ratio

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.

Accounts Receivable Turnover RatioThe convention would be to use one year as the analysis period.  I would use 364 days (because this is a multiple of 7).  If you choose a shorter period you could normalise the figure to an annual one - for example if you chose 91 days you could multiply your answer by 365/91 to get an annualised figure.

The downside of this KPI is that it is somewhat tricky to calculate because you need the historic receivables value for every single day of the analysis period in order to calculate the average.  This can be done quite easily though in a tool like Power BI if you have your current receivables balances and all the historic receivables transactions for the period in question.  

What value to expect?

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).

Back to top

 

Collections Effectiveness Index (CEI)

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.

Collections Effectiveness Index (CEI)

Back to top

 Many KPIs are in this demo

Topics: Accounts Receivable

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts