Power BI with your accounting data

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

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

Days Sales Outstanding (DSO) for example is easy to calculate and while it is a good measure for the current workload facing the collections team, it is flawed as a measure for average debtor days because it is so heavily influenced by recent sales activity.

Similarly, Overdue/Outstanding % is easily calculated and a rough indicator for how well your collections team is performing, but it pays no regard to the extent to which your debt is overdue. For example a large debt that is one day overdue has the same impact on this ratio as if it were 100 days overdue. Similarly, like DSO, is is also very heavily influenced by recent sales activity.

Consider also a customer on 30-day credit terms who has settled two invoices. One for £10,000 after 30 days, and one for £1,000 after 90 days (let’s say there was some confusion over the billing that caused the delay in settlement). The average time to pay is 60 days, which might look bad. However over 90% of the debt was paid on time, which is not bad.

Using weighted averages can be a neat way around this problem by taking into account the size of every receivable. Historically, weighted averages would have been quite tricky or at least laborious to calculate because they involve the detail level of every single invoice. New tools like Power BI however make this kind of calculation very simple to do if you can find an easy way to get your sales invoice data into Power BI (for example via anOData feed).

In the context of Accounts Receivable, a weighted average is calculated by taking into account the values and dates of each and every invoice that forms becomes part of the receivables balance.

For example, the formula to calculate the of weighted average debt age is:

In his blog about this, Michael Van Luven makes a strong case for using weighted average collection days (WACD) rather than Days Sales Outstanding (DSO). His claim is that it could be used to add more predictability for incoming cash flow forecasts. I believe that WACD would certainly be a better predictor than DSO. In our Accounts Receivable solution for Sage 50, we use the average pay days for each customer to help predict incoming cash flow. It is not quite the full-blown WACD model that Michael espouses (we could do that, it just be much heavier on the data needs), but given that the outstanding receivables book is made up of a portfolio of customers with different payment habits our model is quite a good approximation for the WACD model.

To take a look at how this works, let’s work through an example to calculate the weighted average pay days for two invoices. Of course with a tool like Power BI, applying this to 200,000 invoices from 1,000 customers would be a breeze.

Without applying any weights based on the amount, the average pay days for the total sum of £2,77,500 is (61 + 31)/2 which is 46.0 days.

The weighted average pay days however is 58 days, which is much higher because the larger invoice took longer to pay.

Inv No.

Invoice Date

Pay Date

Pay Days

Amount

Weighted Value

(Amount X Age)

90

15/06/2018

15/08/2018

61d

£250,000

15,250,000

92

10/07/2018

10/08/2018

31d

£27,500

825,500

TOTAL

£277,500

16,102,500

Weighted Average pay days = (Amount x Age) / Total O/S values)

Invoice 90 = 250,00 x 61 days = 15,250,000

Invoice 92 = 27,500 x 31 days = 852,500

(15,250,000 + 852,500) / 277,500 = 58.0 Days.

As you can see there is a gap of 12 days between the Average Pay Days (46) and the Weighted Average Pay Days (58) . You can use this gap as a benchmark to evaluate your collection effectiveness. A decrease in the number over time will show that the accounts receivable is being collected more efficiently. If the number increases, the collections team will need to walk their processes to find the problem.

An added benefit of using a tool like Power BI to perform these calculations is that you can drill down or slice them by customer. The customers with the largest gaps are the ones that need the most focus from the collections team.

In our Accounts Receivable solution for Sage 50 Accounts we include a multi-row card showing the following weighted averages.