SI-AR-RT10 AR Aging Summary by Customers
Created by Aljun Talle, Modified on Mon, 9 Sep at 10:53 PM by Aljun Talle
Applies to
- Product version: Velixo NX, Velixo Classic
- ERP: Sage Intacct
- Functional area: AR
- Industry: All except Construction
- Plan: All
- Template type: Production Report template
TABLE OF CONTENTS
- Applies to
- Description
- Coverage
- Typical audience
- Features
- Preview
- Download this template
- Documentation
- User Guide
- Version History
Description
The SI-AR-RT10 AR Aging summary by Customers report provides an overview of the outstanding balances owed to a company, categorized by the length of time the invoices have been outstanding. This report also helps businesses monitor the health of their accounts receivable by identifying overdue payments, tracking the aging of outstanding invoices, and highlighting potential cash flow issues.
Coverage
This workbook includes the following sample reports:
AR Aging Summary: the summarized report showing the AR transactions by aging buckets. This is in Pivot table format and charts are available as well for better visualization.
AR Transactions: the detailed report showing the information for each of the transactions. Filters are available as well for to further drilldown the required data.
Control: a control report that can be used to confirm that the data is accurate and tied up with the GL.
Options: the report's setting sheet that is use for data validation lists and various lookup configuration.
Information: the information sheet of the report, used for storing useful details about the report such as: code, version, and article link in order to keep track of the origin of the report.
Typical audience
The typical users of this type of report are Finance professionals: Accountants, CFOs, Controllers
Features
The following features are used by this report. To use this report, please ensure that your Velixo license includes all of them, (or contact Support or Sales):
- Financial functions
- List functions
- Query functions
Preview
Download this template
💡Having Trouble downloading the file?
Simply right-click the link and select Save link as
Documentation
The SI-AR-RT10 AR Aging Summary by Customer report provides a concise breakdown of outstanding balances by customer accounts and aging periods. Its user-friendly interface ensures effortless execution, requiring minimal effort for comprehensive insights into accounts receivable status.
Filter
Located in the upper left of the AR Transactions sheet, the filters include:
- Location: lists all entities present in Sage Instance (excluding groups) including any custom group defined in the options sheet. For ease of use, Velixo shows you the Entity ID that you may be familiar with along with the entity name. This Display name is configurable in the Options sheet.
- Period: optional filter and can be used to extract the transactions posted on the period specified.
- Project: giving users options to filter the data to a specific project only.
- Customer name: giving users options to filter the data to a specific customer only.
- As of: a date field. The report will extract all open AR transactions as of the date specified on this field. If left blank, this is defaulted to current date.
- Aging by: will be the basis of the aging days calculations. Users can select from Document date, Due date or Posting date.
These filters are seamlessly linked to the data on the Options tab:
AR Transactions Detailed Report
The report is created to gather all important basic information for an aging report. It is neatly organized in a table and includes these key details:
- Customer
- Location
- Reference or Document number
- Transaction currency
- Dates
- Amount
Additional details were added as well to aid users with the analysis:
- Sage Intacct dimensions
- Due in days
- Aging bucket
- % of aging per customer
- % of aging per aging bucket
AR Aging Summary Report
The summarized report is derived from the AR Transaction detailed report and is presented in a Pivot table format. It is categorized by aging bucket and displays various types of calculations. Users have the flexibility to customize the table accordingly based on their needs.
For user convenience in isolating the negative amounts within the Pivot table, Velixo has added the Negative AR Pivot Slicer. This feature allows users to seamlessly filter the table to exclusively display negative amounts, which may be categorized as Prepayments/Advances, Payments, or Credit Memos in Sage Intacct.
Charts are added as well to aid users in visualization of the data.
User Guide
Using the report
Now that we've covered the report overview, let's dive into a step-by-step guide on effectively utilizing the report:
- Set the Connection Name on the Options sheet: crucial for all processes, set the Connection Name on the Options sheet by updating cell A2. Ensure it matches the name used during Velixo application login.
- Update the Options Sheet: as the foundation for all filters on the AR Transactions tab, keep the Options sheet up to date. It should always automatically capture the latest dimension and structures from your Sage Intacct instance.
- Velixo have set multiple aging bucket by default in columns P and Q. However, user can always customize it to fit their requirements.
- Update the AR GL code and Advance GL code as needed: This crucial step ensures accurate identification of aging transactions in Sage. By default, both the AR and Advances codes are derived from the setup in the Sage instance AR configuration.
If no account code is set for customer advances (since this is optional in the Sage instance), users can manually input the Advances code on the Options sheet. Additionally, if users wish to use an AR GL code other than the default, they can enter it in the AR GL code override column. For multiple accounts, group them using a semi-colon.
- Setting the Location Configuration Option: to enable further customization, the template includes an option for users to choose the location level (Parent, Child, or both). By default, the template is set to extract the balances at Parent level.
- Navigate to the AR Transactions tab and expand the grouped rows by clicking the plus sign (+) on the upper left corner of the tab.
- Go to cell C7 and select your preferred location level from the dropdown menu:
- Parent only: extract the balances exclusively for the parent location, excluding any associated child locations.
- Child only: extract the balances solely for the child locations, excluding the parent location.
- Parent and Child: extract the balances for both the parent and child locations.
- On the AR Transactions sheet, choose the desired filter:
- Location
- Period
- Project
- Customer Name
- As of in MM/dd/YYYY format (this will default to current date if left blank)
- Aging by
- Automatic report update: selecting any filter will automatically update the AR Transaction table
- Once everything is set on the filter and the AR transactions tab has been fully refreshed, go to the AR Aging Summary tab to refresh the Pivot table. Refreshing the Pivot table will automatically refresh the charts.
- Validation and analysis: ensure accuracy by verifying balances in the Control Sheet.
Specify the following details on the Control sheet to get the GL balances:
- Books: books on Sage Intacct to extract the balances from
- Accounts Receivable (AR) GL code: the default AR GL code on Sage Intacct
- Customer Advances GL Code: the default customer advances GL code on Sage Intacct
Using the Control report
Committed to upholding data integrity across the report and Sage Intacct, the Control sheet serves as a supplementary component to the primary AR aging report sheets. Its purpose is to validate the data extracted by Velixo on a customer level basis and cross-referencing it with the extracted balances at the AR account code level.
The Control sheet is divided into three sections:
- Customer level balance check: Extracting GL balances at the customer level. The customer list is automatically updated based on the records retrieved on the AR Transactions sheet.
- Aging Balance: the total balance per customer extracted on the AR Transactions sheet. This is then matched with the balance extracted using the AR GL code.
- Differences: Highlights reconciliatory differences between the AR GL code and the aging report on customer level.
Comparing with the Sage Intacct AR Aging Report
The template draws inspiration from the familiar design of the Sage Intacct AR Aging report. However, there will be differences as different platforms were used:
- Reference or Document number reconciliation: In the Sage Intacct AR Aging report, payments or advances are included on the applied invoice total balance. However, in this report, payments or advances are not included in the applied invoice balance, and are instead recorded separately within the report. The report maintains reconciliation with the Sage Intacct AR Aging report both on customer level and in terms of overall total.
- Reversed Invoices: In the Sage Intacct AR Aging report, reversed invoices are automatically excluded from the report, regardless of the specified "As of" date. However, in this report, reversals are excluded only if they occur before or on the same date as the specified date.
Version History
Version | Released on | Changes |
1 | April 30, 2024 | N/A. Initial version of the template. |
1.1 | July 23, 2024 |
|
1.2 | August 07, 2024 |
|
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article