Defining data > Aging data

Aging data

Aging groups records by an aging period and counts the rows in each period. The aging period relies on a date column in the data table, and you can subtotal numeric columns for each aging period.

How it works

Sorting

You do not need to sort data before aging.

Order of operations

When you age data, Add-In for Excel:

  1. groups rows into aging periods based on the values in a date column
  2. subtotals the number of rows for each period and then calculates the percentage of the total count represented by each group
  3. for each numeric subtotal column included in the results, subtotals each unique value and calculates the percentage of the total value represented by each subtotal

Example

You are working with an accounts receivable table and you need to find the total number of transactions for each aging period. To find this, you age the data using the due date field and subtotal the transaction amount column.

In your results, you see the total number of transactions per aging period and the total transaction amount per period.

Outputting results to Pivot reports

If you output the results of aging to a PivotTable or PivotChart, you can further group the aging periods by the values in additional columns.

Example

You are working with an accounts receivable table and you need to find the total number of transactions per customer for each aging period. To find this, you age the data using the due date column and group the periods by customer ID. You then subtotal the transaction amount column.

In your results, you see the total number of transactions per aging period, grouped by customer, and the total transaction amount per period and per customer each period.

Periods and cutoff dates

Aged data is grouped using two elements:

Tip

Instead of using the default aging periods, you can specify your own set of periods. User-specified aging periods remain defined until you exit MS Excel or change them.

Use a cutoff date that aligns with a date such as a fiscal period end date. If you leave the default date, the first aging period (if specified as ‘0’) begins on the most recent date in the column, which may or may not be appropriate for your investigation.

The aging period begins on the date that is equal to the cutoff date minus the number of days and ends at the preceding aging period.

If data included in the aged report falls after the cutoff date, a negative aging period is automatically calculated based on the furthest date past the cutoff.

  Period dates for cutoff at 30 Nov 2016
0 (0 to < 30) 30 (30 to < 60) 60 (60 to < 90) 90 (90 to < 120) 120 (120 to < 365)
1 Nov 2016 to 30 Nov 2016 31 Oct 2016 to 2 Oct 2016 1 Oct 2016 to 2 Sep 2016 1 Sep 2016 to 3 Aug 2016 2 Aug 2016 to 29 Nov 2015

Example

You specify the cutoff date as 30 Nov 2016 and the number of days as 30. As a result, this aging period starts on 31 Oct 2016. In your data, you have two rows with dates of 05 Nov 2016 and 10 Nov 2016. These rows are grouped in the 0 to < 30 period.

Age data

Select the aging column and periods

  1. With a cell selected in an ACL Add-In table, click the ACL Add-In tab and select Summarize > Age.
  2. Select a date column to age.
  3. Optional To omit the count or percentage for the aging periods, clear Include count or Include percentage.

    If you clear Include count, Include percentage is also automatically cleared.

  4. In the Cutoff date field, enter the date that will be used for the beginning of the most recent aging period.

    By default, the most recent date in the column you have selected for aging is selected. You are not prevented from specifying an earlier or more recent cutoff date, however the date must fall after the oldest date in the column.

  5. In the Aging periods field, change the number of days for the periods or keep the default values.
  6. To configure subtotals and pivot output, click Next or click Finish to output the results as is.

Select the numeric subtotal columns

  1. Select one or more numeric columns to subtotal.
  2. Optional To omit the percentage for the subtotal amounts, clear Include percentage.
  3. To configure pivot output, click Next or click Finish to output the results as is.

Define the output format

To define the output format, select one of the following output options and then click Finish:

The output results appear in a new ACL Add-In worksheet. If you output the results to a PivotTable or PivotChart, you can further group aged periods by additional columns, or add additional subtotal columns:

  1. Click any cell in the PivotTable, or click the PivotChart.
  2. If the Pivot Table Field List does not appear, with a cell in the PivotTable selected, click the Options tab and select Field List.
  3. In the PivotTable Field List, select additional columns to group periods or subtotal on.