Accounts Receivable

KPIs for Accounts Receivable Tracking

Accounts Receivable

Hugh Johnson @10/15/2018 8:20:38 AM

KPIs for Accounts Receivable Tracking


How to track your collections team performance. A list of 13 KPIs for Accounts Receivable. Which ones to use depend on what you are trying to achieve and what data you have available to you.


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)


Accounts Receivable

How to use Power BI to estimate each invoice receipt date

Accounts Receivable

Hugh Johnson @9/17/2018 1:56:21 PM

How to use Power BI to estimate each invoice receipt date


How to estimate the date of each invoice receipt from your debtors. Using two clustered column charts in Power BI to show due and estimated receipts alongside tables of your outanding invoices can give strong insights into your epected cash flows and actions you may need to take.


How to use a clustered column chart in Power BI to display estimated vs due cash inflows from your accounts receivable trade debtors.  Reveal what customers are paying more slowly than expected and against which invoices.


Accounts Receivable

Power BI scatterchart showing overdue invoices by customer

Accounts Receivable

Hugh Johnson @9/14/2018 3:50:19 PM

Power BI scatterchart showing overdue invoices by customer


Do you want to analyse many variables together to segment your customers? Take a look at this Power BI scatterchart that shows overdue invoices by customer. Why use a scatterchart and not a pie chart, donut chart or treemap? What does a normal customer look like and what are the outliers?


Accounts Receivable

Power BI Treemap for Accounts Receivable analysis

Accounts Receivable

Hugh Johnson @9/12/2018 7:00:43 AM

Power BI Treemap for Accounts Receivable analysis


Take a look at this Power BI treemap of balance and overdue days by customer. Why use weighted average overdue days? Why use a Treemap and not a Donut Chart or a Pie-Chart? Example shown is uses Sage 50 Accounts data.


Accounts Receivable

Weighted Average Receivables Demo

Accounts Receivable

Hugh Johnson @8/28/2018 1:50:54 PM

Weighted Average Receivables Demo


Example weighted average receivables demo. Weighted average debt age, weighted average due days and weighted average overdue days. Interactive demo shows how you can slice by customer to see who you should be focusing on to drive down your debtor days. What does this tell you?


In a previous blog I introduced the idea of weighted averages to measure accounts receivable and how they can be used to give a more useful measure of your collections performance. In our Accounts Receivable solution for Sage 50 we have included a multi row card with three weighted average calculations to help you get a better picture of your receivables portfolio.  


Power BI

Weighted accounts receivable days - the true cost of customer credit?

Accounts Receivable

Hugh Johnson @8/27/2018 5:48:44 PM

Weighted accounts receivable days - the true cost of customer credit?


Do you track your aged debtors in terms of weighted accounts receivable days? Using Power BI to track your receivables terms, age, and overdue days as weighted averages will help to give you a true picture of the cost of customer credit. Is it time that you took a look?


Accounts receivable KPIs are often quite crude - with the advantages and disadvantages that go with that.


Accounts Receivable

Accounts receivable forecasting method

Accounts Receivable, cash flow

Hugh Johnson @8/24/2018 12:25:03 AM

Accounts receivable forecasting method


Add predictablility to your business with better estimates of when to expect customer receipts. This simple but effective method uses Microsoft Power BI to help with accounts receivable forecasting from your Sage 50 data. Try it free for 30 days.


The need for predictability

In business, it is important to understand how much cash is expected to be collected in the coming weeks from your Accounts Receivable. Having this information handy helps for better planning and cash flow management.

In an ideal world, all customers would pay all of their invoices on or before the due date. This is not the world we live in though and having realistic estimates of when you are actually going to get paid is really useful. 

In our Accounts Receivable for Sage 50 solution we have added a measure in the Power BI model to estimate which sales invoices will be paid this month, based on the payment history of each customer.

By putting this onto a Gauge Chart, along with receipts received so far this month, we can in an instant see what customer cash inflows we can expect this month and how we are progressing against this estimate.




We have used the gauge meter to display this KPI in our Accounts Receivable solution. It shows the difference between what we expected to receive vs how much we have actually received. Every month the value starts at zero and rises when any collection is made. The meter shows that in this month receipts amounting to 422.26K are expected and that so far we have collected 182.65K, 45% of the total expected amount.

The two measures that are feeding this gauge are within the “My Receipts Measures” table and are there for you to use in other visualisations as you wish.

Taking into account your actual customer behaviour

Your Sage 50 Aged Debtor reports may tell you how old your receivables are, but they will not tell you when your customers are likely to pay you. Sage 50 does, however track the average time that it takes for each of your customers to pay you. In our Accounts Receivable solution we take advantage of this. For each sales invoice, we calculate an estimated receipt date simply by taking the invoice date and adding the average time it takes for that customer to pay. The Estimated Receipts This Month are calculated by taking the sum of gross invoice values where the due date and the customer’s average days to pay fall into this month.

Let's say you have five customers with invoices that are due in this month. Now as per your past experience with these customers two of them take on average 45 days to pay an invoice, one 60 days and one 30. If you simply relied on taking your Invoices Due figure as your estimate for cash inflows you are going to be very wrong.


Accounts Receivable

Accounts Receivable - what they are and why they are important

Sage 50 Accounts, Accounts Receivable

Hugh Johnson @8/22/2018 1:15:57 PM

Accounts Receivable - what they are and why they are important


What are trade receivables or accounts receivable? Why are they important? Who should know about them? When and how should you track them?


What are Accounts Receivable (aka Trade Receivables)?

Accounts Receivable (also known as Trade Receivables) are monies owed to you by customers whom you have invoiced for products or services supplied but for which you have not yet received payment.  Your Trade Receivables are an asset (since you expect to receive cash from them) and in your accounting software, your total trade receivables will be the balance of your Debtors Control Account on your Balance Sheet. 


Accounts Receivable

Days sales outstanding (DSO) - A useful or misused KPI?

Sage 50, Power BI, Accounts Receivable, Days Sales Outstanding

Hugh Johnson @8/22/2018 11:07:50 AM

Days sales outstanding (DSO) - A useful or misused KPI?


Days Sales Outstanding (DSO) is a common KPI for Accounts Receivable. It is often misused as an indicator for the Average Collection Period or Average Debtor Days. What does it really mean, and how can you enrich it with a rolling average DSO measure or trend?


Power BI

Power BI examples with Sage 50 data

Accounts Receivable, sales, Power BI Examples

Hugh Johnson @8/20/2018 2:24:48 PM

Power BI examples with Sage 50 data


Interactive Power BI examples. Sales reporting and forecasting. Unexpectedly high or low customer activity. Stock reporting, days free stock forecast, undespatched sales orders. Accounts receivable, trade debtors, collections, customer history and expected receipts.


Here are some example Sage 50 reports produced using Power BI.


Copyright © 2019,
Accounting Insights Ltd.
All Rights Reserved.

Our Office :
Unit 4, Roof Garden Offices,
Clarehall Shopping Centre,
Dublin,
Ireland

+353 1 567 4069