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.


Power BI

Power BI tip: use Boolean columns to add a simple DAX filter

DAX

Hugh Johnson @9/5/2018 6:15:25 AM

Power BI tip: use Boolean columns to add a simple DAX filter


Power BI Measures are a great way to calculate aggregations and summaries of your data. This article explains how you can use Boolean columns to add a simple DAX filter to your Measures, and so make it very easy to create those aggregations.


In Power BI, something that you often want to do is create aggregations based on filtered sets of your data.  This may be aggregations based on time, such as YTD or by geography such as "Within the EU". 

In DAX there are many functions available to create a filtered set of your data.  Wherever the DAX function includes <filter> as a parameter, such as with the CALCULATE or FILTER functions, then Boolean TRUE/FALSE columns in your dataset can make this very easy.  In the our Power BI template for Sage 50 we have added many Boolean TRUE/FALSE columns to the raw data from the Suntico BI OData feed, as well as to some additional Calculated Columns such as [IsReceivable] in the BITransSplits table.  This makes it really easy to add filters to any Measures that you may want to create to perform aggregations of your data.


Power BI

Power BI vs Tableau: who is winning the mindshare?

Power BI

Hugh Johnson @9/2/2018 7:44:19 PM

Power BI vs Tableau: who is winning the mindshare?


In an analysis of Google Trends over the last 12 months, Power BI occupies nearly 50% of UK Google searches relating to Business Intelligence. Look at the relative searches relating to Power BI vs Tableau. Is Power BI in danger of becoming a generic term, like Google, Hoover, or Jet-Ski?


Is Power BI in danger of becoming a generic term?

We search for something on the internet, we "Google" it.  When we vacuum the floor, we "Hoover" it.  There are many examples of brand names that have become generic terms.  Could Power BI be going the same way for business intelligence?


Power BI

My favourite three Power BI DAX functions

Power BI, DAX

Hugh Johnson @8/30/2018 7:53:46 AM

My favourite three Power BI DAX functions


A summary of my favourite Power BI DAX functions. SUMX, CALCULATE and FILTER. Why I always use SUMX and not SUM. The power of CALCULATE. The super combination of CALCULATE & SUMX together. The importance of filters and FILTER. Example given in DAX code.


DAX (Data Analysis Expressions) is the language used to write formulae in Power BI when you want to create new Calculated Columns or Measures.  In many respects it is similar to the way you write formulae in Excel.  Stated more simply, DAX helps you create new information from data already in your model.  While it is easy to make visuals in Power BI from your existing data model, we regularly want to see information for particular time periods, or with specific filters applied or create new calculated values based on our raw data.  There are some super free online resources available to learn DAX, which will help you to turn your simple Power BI model into something great.  Learning just a few basic functions of DAX can assist you with unlocking numerous new bits of knowledge from your data.  Here I introduce my favourite DAX functions that I use in the Accounts Receivable Solution for Sage 50 and the Power BI Template for Sage 50.



Power BI

DAX Running Total Calculation in Power BI

Power BI, DAX

Hugh Johnson @8/29/2018 7:04:03 PM

DAX Running Total Calculation in Power BI


Why and how to do a DAX running total calculation in Power BI. Two methods are presented: using the Quick Measure facility in Power BI, and writing your own DAX formula from scratch. Use variations to calculate running totals like YTD and rolling totals like sales last 52 weeks (example given).


What is a running total?

Power BI

Power BI Adoption By Country

Power BI

Hugh Johnson @8/29/2018 7:03:04 AM

Power BI Adoption By Country


An estimate of relative Power BI adoption by country, based on Google search volume for the term "Power BI". It appears that interest in the UK is more than double that of the USA, and in New Zealand more than four times.


Microsoft does not publish figures for Power BI adoption by country.  I am not surprised, but it is something that I wanted at least some kind of feel for to help me plan the development and roll-out of Suntico BI.  So as a proxy for relative adoption rates by country, I looked at monthy Google search volume per capita for the term "Power BI".  I picked a few mature English-speaking countries for which Power BI would have been available for the same length of time, and that for various reasons were countries that we were interested in targeting for our service.  The results are below.


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.  


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

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

+353 1 567 4069