by Hugh Johnson, Posted on 8/27/2018 4:38:59 PM

Accounts Receivable

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 an OData 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:

Sum of selected receivables (Amount X Age) ÷ Total o/s amount of all selected receivables

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 |

| £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.

- Debt age
- Due days
- Overdue days

Take a look at page 1 of this Power BI example report for Accounts Receivable.

- KPIs for Accounts Receivable Tracking
- How to use Power BI to estimate each invoice receipt date
- Power BI scatterchart showing overdue invoices by customer
- Power BI Treemap for Accounts Receivable analysis
- Weighted Average Receivables Demo
- Accounts receivable forecasting method
- Accounts Receivable - what they are and why they are important
- Days sales outstanding (DSO) - A useful or misused KPI?
- Power BI examples with Sage 50 data
- Track your outstanding receivables with Power BI
- Aged Debtor Report for Sage 50 Receivables

- OData (21)
- Power BI Desktop (16)
- Sage 50 Accounts (14)
- Accounts Receivable (12)
- Sage 50 (11)
- Power BI (11)
- ODBC (8)
- Sage 50cloud (7)
- Reporting (5)
- Accounting (5)
- Power BI Pro (5)
- accountant (3)
- accountants (3)
- sales orders (3)
- DAX (3)
- Connect Power BI to Sage 50 (2)
- currencies (2)
- customers (1)
- power bi datasources (1)

- Transaction Splits (1)
- sales ledger (1)
- sales invoices (1)
- product sales (1)
- stock (1)
- suppliers (1)
- metadata (1)
- chart of accounts (1)
- Maps (1)
- nominal records (1)
- nominal budgets (1)
- outstanding (1)
- overdue (1)
- sales (1)
- Power BI Examples (1)
- Days Sales Outstanding (1)
- cash flow (1)
- Connect Power BI to Sage 50 Accounts (1)

Clarehall Shopping Centre,

Dublin,

Ireland