Power BI with your accounting data

KPIs for Accounts Receivable Tracking

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

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
Read More

Topics: Accounts Receivable

How to use Power BI to estimate each invoice receipt date

Posted by Narayani Ambar on 17-Sep-2018 13:56:21

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.

Read More

Topics: Accounts Receivable

Power BI scatterchart showing overdue invoices by customer

Posted by Narayani Ambar on 14-Sep-2018 15:50:19


Read More

Topics: Accounts Receivable

Power BI Treemap for Accounts Receivable analysis

Posted by Hugh Johnson on 12-Sep-2018 07:00:43
Read More

Topics: Accounts Receivable

Weighted Average Receivables Demo

Posted by Narayani Ambar on 28-Aug-2018 13:50:54

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.  

Read More

Topics: Accounts Receivable

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

Posted by Narayani Ambar on 27-Aug-2018 17:48:44

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

Read More

Topics: Accounts Receivable

Accounts receivable forecasting method

Posted by Narayani Ambar on 24-Aug-2018 00:25:03

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.

Read More

Topics: Accounts Receivable, cash flow

Accounts Receivable - what they are and why they are important

Posted by Hugh Johnson on 22-Aug-2018 13:15:57

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. 

Read More

Topics: Sage 50 Accounts, Accounts Receivable

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

Posted by Narayani Ambar on 22-Aug-2018 11:07:50
Read More

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

Power BI examples with Sage 50 data

Posted by Hugh Johnson on 20-Aug-2018 14:24:48

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

Read More

Topics: Accounts Receivable, sales, Power BI Examples

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.


  • 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