# The best little payment performance formula you'll ever need.

All too often, in the internal workings of software packages, the formulas used to calculate a customer's average time of payment are woefully inadequate. This can be true whether the software is home grown or purchased from third-party providers.One common problem is that any particular invoice cleared by a series of payments is often reported as having been paid entirely on the date of the last payment. For example, if 90% of an invoice is paid in 30 days and the remainder in 60 days, the resultant reported time of payment in some systems would be 60 days, based on the last payment date. If the terms of sale were 30 days, that customer would be reported as 30 days late. Another common inaccuracy is that using the payment application date rather than the payment receipt date could negatively impact the reported timing of payment.

Over the years, there has been an obvious need for a formula that would produce the true dollar weighted average of a customer's payments, regardless of how and when they were received and applied. But let's stop for a minute and clearly define what we are actually trying to formulate here. We are not looking for a customer's DSO, which would include unpaid as well as paid invoices. Rather, we are seeking a set of formulas that would accomplish the following:

* Step One: Compute the dollar weighted average days to pay (or days late/early) for a given set of invoices and payments applied together on a given date in a single application. If applicable, any credit and debit memos and journal entries closed by this application would be included.

* Step Two: Do this for all the customer's payments over the period measured.

* Step Three: Aggregate these averages to determine the dollar weighted average for all of the customer's payments over a given time period, i.e., year-to-date.

* Step Four: Do this for all customers to obtain each customer's average days to pay or average days late/early.

Figure 1 FORMULA (AVERAGE DAYS LATE) ADL = [summation] ($ Amount of Item* Closed with the Application) x (Settlement Date - Due Date)/[summation]($ Value of all Invoices and Debits Closed with the Application) * Credit memos and payments should be treated as "minuses," invoices and debits should be treated as "plusses."

Formulas to Accomplish the Four Steps

For Step One, if calculating average days late, use the formula in Figure 1.

In other words, we are going to take the dollar amount of each item closed with the application and multiply it by the number of days from settlement date to due date. (Credit memos and payments should be treated as "minuses" invoices and debits should be treated as "plusses") Then we are going to take the sum of all the individual application amounts (as calculated above) and divide this by the sum of the dollar value of all invoices and debits closed with the application.

Figure 4 Cumulative ADL = [summation][{$ Value of Invoices & Debits Closed in a Given Application} x {Weighted ADL for That Application}]/ [summation][$ Value of All Invoices & Debits Closed] Note: A debit memo is considered an invoice.

Let's apply this formula to the single application in Figure 2.

$1,263,987.67/$24,000.00 = 53 Days Late

In this situation, we had two invoices that were paid with three "on account" payments, then a journal entry was issued to clear the remaining balance. You will notice also that the "settlement date" (the date the payment was applied) was five days after the final payment was received and two days after the journal entry. Yet our formula still yielded an answer that is intuitively correct, 53 days late, and is in fact mathematically correct.

The idea of our formula is, of course, to obtain the dollar weighted days caused by each "plus" tug created by the application (invoices, debit memos), and each "minus" tug (credit memos, payments). The image in Figure 3 presents the idea visually, with the sizes of the circles representing the amount of the item involved, and the colors representing the direction of the tug (red = minus, black = plus), and the length of each arrow being the passage of time involved.

Without filling pages with all imaginable types of examples, let me just say that you can try this yourself with different scenarios, and you can see that it will work universally. To get average time to pay rather than average days late/early, you can just substitute Item Date for Due Date in the numerator of the formula.

Now that we have our formula to establish the days late for individual applications, we can apply the following formula to derive average days late for a customer for a given period of time (Step Three) (see Figure 4).

If we apply this formula to the scenario in Figure 5, we again see an answer that is both intuitively and mathematically correct for a given customer's payments over a defined period of time.

Of course, the larger items, having been paid satisfactorily, work to diminish the negative impact of the smaller invoice paid quite slowly. So, if you are interested in a formula that reliably tells you how a customer has paid his bills, and can adapt this to your software, give it a try and see how it works for you.

"Stump the Formula": If you find any situations where the formula doesn't work, or any other point you would like to discuss, please email me at the address below.

Norman Taylor, CCE is a credit consultant in the Evans, GA area. He can be reached at norml219@comcast.net or 706-854-8263.

"The Best Little Payment Performance Formula You'll Ever Need" or "TBLPPFYEN" is copyrighted by Norman Taylor.

NORMAN TAYLOR, CCE

Figure 2 Item Due "Settlement Scenario Date * Date * Item Amount Date" On Acct Pay 7/13/2000 7/13/2000 $(10,000.00) 7/25/2000 On Acct Pay 7/17/2000 7/17/2000 $ (4,000.00) 7/25/2000 Invoice 4/21/2000 5/21/2000 $ 7,030.73 7/25/2000 Invoice 4/28/2000 5/28/2000 $ 16,518.78 7/25/2000 On Acct Pay 7/25/2000 7/25/2000 $(10,000.00) 7/25/2000 Journal Entry 7/28/2000 7/28/2000 $ 450.49 7/25/2000 $(0.00) Days From Dollar Due Date to Dollar Amount of "Settlement Weighted Invoices/ Scenario Date" Average Debits Closed On Acct Pay 12 $(120,000.00) On Acct Pay 8 $ (32,000.00) Invoice 65 $ 456,997.45 Invoice 58 $ 958,089.24 On Acct Pay 0 -- Journal Entry 2 $ 900.98 $1,263,987.67 $24,000.00 53 "Settlement Date" = Application Date * For credits and journal entries, the date of issuance = Due Date. For payments, the date of receipt = Due Date. Figure 5 A B C Invoice Wtd Days Inv Amts * Item Amounts Late WADL C/A 1 $ 24,000.00 53 $ 1,271,673.54 2 $ 60,000.00 1 $ 60,000.00 3 $ 90,000.00 4 $ 360,000.00 Total $173,549.51 $1,691,673.541 9.7

Printer friendly Cite/link Email Feedback | |

Title Annotation: | SELECTED TOPIC |
---|---|

Author: | Taylor, Norman |

Publication: | Business Credit |

Date: | Jul 1, 2013 |

Words: | 1188 |

Previous Article: | NACM is pleased to announce its first CCRAs! |

Next Article: | Do the right thing: can paying better attention to ethics truly create a better bottom line for credit and business? |

Topics: |