Understanding History Calculations

Both the Receivables Update Application Engine process (AR_UPDATE) and the Aging Application Engine process (AR_AGING) update the system-defined customer history elements.

This topic discusses:

Customer history calculations in the Receivables Update process.
Customer history calculations in the Aging process.

The Receivables Update process updates these system-defined customer history elements:

Average Days Late (AVGDAYS).
Days Sales Outstanding (DSO30 and DSO90).
Highest Balance (HI_BAL_AMT).
Weighted Average Days Late (WTAVGDAYS).
Weighted Average Terms (WTAVGTERMS).
Weighted Average Days Paid (WTAVGPAID).

Average Days Late

Days late is the number of days between the due date and the accounting date of the entry that closed the item. The Receivables Update process calculates average days late as:

sum (days late) ÷ number of items

For example, suppose that a customer has these closed items: a 1,000.00 USD item 2 days late, a 2,000.00 USD item 5 days late, and a 3,000.00 USD item 4 days late.

The average days late for this customer = (2 + 5 + 4) ÷ 3, or 11 ÷ 3, or 3.67

Use the Receivable Update Request page to indicate that you want to update the Average Days Late figures for subcustomer levels (assuming subcustomer is enabled). The system date at the start of the Receivables Update run and the accounting calendar determine the accounting period for the result.

The Receivables Update process considers items only if they have been closed since the last time the history was updated.

It can exclude items for a variety of reasons:

The dispute reason governs exclusion of disputed items.
The deduction reason governs exclusion of deduction items.
The entry type governs exclusion by entry type.
The item was originally negative (such as a credit memo).

For each item that meets the inclusion criteria, the system determines the number of day's difference between the due date and the accounting date of the entry that closed the item. Consider two examples:

Payment in full.
A 1,000.00 AUD invoice is posted with an accounting date of September 1 and a due date of September 30. An 1,000.00 AUD payment with an accounting date of October 1 is posted on October 5, bringing the balance of the invoice to 0 AUD and changing its status from open to closed.

Days late = 1
Partial payment.
A 1,000.00 AUD invoice is posted with an accounting date of September 1 and a due date of September 30. A partial payment of 900.00 AUD with an accounting date of October 1 is posted against the invoice on October 5, bringing the balance of the invoice to 100.00 AUD. A 100.00 AUD credit memo with an accounting date of October 15 closes the invoice.

Days late = 15

Previous activity for the fiscal year and accounting period determine how the Receivables Update process updates history. If a value for Average Days Late does not exist for the fiscal year and accounting period, the Receivables Update process updates the history ID by adding the number of days and dividing by the count of closed items. Here are two examples:

Example one.
Two items are closed for a customer. One item is 10 days late, and the other is 5 days late. Total days late is 15, which when divided by 2 equals 7.5 Average Days Late.
Example two.
Two items are closed for a customer. One is 10 days late, and the other is 5 days early. Total days late is 5, which when divided by 2 equals 2.5 Average Days Late.

If a value for Average Days Late does exist, the system computes a running average. It adds the sum of the days late of the closed items to the product of the existing value and the number of existing closed items, and then divides by the sum of the existing items and the newly closed items. For example, the previous values are 15 days late, 3 items; the recent closed items values are 40 days late, 2 items. The formula is:

[(15 * 3) + 40)] ÷ (3 + 2) = 17 days late, 5 items

Days Sales Outstanding (DSO30 and DSO90)

Days sales outstanding (DSO) is reported as two different history IDs: a 30-day based figure and a 90-day based figure. The system uses the calculation year and period from the Receivables Options - Options 1 page as the basis for calculations to determine the accounting period for the resulting history.

Use the Receivable Update Request page to indicate whether you want to update the DSO and the subcustomer history DSO figures. You should request user-defined history when you request DSO, because DSO calculations use the SALES figures updated as part of user-defined history. If you run DSO alone, the results do not reflect the latest sales figures.

The Receivables Update process calculates DSO30 by multiplying the customer's current balance by 30 and dividing by prior period sales. The prior period is the DSO calculation period on the Receivables Definitions - Accounting Options 1 page, even if it crosses a fiscal year.

The Receivables Update process calculates DSO90 by multiplying the current customer balance by 90 and dividing by the sum of sales for the previous three periods. Use a view to sum sales for the three periods before the calculation period, even if a fiscal year boundary is crossed.

Highest Balance (HI_BAL_AMT)

This is the highest balance for the customer for the accounting period. The system uses the system date at the time you run the Receivables Update process, as well as your accounting calendar, to determine the accounting period. If no activity occurs during an accounting period, the system does not create a history record for that period.

Weighted Average Days Late (WTAVGDAYS)

Use the Receivable Update Request page to indicate whether you want to update Weighted Average Days and Weighted Average Days Late figures for subcustomer levels (assuming that subcustomer levels are enabled). The system date at the start of the Receivables Update run and the accounting calendar determine the accounting period for the result.

The amount of the closed item is used to weight the days based on the assumption that a 100,000.00 EUR invoice paid 10 days late is more serious than a 10.00 EUR invoice paid 10 days late. The formula is:

sum (item amount * days late) ÷ sum (item amount)

The item amount is drawn from the first instance of item activity that has the same entry type as the closed item. Days late is the number of days between the due date and the accounting date of the item activity that closed the item.

For example, suppose that a customer has the following closed items: a 1,000.00 USD item 2 days late, a 2,000.00 EUR item 5 days late, and a 3,000.00 EUR item 4 days late. For this customer, WTAVGDAYS equals:

(1,000.00 * 2) + (2,000.00 * 5) + (3,000.00 * 4)] ÷ (1,000.00 + 2,000.00 + 3,000.00) = 24,000.00 ÷ 6,000.00 or 4

The Receivables Update process updates history for Weighted Average Days Late based on the previous activity for the fiscal year and accounting period. If a Weighted Average Days Late value does not already exist for this fiscal year and accounting period, the history is updated. If a Weighted Average Days Late value exists, a running average is computed, using additional information stored by customer history and subcustomer history, if appropriate, similar to the example for Average Days Late. The system date at the start of the Receivables Update run and the accounting calendar determines the accounting period for the result.

When an item is created, the Item Activity line contains an entry type. This entry type is stored with the item, unless a subsequent Item Activity line has an entry type with the Prevent Posting of Duplicate Entries option selected. In this case, the entry type stored with the item changes to the entry type of the subsequent entry.

The Prevent Posting of Duplicate Entries option is used when a debit or credit memo is posted before the invoice. Because the system uses the entry type on the item for aging redirection and correspondence inclusion, you can use the invoice entry type as the controlling entry type even though the invoice is not posted first. This is important because Weighted Average Days Late uses the amount associated with the controlling entry type rather than the original amount of the item.

Weighted Average Terms (WTAVGTERMS)

Weighted Average Terms calculates the average number of days allowed for a customer before payment is due, weighted according to the item amount. By default, the calculation is based on the accounting date unless the terms code on the item specifies a different basis date.

Customer payment terms impose limits on the allowable days, and a customer can buy with multiple payment terms. Some invoices can be due in 20 days, others in 30 or 40 days. For example, the Weighted Average Days Late calculation informs you that the customer pays, on average, 5 days late. However, that number is more meaningful when you know that the customer had an average of 25 days to make payments.

The Receivables Update process performs the calculations only if you select Payment Performance on the Receivable Update Request page. Settings on the Receivable Update Request page also determine whether the subcustomer history module runs (assuming it is enabled). The accounting period for the result is determined by the Receivables Update run date and your accounting calendar.

Weighted Average Days Paid (WTAVGPAID)

Weighted Average Days Paid is the number of days a customer takes to make payments. The average is weighted by the payment amount, on the assumption that a 1,000.00 USD payment is more significant than a 100.00 USD payment. Weighted Average Days Paid is calculated by adding weighted average terms and Weighted Average Days Late:

days allowed + days late = days taken

For example, weighted average terms of 25, plus Weighted Average Days Late of 5, means that the customer pays an average of 30 days from the invoice date—25 days were allowed, and 5 extra were taken.

The Receivables Update process performs the calculations only if you select Payment Performance on the Receivable Update Request page. Settings on the Receivable Update Request page determine whether the subcustomer history module runs (assuming it is enabled). The accounting period for the result is determined by the Receivables Update run date and your accounting calendar.

This section discusses the due and high-due formulas for each history calculation performed during aging.

The Due Family

The Aging process updates these history IDs:

Current Due
Future Due
Past Due
Other Due

The amounts are based on how you defined an aging ID (the sum category that you selected for each aging category). The history calculations are also affected by any special handling that was defined for entry types. The system date for starting the Aging run determines the fiscal year and accounting period.

The High-Due Family