SI-AR-RT10 AR Aging Summary by Customers

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
  • Filter
  • AR Transactions Detailed Report
  • AR Transactions source tables
  • AR Aging Summary Report
• User Guide
  • Using the report
• Version History

Description

The SI-AR-RT10 AR Aging summary by Customers (or AR Aging Summary by Customers with Retainage) 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:

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:

• Project ID and Project name
• Due in days
• Aging bucket
• % of aging per customer
• % of aging per aging bucket

In the AR Aging Summary by Customers with Retainage variant, additional columns have been added to accurately reflect retainage amounts:

• Outstanding Retainage: displays the retainage balance still open for each invoice, calculated as the difference between the Amount Retained and the Retainage Released.
• Amount Retained: shows the initial amount retained for the invoice.
• Retainage Released: reflects the total retainage amount released as of the specified date.

AR Transactions source tables

To accurately generate the AR Transactions tab, several tables have been created to represent different types of AR transactions in the Sage Intacct instance. The data in all of these tabs is filtered based on the parameters set in the AR Transactions sheet.

The tabs are as follows:

• AR Invoice: contains all invoices recorded in Sage.

• AR Adjustment: includes all AR adjustments made in Sage.

• AR Advance: lists all advance records created in Sage.

• AR Overpayments: lists all the outstanding AR overpayments made on the system.

• AR Payments: displays all payments made in Sage Intacct, used to determine the amount paid for the Invoice and Adjustment transactions. This is crucial for accurately capturing both partial and full payments.

• Applied Advances: displays all advance payments recorded in Sage Intacct. This tab is used to calculate the total amount paid for AR Advances.

• Paid Invoice with Retainage (for AR Aging Summary with Retainage template): displays all paid invoices with any outstanding retainage that has yet to be released.

• Historical AR Retainage (for AR Aging Summary by Customers with Retainage template): provides a list of all AR retainage released as of the specified date, driving the balances in the retainage amount columns on the main tab.

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.

In the AR Aging Summary by Customers with Retainage variant, an additional column has been added to the table to display the summarized retainage amounts at both the Customer and Invoice levels.

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.

• Exclude overpayments: by default, this setting is set to No, ensuring that all AR overpayments are included in the aging process. Change this setting to Yes to exclude overpayment transactions from the report.

• 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 C and D. However, user can always customize it to fit their requirements.

• 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 C3 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.

Version History