This chapter contains the topic:
Recalculate Average Days Late (P03830) calculates the average days to pay for each customer based on the A/R Ledger file (F0311) payment history for that customer. The average days late is maintained in the Customer Master file (F0301) and is available for review through Account Status Summary (P03203).
The average days are based on the invoice net due date rather than the invoice date. Therefore it is possible to have plus and minus average days to pay. A customer who pays ahead of schedule would have negative average days to pay. A customer who always pays exactly on time would have zero average days to pay. A customer whose account is delinquent would have a positive number of days to pay.
From Accounts Receivable (G03), choose Credit/Collection Management
From Credit & Collection Management (G0315), choose Recalculate Average Days Late
The Recalculate Average Days Late program (P03830) uses the following steps in its calculations. Field descriptions and names are noted in parentheses.
Using the required data selection, the system retrieves invoices that are paid or partially paid. This excludes adjustment records (TRTC = A or W) and the original unapplied cash document (DCT = RU).
The software compares the invoice Net Due Date (DDJ) to General Ledger Date (DGJ) to determine the number of days late for each invoice, except in the case of a spread transaction (TRTC = S). If unapplied cash payments are spread to an invoice, those records (document type RS) will trace back to their original Unapplied Cash receipt record (document type RU/RC), and use the G/L date of the RU/RC record to calculate the number of days to pay.
After calculating the number of days to pay for each invoice, the program multiplies the number of days to pay by the amount on each receipt (AG) to determine the weighted days late for that receipt.
The program sums the receipt amounts and the weighted days for each customer, and then divides the weighted days by the customer's total receipts to determine weighted average days late.
The average days to pay for the parent is calculated by accumulating the TOTAL weighted days for all children, including the parent, and dividing by the TOTAL RC dollars received for all children, including the parent and placing the value in the average days to pay parent field. This value can be viewed via the Account Status Summary screen (P03203) by inquiring on the parent.
The program updates the number calculated in Step 4 to the Average Days Late field (AVD) in the Customer Master table (F0301) and can be viewed in Account Status Summary (P03203).
| Invoice/Receipt Number | Amount | Due or G/L Date | Days Late | Calculation |
|---|---|---|---|---|
| Invoice 1 | $1000 | 7/01/2017 | ||
| Receipt 1 | 1000 | 7/25/2017 | 24 | 24 x 1000 = 24,000 weighted |
| Invoice 2 | $20 | 8/31/2017 | ||
| Receipt 2 | 15 | 1/01/2018 | 123 | 123 x 15 (partial payment) = 1845 weighted |
24,000 + 1845 = 25,845 total weighted days
1000 + 15 = $1,015 total receipts
Weighted Average Days Late: 25,845 / 1,015 = 25
Note:
RU documents are not included until they are spread. After they have been spread, the program uses the G/L date of the RU/RC document as the receipt date.If the Recalculate Average Days to Pay program has data selection limiting invoices by due date, only invoices that meet those criteria in the data selection and their corresponding RC check records will be used to calculate the average days to pay.
| Invoice/Receipt Number | Amount | Due or G/L Date | Days Late | Calculation |
|---|---|---|---|---|
| Invoice 3 | $100 | 6/01/2017 | ||
| Receipt 3 (entered as RU) | 100 | 6/30/2017 | ||
| Spread between Invoice 3 and Receipt 3 | 100 | 7/31/2017 | 29 | 29 x 100 = 2900 weighted |
| ( RU/RC G/L date is retrieved) |
Total weighted days: 2900
Total receipts: $100
Weighted Average Days Late: 2900/100 = 29
The DREAM Writer data selection can be used to specify the amount of invoice history you want to include in the average days to pay computation.
A mandatory selection is set up to include only those invoices where the open amount does not equal the gross amount; that is, only invoices that have either been paid or partially paid.
If invoice due date is set to *ALL in the DREAM Writer, average days to pay is calculated on inception to date account detail unless the detail has been purged previously.