Aged Reports

Summary

This article describes configuring Aged Reports and Settings. An Aged Balance report details the amounts owed by customers by age. If a customer owes you $50.00 and that $50.00 was due two months ago, it would be considered an 'aged receivable'. EngageIP has several aged reports described below.

Aged Reports Overview

Aged Balance

This is a standard aged report based off transaction dates individually, totaled within the reporting period columns, e.g. 30-60 days aged.

Aged Invoice Balance

Open

This report provides aged balances based on the date you select (invoice created date, due date, posted date or sent date). It calculates invoice balances as totals as of the selected date type rather then considering individual transactions and dates. The 'Total' column presents the invoice balance total as of the date selected, e.g. the amount owing on invoices as of the date you specified.

• Date Settings:

  • Created Date - the date the invoice was created. Transactions for open or closed invoices will be included in the report

  • Due date - the due date specified on the invoice. Transactions for closed invoices only will be in this report

  • Posting Date - the date the invoice was posted. Transactions for closed invoices only will be in this report

  • Sent Date - the date that the invoice was sent (or printed if you are printing them). Transactions for closed invoices only will be in this report

• Column Definitions:

  • User Balance - the balance as of the date selected, including transactions prior to the date
    Notice: disbursed payments are reflected in the User Balance column, even when the disbursed payment was made after the Date value you specified when creating the Report

  • Total - the total value of all outstanding invoice balances

  • Current - the amount on the account that is NOT overdue / aged

Aged Invoice Detail Balance

One cannot directly compare aged balance and aged invoice balance. The aged invoice balance considers the invoice balance effectively as of TODAY since disbursements to those invoices are not dated. It may not have been paid as of the date you select but paid after the date at which point the balance returned would be less than what the aged balance reflects since aged balance more accurately reflects payments according to date. To match the two, first filter out invoice dates greater than the date selected on the aged invoice balance, then undisburse any payments / credits which MAY have been disbursed to the invoices in the aged invoice balance report (i.e. any payments added after the date you ran the report with). At this point with payments undisbursed, you will get an accurate idea of the actual state of the invoice balance on the report since the report doesn’t have enough data to determine the amount of balance left as of a specific date – again, due to the fact that disbursements for credits or payments are not actually dated, only the original credit or payment is.

Aged Invoice Report Image Example

Open

Aged Balance Report

This report lists all receivables aged in the specific period columns according to how you have configured your report. By default, its 30, 60, and 90 and 120+ days.

The Aged report has configurable columns, allowing you to specify whichever number of days you would like to see represented by each column. For example the standard columns are 30 / 60 / 90. The configuration screen for this setup would look like the following:

As well the above image would be displayed on the Aged Report as shown below:

If for example you wanted to have 10 / 30 / 50 columns or similar, you would configure it as below to show the appropriate columns:

The above image depicts the configuration for the below report image:

Aged Report Configuration

1. Click Setup tab

2. Click Aged Balance Report Settings

3. Add the number of days each column should represent (example below)

4. Click the Save button

5. Confirm the changes took effect by going to the Aged Report

   1. Load the Reports tab

   2. Click on Aged Balance

   3. Click the Date button in the lower right

   4. On the page that loads select a date (this forces the system to regenerate the report with your new configuration)
      Note: the Date value returns values that are less than the selected date. For example, to report on transactions that are dated April 30th or older, select a date of May 1. If you select April 30th as the date the report will return date up until the end of day, April 29th

   5. Confirm that the report shows the columns as needed

Automating Aged Report Generation

The below allows you to setup an SQL job to generate an AR report on any schedule you wish. If your AR report takes a long time to load then this approach may be a good option.

1. Create SQL job in MSSQL Studio Management

    

2. Set the SQL job to use 'BOSS' database or whatever your main EngageIP database is named

3. Enter the SQL in the box below

   DECLARE @DATE datetime; SET @DATE = CONVERT(VARCHAR,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),0),101)--'2017-01-01' EXEC logisense_boss_aged @DATE,'30','30','30','30' UPDATE agedreportsettings SET DATE = @DATE WHERE ownerid = 1

   • Configure the date that you require, by default it’s the first day of next month

   • Configure the periods as you have selected on your AR settings on the Setup tab. By default, they periods are all set to 30 day periods

   • Define the ownerID of the owner you wish to have the AR generated for (usually ID 1 is correct)

4. Configure the job to run on the schedule you require

5. Test by manually executing the job and check that it has updated the AR as expected