Updating Accounts Receivable Statistics for Customer Analysis

This chapter provides an overview of Accounts Receivable statistics and discusses how to:

Update statistical information.
Analyze statistical information.
Process the statistics annual close.
Purge statistical history records.

Understanding Accounts Receivable Statistics

When you want to review account activity over several fiscal periods, as well as statistics such as delinquent days sales outstanding (DSO), weighted average days late, average invoice amount, percent of invoices paid late, and so forth, you can run the Statistics History Update program (R03B16A). In addition to providing statistical information about the performance of collections over a period of time, the system enables you to review multiple customer accounts simultaneously for comparison purposes.

Minor Write-off and Bad Debt Amounts

When you run Statistics History Update, the system accumulates amounts for the Minor Write-Off (MWO) and Bad Debt (BDBT) fields in the A/R Statistical History (F03B16) and A/R Statistical Summary (F03B16S) tables. The system determines the write-off amount with which to update each of these fields based on the value of the Special Handling field in user-defined code (UDC) 03B/RC:

If the value in the Special Handling field of the UDC table is 1, the system accumulates the write-off amount for the Minor Write-Off field.
If the Special Handling field is blank, the system accumulates the write-off amount for the Bad Debt field.

You specify the reason codes to associate with each field by updating the Special Handling field in the UDC table.

See Setting Up UDCs for Accounts Receivable.

See JD Edwards EnterpriseOne Tools 8.98: Foundation Guide

Updating Statistical Information

This section provides overviews of the Statistics History Update program, periodic statistical calculations, and aging information on periodic statistics, lists prerequisites, and discusses how to:

Run the Statistics History Update program.
Set processing options for Statistics History Update (R03B16A).

Understanding the Statistics History Update Program

As part of the customer analysis activities, you can update statistical information about customer accounts by running the Statistics History Update program. This enables you to review the activity of a customer's account for a specific period, which is helpful for trend analysis and cash forecasting. The update includes information about both closed and open transactions, such as:

Invoice amount
DSO

When you run this batch process, the system includes posted information only. The system also updates inception-to-date statistics for the customer accounts. This information is similar to the periodic statistics except that the system accumulates statistical information from the time the customer's account is opened. It includes:

The date of the first and last invoice.
The last receipt date.

Running this batch process is time consuming. You should run this process during off-peak hours.

When updating statistical information, the system gathers information from these tables:

Customer Ledger (F03B11)
Invoice Revisions (F03B112)
Receipts Header (F03B13)
Receipts Detail (F03B14)

After the system gathers the information, it updates the F03B16 and F03B16S tables. The F03B16S records are a summary of a subset of F03B16 records. You define the subset by using processing options. The system stores summary information at the parent/child level. The parent record contains information that pertains to the account and all associated child accounts.

The system also updates these fields in the Customer Master by Line of Business table (F03012):

Invoiced This Year (ASTY)

The system updates this field using the gross amount (AG) of the invoice record in the F03B11 table, regardless of whether the invoice has applicable taxes.
Invoiced - Prior Year (SPYE)
YTD Finance Charges (AFCY)
Last Applied Amount (ALP)
Date of First Invoice (DFIJ)
Last Invoice Date (DLIJ)
Date Last Paid (DLP)

After invoice and receipt information is included in the F03B16 table, the system updates the processed records with 1 in the A/R Post to Cash Management (ISTC) field, which prevents the records from being processed a second time. If you do not want to include all posted transaction information in the statistical tables, you must update the ISTC field with a value of 1, using a data file utility. The ISTC field is located in the F03B11, F03B112, F03B13, and F03B14 tables.

The system does not process R5 deduction records from the F03B14 table or RU (unapplied receipt) and R1 (draft) records from the F03B11 table.

To run the Statistics History Update program successfully:

Set up credit and collection date patterns for company 00000 for all fiscal years for which you have invoices and receipts entered on the system.
Post all invoices and receipts that you want to include.
Do not change the data selection or data sequence of the program.
With the exception of calculating the high balance, do not change the processing options after you run the Statistics History Update program.

Understanding Periodic Statistical Calculations

As part of the customer analysis activities, you can update statistical information about the customer accounts, based on how you set the processing options. To do so, run the Statistics History Update program. This enables you to review the activity of a customer's account for a specific period, which is helpful for trending analysis and cash forecasting.

This sections provide quick references to setup considerations, troubleshooting tips, and calculations that the system performs.

Setup Considerations

Note these setup considerations:

Credit and collection fiscal date patterns need to be set up for at least company 00000, which will encompass the GL date of all invoices and receipts in the database.
The Statistics History Update program should not be run with any user-defined data selection.
A record in the F03B16S table is a summary of a subset of records in the F03B16 table.

That subset is defined by R03B16A processing options 5 and 6.

Troubleshooting Tips

Note these troubleshooting tips:

In the parent/child relationship between customers, the parent cannot be the child of its child.

This causes a loop error.
The Statistics History Update program includes only records that match one of these selection criteria:

F03B11: select where RPPOST = D and RPISTC ! = 1 and RPDCT ! = RU and RPAN8 ! = 0

F03B112: select where RWPOST = D and RWISTC ! = 1 and RWAN8 ! = 0

F03B13: select where RYNFVD = N and RYISTC ! = 1 and RYAN8 ! = 0

F03B14: select where RZPOST = D and RZISTC ! = 1 and RZAN8 ! = 0
The system creates F03B11 R5 records with ISTC = 1 so that they are not included in the Statistics History Update program.

Calculations

You can review statistical (periodic) calculations on the Periodic Statistics form from the Credit Collections Management menu (G03B15). This table describes how the system calculates this information for customers:

Page Element	Calculation Information
Bad Debt	If the Write-off Reason Code field is populated and the Special Handling Code field in the 03B/RC UDC table is blank, the system adds write-off amounts to the Bad Debt total. Otherwise, the amount is accumulated into the Minor Write-Off total.
Minor Write-off	If the value in the Special Handling Code field is equal to 1 in the O3B/RC UDC table, the system adds write-off amounts to this total.
Bad Debt Ratio	The system divides bad debt amount by the total sales for the period. Calculation: Bad debt ÷ total sales for period.
Total Write-off	This field is the sum of bad debt and minor write-off amounts. Calculation: Bad debt + minor write-offs.
Average Days Late (Non-Weighted)	The system uses these calculations: Total number of days = Difference between the Invoice Due Date and the Receipt Date. The system calculates the number of days between the receipt date and the invoice due date, and adds this calculation to the Total Number of Days. Average Number of Days = Total Number of Days ÷ Number of Invoices Paid. The system calculates the average number of days by dividing the total number of days by the number of invoices paid. The system includes only the receipts that close or the pay-off invoices in the number of invoices paid. For example, suppose a receipt for 100,000 is one day late. Another receipt for 500 is 30 days late. Each receipt pays one invoice. If you use a nonweighted average days late calculation, the average days late is 15.5 days: Number of invoices ÷ Days late = Nonweighted average days late (2 ÷ 31 = 15.5)
Weighted Average Days Late	The system weighs the average days late calculation by determining the current percentage of all receipt amounts. Larger receipt amounts have a greater effect on the average days late. The system uses these calculations: Number of Days = Difference between the Invoice Due Date and the Receipt Date. Previous Receipt Amount = Amount of the last payment received. Number of Days Current Receipt = Receipt Amount ÷ (Receipt Amount + Previous Receipt Amount). For example, assume that a receipt for 100,000 is one day late, and another receipt for 500 is 30 days late. Each receipt pays one invoice. If you use a weighted average days late calculation, the average days late is 1.1 days. Previous Receipt Amount ÷ (Receipt Amount + Previous Receipt amount)) + Number of Days Current Receipt (receipt amount ÷ (receipt amount + previous receipt amount)) 100,000 ÷ (500 + 100,000)) + 30 (500 ÷ (500 + 100,000)) = 1.1. F03B16 and F03B16S Level Calculations for the Weighted Days Late: Previous Average Days Late (previous receipt amount ÷ (receipt amount + previous receipt amount total)) = Current Average Days Late (current receipt amount ÷ (current receipt amount total + previous receipt amount)).
DSO	The system stores the DSO amount at the periodic level in the F03B16. You can use these methods to calculate DSO: Using the countback method, if the current outstanding balance is less than the total sales, the DSO = (current balance ÷ total sales) × number of days in the period. Otherwise: Subtract the current period's total sales amount from the current balance and increment DSO with the number of days in the period. Subtract from the remaining balance the total sales of the first preceding period. This will continue until the outstanding balance of any preceding period exceeds the remaining balance. If the remaining balance is less than the preceding period, then DSO = Accumulated DSO + ((current balance ÷ total sales) × number of days in period). The Current Balance Divided by Sales is calculated like this: DSO = (current A/R balance × number of days in n periods) ÷ Total invoiced over n number of periods n = number of periods that are specified in the processing options The Average Balance Divided by Sales is calculated like this: DSO = ((total period end balance over n number of periods) × average number of days) ÷ Total sales amount for n periods Where: Sales Amount is the taxable amount of the invoice. If you leave this field blank, the system uses the gross amount. Period End is the open A/R amount at the end of the period. Number of Days is the number of days for the period. This value is defined in the Credit and Collections Date Pattern table (F03B08). n = number of periods specified in the processing options For example: For January, the Sales Amount is 7,570.00, the Period-End Balance is 10,825.00, and the Period Days are 31. For February, the Sales Amount is 4566.00, the Period-End Balance is 10,596.00, and the Period Days are 28. For March, the Sales Amount is 5,538.00, the Period-End Balance is 10,869.00, and the Period Days are 31. DSO = (10,869 + 10,569 + 10,825) × 30 ÷ (5,538 + 4,566 + 7,570) = 54.81 for March. Note. The processing option settings for the sample calculation include 1. DSO Calculation Method = 1 (Avg. Balance) 2. Number of Periods = 3 3. Number of Days for Summary = Inception to Date

Examples of DSO

This table shows amounts that are used in these calculations of DSO:

Period	Sales Amount	Period End Balance	Period Days
January	7570	10825	31
February	4566	10596	28
March	5538	10869	31

Countback Method

This table shows the calculation of DSO using the countback method:

March	10869 − 5538 = 5331	Accumulated DSO = 31
February	5331 − 4566 = 765	Accumulate DSO = 31 + 28 = 59
January	(765 ÷ 7570) × 31 = 3.1
		DSO = 59 + 3.1 = 62.1

Average Balance Divided by Sales

This example shows the calculation of DSO using the average balance divided by sales method:

((10869 + 10596 + 10825) × 30) ÷ (5538 + 4566 + 7570)

DSO = 54.81

Current Balance Divided by Sales

This example shows the calculation of DSO using the current balance divided by sales method:

(10869 × 90) ÷ (5538 + 4566 + 7570)

DSO = 55.35

This table describes how the system calculates additional amounts:

Page Element	Calculation Information
Days Credit Granted	The system uses the same calculations as DSO to calculate the days credit-granted amount. The exception is that the system replaces the current period-end balance with the amount of the balance that is due or due in the future. Calculation: open amount − past due amount
Delinquent DSO	This is the number of days that a customer takes beyond the Days Credit Granted. Calculation: DSO − Days Credit Granted
Extended DSO	This is the number of days beyond the assigned payment terms for a customer. Calculation: Days Credit Granted − Payment Terms Result: The amount of credit that the sales staff grants customers beyond the payment terms.
Invoice Amount	The gross amount of the invoice.
Sales Amount	The taxable amount of the invoice.
% of Amount Paid Late	Calculation: Invoice Amount ÷ Amount Paid Late

Technical Considerations

When you run the Statistics History Update program, the system creates a minimum of four records in the F03B16S table for each customer. For example, if you enter one invoice for customer 1234 for company 00001, the system creates these records in the table:

One record for the child information (RSPART = C) for the company that the invoice was entered against (RTCO = 00001).
One record for the child information (RSPART = C) for company 00000.

The system uses the record for company 00000 to total the information for all company records.
One record for the parent information (RSPART = P) for the company that the invoice was entered against (RSCO = 00001).

Even if you do not use parent/child relationships, the system still generates a parent record.
One record for the parent information (RSPART = P) for company 00000 (RSCO = 00000).

The system uses the record for company 00000 to total the information for all company records.

If you had invoices entered for multiple companies, the system would generate additional records, one record for each company for both the child and the parent.

Understanding Aging Information on Periodic Statistics

The system obtains aging information that is displayed on the Periodic Statistics form differently from the way aging information is displayed on other forms, such as Account Balance Inquiry and Account Status Summary.

When you run the Statistics History Update program, the system uses this criteria for aging invoices and displays the aging information for each period:

Criterion	Description
Aging Method	The system ages by fiscal periods that you define in the credit and collection date pattern.
Aging Date	The system uses the last day of each fiscal period that you define in the credit and collection date pattern.
Date Aging Based On	The system uses the value from the AR constants (due date, invoice date, GL date, or statement date).

Example of Aging

This information applies to this example:

Credit and Collection Date Pattern: Defined for a calendar year.
Date Aging Based On: Invoice due date.
Invoice that is entered for 2000 with a due date of June 15, 2008.

When you run the Statistical History Update program on or after June 15, 2008, the amount of the invoice (2000) appears in the first aging category for the period ending June 30, 2008.

If the invoice is outstanding when you run the Statistical History Update program in July, the system displays 2000 in the first aging category for both the period ending June 30, 2008 and the period ending July 31, 2008. If the invoice is outstanding when you run the Statistical History Update program in August, the system displays:

2000 in the third aging category for the period that ends August 31, 2008.
2000 in the second aging category for the period that ends July 31, 2008.
2000 in the first aging category for the period that ends June 30, 2008.

This table illustrates how the system displays aging information on the Periodic Statistics form after running the Statistical History Update program on June 15, 2008:

Period End Date	Aging Amount 1	Aging Amount 2	Aging Amount 3	Aging Amount 4
June 30, 2008	2000

This table illustrates how the system displays aging information on the Periodic Statistics form after running the Statistical History Update program on July 15, 2008:

Period End Date	Aging Amount 1	Aging Amount 2	Aging Amount 3	Aging Amount 4
July 31, 2008		2000
June 30, 2008	2000

This table illustrates how the system displays aging information on the Periodic Statistics form after running the Statistical History Update program on August 15, 2008:

Period End Date	Aging Amount 1	Aging Amount 2	Aging Amount 3	Aging Amount 4
August 31, 2008			2000
July 31, 2008		2000
June 30, 2008	2000

This table illustrates how the system displays aging information on the Periodic Statistics form after running the Statistical History Update program on September 15, 2008:

Period End Date	Aging Amount 1	Aging Amount 2	Aging Amount 3	Aging Amount 4
September 30, 2008				2000
August 31, 2008			2000
July 31, 2008		2000
June 30, 2008	2000

Prerequisites

Before you complete the tasks in this section:

Set up credit and collection date patterns for company 00000.
Post all invoices and receipts.
Verify that the UDC values that you want to use to accumulate amounts for bad debt and minor write-offs are specified.
If you are converting information from another system, you might want to run the Statistics Annual Close program (R03B16A1) to update the invoiced prior year amount.

Running the Statistics History Update Program

From the Credit/Collections Management menu (G03B15), select Statistics History Update.

Setting Processing Options for Statistics History Update (R03B16A)

Processing options enable you to specify the default processing for programs and reports.

DSO

These processing options determine how the system calculates days sales outstanding.

1. DSO Calculation Method

Specify how you want the system to calculate the value in the Days Sales Outstanding field (DSO). Values are:

Blank: Countback Method

1: Average Balance

2: Current Balance

2. Number of Periods

Enter the number of fiscal periods, based on the credit and collection date pattern, that you want the system to use to calculate the value in the Days Sales Outstanding field (DSO).

Calculations

These processing options determine whether the system calculates amounts for days sales outstanding and the high balance.

1. Calculate DSO

Use this processing option to calculate the value in the Days Sales Outstanding field (DSO). To calculate this value, the system retrieves the values that are entered in the DSO Calculation Method and Number of Periods processing options. Because the system performs complex calculations to arrive at DSO figures, you might select to calculate DSO periodically, instead of nightly, to reduce the overall processing time. Values are:

Blank: Do not calculate DSO.

1: Calculate DSO based on the values that are entered in the processing options on the DSO tab.

2. Calculate High Balance

Use this processing option to calculate the high balance amount. The system runs the Calculate High Balance program (R03B162) when you select this option. If you do not need to calculate the high balance each time that you update statistics, you can save processing time by setting this option to blank and then activating it only when necessary. Values are:

Blank: Do not calculate the high balance.

1: Calculate the high balance. The system calculates the high balance by using the beginning balance (the ending balance of the last period) and adding invoices and subtracting receipts for each day between the last day of the ending balance and the day that you run the program. If the ending balance for a day is higher than the previous day's ending balance, then it becomes the new high balance amount.

Summary Record

These processing options determine which records the system selects for updating the F03B16S table.

1. Number of Days for Summary

Use this processing option in conjunction with the Thru Date processing option to specify the subset of detail records from the F03B16 table that you want the system to summarize into records to include in the F03B16S table.

For example, if you enter 365 in the Number of Days for Summary processing option and enter a thru date of September 30, 2006, the system updates the F03B16S table with periodic records from the F036B16 table that have dates between October 01, 2005 and September 30, 2006.

If you do not specify a number of days, the system selects all records that have a date that is less than the thru date that you enter (inception-to-date). For fields that require calculations, processing time greatly increases. If you do not need to maintain inception-to-date information, update this processing option with a value to reduce the overall processing time of the program. You must enter at least the number of days in one period, as defined in the credit and collection date pattern, for the system to update the summary records with amounts. Otherwise, the system updates summary records with date values only.

The system updates the F03B16S table only for completed periods as defined by the credit and collection date pattern. For example, if the credit and collection date pattern is set up for a regular calendar year and you enter a date of April 15 in the Thru Date field, the system updates the F03B16S table with information only through period 3 (March). If you want the system to update the information in this table daily, set the through date ahead to the beginning of the next period or through the end of the fiscal year.

2. Thru Date

Use this processing option in conjunction with the Number of Days for Summary processing option to specify the subset of detail records from the F03B16 table that you want the system to summarize into records to include in the F03B16S table.

For example, if you specify 365 in the Number of Days for Summary processing option and enter a thru date of September 30, 2006, the system updates the F03B16S table with periodic records from the F03B16 table that have dates between October 01, 2005 and September 30, 2006.

If you do not specify a number of days, the system selects all records that have a date that is prior to the thru date that you enter (inception-to-date). For fields that require calculations, processing time greatly increases. If you do not need to maintain inception-to-date information, update this processing option with a value to reduce the overall processing time of the program. You must enter at least the number of days in one period, as defined in the credit and collection date pattern, for the system to update the summary records with amounts. Otherwise, the system updates summary records with date values only.

The system updates the F03B16S table only for completed periods, as defined by the credit and collection date pattern. For example, if the credit and collection date pattern is set up for a regular calendar year and you enter a date of April 15 in this processing option, the system updates the F03B16S table with information only through period 3 (March). If you want the system to update the information in this table daily, set the through date ahead to the beginning of the next period or through the end of the fiscal year.

Analyzing Statistical Information

This section provides an overview of statistical information, lists a prerequisite, and discusses how to:

Review statistical information.
Review periodic statistics for an account.

Understanding Statistical Information

As part of the credit and collection activities, you can analyze the payment trends of a customer within a specific time period. For example, you can review the average number of days that a customer is late in paying an invoice or review the total amount that is invoiced for a customer. In addition, you can review these types of statistical history:

Date that the account was opened.
Amount and number of invoices that were paid late.
Amount and date of a customer's high balance.
DSO.

You can review statistical information by fiscal period, as defined in the credit and collection date pattern, or that is summarized based on the processing options that you set when you run the Statistics History Update program.

The system displays statistical information for each customer by company and provides a total for all companies in the company 00000 record.

The system stores summarized statistical information in the F03B16S table and period statistical information in the F03B16 table.

Note. The system does not update information from a previous period to the next period until at least one invoice is posted to that period. For example, if an invoice appears in an aging category for the period ending June 30, 2008, it will not appear in the aging category for July 31, 2008, even if the invoice is still outstanding, until at least one invoice is posted to the July period.

Prerequisite

Run the Statistics History Update program to update the F03B16 and F03B16S tables.

Forms Used to Analyze Statistical Information

Form Name	FormID	Navigation	Usage
Account Statistical Summary	W03B16E	Credit/Collections Management (G03B15), Account Statistic History Inquiry. Customer Invoice Entry (G03B11), Account Statistic History Inquiry.	Review statistical information, such as the average number of days that a customer is late in paying an invoice, or review the total amount that is invoiced for a customer.
Periodic Statistics	W03B16F	Select an account, and then click Select on the Account Statistical Summary form.	On the Periodic Statistics form, the system displays each period on a separate line. The system displays the totals on the second to last line. The last line in bold, black text represents the average for the column. This average includes those periods that have no activity.

Reviewing Statistical Information

Access the Account Statistical Summary form.

To review information for parent records only, select the Parent option, and then click Find.

Reviewing Periodic Statistics for an Account

Access the Periodic Statistics form.

Processing the Statistics Annual Close

This section provides an overview of the statistics annual close, lists a prerequisite, and discusses how to:

Run the Statistics Annual Close program.
Set processing options for Statistics Annual Close (R03B161).

Understanding the Statistics Annual Close

You run the Statistics Annual Close program when:

The fiscal year that you define in the credit and collection date pattern is complete.
You have converted invoice information from another system and you want to establish a value for the Invoiced - Prior Year field (SPYE).

When you run Statistics Annual Close program, the system:

Recalculates the invoiced year-to-date amount and updates the Invoiced - Prior Year field (SPYE) with the amount.

The system uses the value that you enter in the processing option for the fiscal year to determine the amount invoiced for the year.
Clears the amount of the Invoiced This Year field (ASTY).
Changes the designation of the current year in the Credit/Collection Date Pattern table.

The fiscal date patterns that are designated for the current year are changed to a value of 2 for the prior year. The date patterns that are established for the next fiscal year are set to the current year value of 1.

You can review the Invoiced This Year and Invoiced - Prior Year fields on these forms:

Account Statistical Summary form, which you access from the Account Statistic History program.
Additional Customer Information form, which you access from the Customer Master Information program.

Prerequisite

Verify that a credit and collection date pattern is set up for the companies for the next fiscal year. You must have a date pattern set up for company 00000.

Running the Statistics Annual Close Program

From the Period End Processing menu (G03B21), select Statistics Annual Close.

Setting Processing Options for Statistics Annual Close (R03B161)

Processing options enable you to specify the default processing for programs and reports.

Year

This processing option determines the records that the system selects to purge.

1. Fiscal Year

Enter the fiscal year, according to the credit collection date pattern that is established, that you want to close. The system recalculates the invoiced year-to-date amount for the fiscal year that you enter and updates the Invoiced - Prior Year field (SPYE) with the value.

Purging Statistical History Records

This section provides an overview of the purge of statistical history and discusses how to:

Run the Statistical Purge Repost program.
Set processing options for Statistical Purge Repost (R03B163).

Understanding the Purge of Statistical History

When you run the Statistical Purge Repost program, the system:

Removes records from the F03B16 table based on the data selection.

Important! Because the F03B16 table comprises company-specific totals and totals for all companies (company 00000), do not use Company in the data selection.
Resets the value of the A/R Post to Cash Management field (ISTC) from 1 to 0 in the F03B11 invoice records and the F03B14 receipt records. Because changes to invoices are reflected in the F03B11 table, the system does not reset the value of the ISTC field in records in the F03B112 table.

You purge F03B16 records when you want to start over, perhaps due to a change in the processing options. After the system removes the information from the F03B16 table and resets the status of the ISTC field, the program automatically reruns the version of the Statistics History Update program (R03B16A) if specified in the processing options. Therefore, if you specify a version in the processing options of the Statistical Purge Repost program, you must have the processing options set correctly for the Statistics History Update program before you run the purge and repost. The system recalculates information in the F03B16S table based on the new values that it generates for the F03B16 table.

Running the Statistical Purge Repost Program

From the A/R Advanced & Technical Operations menu (G03B31), select Statistical Purge Repost

Setting Processing Options for Statistical Purge Repost (R03B163)

Processing options enable you to specify the default processing for programs and reports.

Options

This processing option determines the version of the Statistics History Update program that the system runs to re-create the F03B16S table when the purge completes.

1. Version for R03B16A

Specify the version of the Statistics History Update program to run after the purge or repost process is complete. If you do not specify a version, the system does not run the Statistics History Update program.

Dates

These processing options determine which records the system selects to purge from the F03B16 table.

1. From Date and 2. Thru Date

Use these processing options to specify the date range in which records are purged from the F03B16 table.