Power BI with your accounting data

Weighted Average Receivables Demo

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.

Weighted Average Receivables Age (Days)

This measure calculates the weighted average age of all of the outstanding sales invoices. It is effectively your Aged Debt report condensed into a two numbers: the total outstanding, and the weighted average age. In this example, it is £736890.58 with a weighted average age of 50.40 days.

When we calculate the weighted average age, we multiply the invoice amount with the age of the invoice. If we sum this for all outstanding amounts, then divide this by the total outstanding, then we end up with the weighted average age of all of the receivables.

For example, if we have two invoices: the first is for £100 and is 60 days old and the second is for £1,000 and is 30 days old.

The average age of the two invoices is 45 days ( (60 days + 30 days) / 2 ). The weighted average days for the two invoices will be-

= (£100 x 60 days + £1,000 x 30 days) / £1,100

= 6,000 + 30,000 /1,100

=36,000 / 1,100

= 32.72 days

The picture is better than I had first thought because the much older invoice is the smaller one.

Try the following in our Power BI demo for Accounts Receivable

If you go to page 1 of our Power BI demo for Accounts Receivable, you can try exploring these numbers. Here we can see the benefits of using a tool like Power BI to crunch the numbers. Since we have calculated the weighted average receivables age based on every single invoice, we can now slice this calculation to see the contribution by each customer.

If you now select the largest debtor “105 Auto Stop” and then the second largest “Advance Care Rx Pharmacy”. Watch how the weighted outstanding amount and the weighted average age changes.

So we can see that a weighted average debt age calculation not only helps us to understand our overall accounts receivable position, it also helps us to see which customers need the attention from our collections team the most.

Weighted Average Due Days

Many business will offer different credit terms to different customers and for different products and services. A new customer, or riskier customer may be offered shorter credit terms than a large, very reliable customer. The irony here is that it tends to be your largest customers that have the longest payment terms, driving up your weighted average debt age. Similarly, you may offer different credit terms for different products and services. You may, for example, offer longer terms for products that are very high margin, or for which you have very favorable terms with your supplier.

If in your business, you have varying credit terms for different invoices, then a simple weighted average age does not give you the full picture. You need context. In other words, how is the age of my receivables relative to their credit terms?

To make this analysis we need to calculate the weighted average due days for every outstanding sales invoice. This calculation is based on the same theory of weighted average age.

Take a look again at page one of the Power BI example report. You can see that that my weighted average due days is 37.54 days, but if I select my largest debtor we can see that is has weighted average due days of 60.02 days. This puts into context the age of my receivables against their credit terms.

Weighted Average Overdue Days

Now that we have the weighted average age and the weighted average due days for our receivables portfolio, we can calculate, on a weighted average basis, the weighted average overdue days, which is simply:

Weighted average debt age -Weighted average due days.

What does our Power BI demo data say (go to page 1)? We see that that we have total debtors of £736,890.58 that is on average 5.87 days overdue. This is not particularly good.