38 Determine Average Days Late

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

38.1 Calculations

The Recalculate Average Days Late program (P03830) uses the following steps in its calculations. Field descriptions and names are noted in parentheses.

  1. 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).

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

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

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

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

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


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.

Spread Example:

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

38.1.1 DREAM Writer Considerations

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.

38.1.2 Guidelines

Adjustments, write-offs and open unapplied cash receipts on a customer's account are not included as part of the "average days to pay" calculation.