Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

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

Note: whenever viewing Aged reports ensure you click the 'Date' button on the bottom right of the screen to generate the report. Otherwise the Report will show data that was last generated and still stored in the database. Aged reports will not automatically generate new data each day as other reports do. The date value will return data up until AND including that the day specified

 

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

Note: deposits are NOT reflected on this report in the credit column
Note: If you are using 30 day buckets (30 30 30 30), and you have 28 days in February, your reporting may look incorrect as some invoices for March 1 may be included in a bucket you do not expect as example. The days in a month needs to be considered  /understood  / accounted for when using these reports

Aged Invoice Balance

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 (ie 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

Note: some columns were hidden in this image to only present the most critical data

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.

Note: if you are attempting to add the totals across the bottom be aware that the total column does include credit balances so it will show less then the sum of the aged columns. To be able to sum it, click the total header, enter a zero in the 'min' text box, and hit enter. This tells the system not to consider credit balances in your report

 

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

    [cce lang='sql']
    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
    [/cce]

    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

  • No labels