Table of Contents |
---|
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
Info |
---|
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.
Info |
---|
Note: deposits are NOT reflected on this report in the credit column |
Info |
---|
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 ReportTotal - 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 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
Info |
---|
Note: some columns were hidden in this image below in order to present only |
...
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.
Info |
---|
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:
...
The above image depicts the configuration for the below report image:
...
Aged Report Configuration
Click Setup tab
Click Aged Balance Report Settings
Add the number of days each column should represent (example below)
Click the Save button
Confirm the changes took effect by going to the Aged Report
Load the Reports tab
Click on Aged Balance
Click the Date button in the lower right
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 29thConfirm 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.
Create SQL job in MSSQL Studio Management
Set the SQL job to use 'BOSS' database or whatever your main EngageIP database is named
Enter the SQL in the box below
Code Block language 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
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)
Configure the job to run on the schedule you require
Test by manually executing the job and check that it has updated the AR as expected