Power BI Accounts Receivable Template to drive down debtor days

Power BI Accounts Receivable Template

The Power BI Accounts Receivable template from Accounting Insights is for finance and collections professionals that want to drive down average debtor days.

Unlike conventional aged debtor reports, this Power BI Accounts Receivable template is highly interactive.  Across just three report pages, the report provides the following insights:

1. A top-level overview of debtors, KPIs and cash outlook for the month
2. A timeline of customer(s) balances and invoices falling due, with customer and invoice details
3. Trends of Debtor Days (DSO), Sales and Debtors for the last 3 months and 12 months

Receivables overview page

The Receivables Overview page (called “Receivables”) of the Power BI Accounts Receivable Template gives a company-wide overview of your debtors.

10 Accounts Receivable KPIs tracked by this page

The Receivables Overview page of the Accounts Receivable Template for Power BI tracks the following 10 Accounts Receivable KPIs for the whole company and any selected customers:

1. Aged Debt by Aged Period and by Week
2. Total outstanding (unreconciled) debt
3. Total Overdue Debt
4. Overdue ratio (overdue/outstanding) x 100%
5. Days Sales Outstanding (based on 52-weeks sales)
6. Weighted Average Debt Age
7. Weighted Average Due Days
8. Weighted Average Overdue Days
9. Receipts to date this month
10. Total receipts expected this month

Collections Page (timeline of receivables due)

The Collections page presents a more detailed picture of your Accounts Receivable, showing  timeline of receivables falling due by customer, invoice and invoice item with the customer(s) balance history.

Receivables Trends Page

The trends page of the Accounts Receivable template shows the underlying trends of the receviables over the last 12 months and three months.  Three trends are tracked: 

1. Days Sales Outstanding (aka “DSO” or “Debtor Days”), calculated on a 28-day basis (to match the trading patterns of the company).  From the trends graph, you can see that there was a spike around 10 months ago, but the underlying trend has been down (an improvement).
2. 28-Day Gross Sales Running Total.  It is important to look at DSO trends and Debtor Balance trends in the context of sales that are being made.  In other words, if you make no sales, then your debtors’ balance will reduce, but this is not usually a good thing.  In this example, we have selected 28-days rolling sales as it matches the underlying sales patterns and collections patterns of the company, so reducing spurious “noise” from sales patterns that are misaligned from the reporting patterns.
3. Customer balances.  In this case we are showing all customer balances, including those with a credit balance since all customers in this dataset trade on credit.