I got quite a response to my last post, so decided that maybe I should do a short series about how we show other frequently asked KPI’s, which are not straightforward to get from Dynamics NAV.
Here in the UK we have an obsession with credit, probably caused by a culture of paying later than the agreed terms. In fact, the bigger the business the longer they take to pay generally, and it doesn’t matter what the invoice says are the terms. It’s not illegal here to be late, just bad practise.
That means that every business is focused on not just writing invoices, but getting paid with the money firmly in their bank. And to measure your performance in doing that, they focus on what is called average debtor days.
So how do we calculate that and show it in Dynamics NAV? Preferably, shown on a Cue on the credit controllers role centre but also on the director’s phones?
What’s the definition?
Well first we have to define how it’s calculated and there are two measures which are usually defined as follows:
Average Debtor Days
“The average days between the invoice document date and the date the invoice is closed (i.e. paid in full so fully applied with zero amount remaining) including only invoices that are ultimately closed with a payment and not those that that are reversed out or have credit memo’s applied.”
Average Overdue Debtor Days
“The average days between the original due date and the date the invoice is closed (i.e. paid in full so fully applied with zero amount remaining) including only invoices that are ultimately closed with a payment and not those that that are reversed out or have credit memo’s applied.”
Customer Entry Statistics Page
Now Dynamics NAV’s Customer Entry Statistics Page (P302) has an attempt at this first calculation in the Documents section with what is called ‘Avg. Collection period (Days)@. Now apart from the fact that this is calculated on the ‘after get record’ trigger on the page itself (considered poor practise these days, we cannot call it from elsewhere so have to duplicate code) it only considers closed transactions, so an account with one invoice that’s 365 days overdue would have an average of zero, not really helpful.
That’s a fail for most accountant’s I believe, because it doesn’t help. The objective of this KPI is to identify customers where the average is getting worse – indicating that a total inability to pay might be on the way. Excluding anything not paid doesn’t help, if it is outstanding then the ‘average days’ is getting worse each day that goes by. I will say that if sometimes you exclude invoices which are ‘on hold’ or ‘disputed’, you don’t expect the customer to pay until that resolved.
The second issue I have is that it loops through every customer ledger entry to do the calculation, determining if it fits the rules and if so, what the ‘days to pay’ are. Then it finds the customer ledger entry of the ‘Closed by Entry No.’ to see if it’s a payment rather than a credit note. Microsoft might have considered this process OK for a single account looking at a filtered date range but for the whole of the sales ledger that we need for the management roles centre cue – it just doesn’t perform efficiently enough, actually I don’t like it for a single account either.
So I’ve ignored it for my solution to this. I’m not the MVP with the most pure approach to code design but anything calculating a KPI that that has a comment of ‘Optimized Approximation’ doesn’t fill you with confidence does it!
How to calculate it?
So I’m back to the point I made in my last blog post that you have to have the right data structure to make reporting easy. In this case it’s a customisation (yes another one, sorry) on the Cust. Ledger Entry table that adds a couple of extra fields and an index.
‘Valid for Debtor Days’ field
The first is a Boolean that simply indicates if that record is to be included in the Average Debtor Days calculation. This field is set true if it satisfies the conditions that it’s an invoice transaction that is not completely closed by credits and/or refunds applied. This is updated whenever the ledger entry record is modified or one applied against it.
Record the Original Due Date
Second we need a custom date field called ‘Original Due Date’. This is set to the first Due Date stored on the customer ledger entry – remember the due date can be edited on the entry and we don’t want the credit controller able to make their average overdue debtor days look good by being able to set the due date back. This should be set when the original ledger entry is created.
New ‘Days to Pay’ & ‘Days to Pay Overdue’ flowfields
Thirdly we need to add a ‘Days to Pay’ and a ‘Days to Pay Overdue’ integer to the ledger entry record. This should be calculated and populated when a customer ledger entry record is closed as the number of days between the document date and the date the payment is applied that closes the transaction and the other then number of days between the original due date and that final application date. When the transaction is still open or reopened they should be zero.
Why do we want these? Well it allows us to create two flowfields, that I usually put on a role centre cue table, one which totals all the ‘Days to Pay’ and ‘Days to Pay Overdue’ and another which does a count of the same entries that match the set filters. Make sure that both flowfields include a date filter as well as usually a customer account and even a region code filter in their definition (so you can do analysis by country). Remember you’ll need a index on the cust. ledger entries to match the filters you want to use and with a SumIndexFields (A technical term your developer should understand) on the ‘Days to Pay’ and ‘Days to Pay Overdue’.
You can arrive at a figure for either of the closed transactions on the ledger by setting that date filter for the period you want to calculate for, getting the results from both flowfields and then dividing the total by the count to get the average. This you can display as you need to.
But you said you should include open transactions in the calculation?
Open transactions are different because obviously it’s not a set static number, it literally changes daily; so we have to calculate them separately and the easiest way to do that is use the fact that dates are actually stored as numbers in the database in what’s called Julian notation. Simply explained the system stores dates and the number of days since 1st January 1753!
You can’t build a flowfield on a date field, which is sort of what we need next to create the integer fields on the customer ledger entry as Document Date Int & Original Due Date Int and then make them the integer julian date equal of their date equivalents. This will be the number of days since the 01/01/1753 that these respective dates are.
So to get our average debtor days for open transactions we simply need to do the following:
· Count of all transactions with ‘Valid for Debtor Days’ set from the cust. ledger entry table.
· Multiplied by todays date
· Subtract total of all either Document Dates Int field or Original Due Date Int from the cust. ledger entries
· Divide the result by the count of all transactions with ‘Valid for Debtor Days’ set from the cust. ledger entry table.
Overall Average Debtor Days
To get the overall average between closed and open transactions simply add the result of the first three points above, to the ‘Days to Pay’ or Days to Pay Overdue’ flowfields before dividing by the count of closed transactions added to the count of open transactions. Often you only count open transactions which are overdue, and if the ‘Day to Pay’ is one because the invoice was written yesterday it can lower the average when its unreasonable to expect it to be paid. This is where the ‘Days to pay Overdue’ is a better measure in my view.
Put Average Day for different periods onto a Cue
You can query the whole ledger or one account just as simply and with no significant performance impact. Putting this resulting average onto a Cue is then also simple.
I like to put several for different date ranges so that I can quickly see the trend. Another option is to put different ones for different customer groups, have one average for trade customers to retail customers for instance. Its helps me understand if our performance is getting better or worse.
One point about the Cues – I’ve not figured out what is the most logical drilldown yet, into the ledger entries that make it up maybe, but mostly I leave it without one.
Result per Credit Controller
Some refinements – well sometimes when we have multiple credit controllers we add a field signifying which Credit Controller is responsible for the account to the customer account card. When that is changed it updates identically on all that customer’s open ledger entries, so that we can again use the flowfields to quickly calculate the average debtor days for that credit controller, don’t forget to add it to their role centre then, show them their performance against the company overall average.