SI-CRE-RT13 AR Aging Summary by Customers with Retainage

Applies to

• Product version: Velixo NX

• ERP: Sage Intacct

• Functional area: Financials, Project, Other

• Industry: 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 with Retainage Report

  • Control report

• User Guide

  • Using the report

• Version History

Description

The SI-CRE-RT13 AR Aging Summary by Customers with Retainage report provides an overview of customer debts by age and highlights retainage amounts, crucial for managing receivables and prioritizing collections. It offers insights into aging receivables and funds held for future release

Coverage

This workbook includes the following sheets:

• 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 AR 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 balances.

• Transaction and Payment: these tabs contain all AR transaction records in Sage, organized by transaction type for easier reference.

• 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 and Project managers.

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):

• Project functions

• List functions

• Query functions

Preview

Download this template

Download

💡Having trouble downloading the report?

Simply right-click on the link and select Save link as

Documentation

The SI-CRE-RT13 AR Aging Summary by Cwith Retainage report gives a comprehensive overview of outstanding receivables, organized by customer accounts and aging periods. In addition to displaying unpaid invoices, the report also includes retainage amounts withheld for construction projects. These retainage balances are usually held until all contractual obligations are fulfilled. This report provides a user-friendly method to monitor both standard receivables and retainage, aiding businesses in tracking collections, cash flow, and future revenue.

Filter

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

• 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

• Retainage

Additional details were added as well to aid users with the analysis:

• Project

• Due in days

• Aging bucket

• % of aging per customer

• % of aging per aging bucket

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

• Paid Invoice with Retainage: contains all paid bills with outstanding retainage

• AR Adjustment: includes all AR Adjustments in Sage

• AR Advances: lists all advances records in Sage

Note: Since the Sage object does not provide a posting date for advances, the template defaults the Posting Date to match the Invoice Date. This value can be customized by manually updating the formula in the Posting Date column.

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

• AR Payments (hidden): 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 (hidden): displays all advance payments recorded in Sage Intacct. This tab is used to calculate the total amount paid for AR Advances.

• Historical AR Retainage (hidden): 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 with Retainage Report

The summarized report is created from the detailed AR Transaction report and presented in a Pivot table format. It is categorized by aging bucket and shows the total amount owed by each customer within each aging bucket. The report also includes the total retainage amount per customer, which can be expanded to show invoice numbers for a more detailed view. Users have the flexibility to customize the table according to their specific 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.

Control report

Committed to upholding data integrity across the report and Sage Intacct, the Control sheet serves as a supplementary component to the primary Aging report sheet. Its purpose is to validate the data extracted by Velixo by cross-referencing account and customer-level details with the corresponding extracted balances at the account code and customer level on GL.

The Control sheet is divided into two sections:

• Account Code Balance Check: extracting balances at the account code level to guarantee accurate capture and reconciliation of AR balances with the aging transactions captured on the AR Transactions tab. The Account code should be manually specified on cells B7 and B8 to match the Sage configuration.

Note: There may be variances in this section if Sage is configured for multi-currency. By default, the aging report displays amounts in the transaction currency, while the general ledger (GL) balance reflects values in the base currency.

• Customer Balance Check: this section extracts customer-level balances from the general ledger (GL) and compares them with the customer-level data from the aging report. This helps ensure consistency between the GL and subledger, and highlights any discrepancies that may need investigation.

Note: discrepancies may appear in this section if GL entries lack an associated customer dimension. This can occur when AR transactions are recorded manually in Sage (e.g., via journal entries or imports). Additionally, similar to the Account Code Balance Check, variances may also arise in instances where Sage is configured for multi-currency, due to differences between transaction and base currencies.

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.

• Show Overpayments: by default, this setting is set to No. Change this setting to Yes to include all AR overpayments in the aging process. 

• Show Paid Invoices with Outstanding Retainage: by default, this setting is enabled (set to Yes) to capture all retainage balances in Sage, regardless of whether they are associated with closed invoices. This ensures that the retainage amounts reflected in the aging report align with the General Ledger balances.

• 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 buckets 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 D1 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.

Note: Due to system limitations, the template only supports a single level in the parent-child location hierarchy. If your location structure includes multiple levels, please manually group the relevant locations under the Options tab.

• On the AR Transactions sheet, choose the desired filter:

  • Location

  • Project

  • Customer name

  • As of

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

Important: it is essential to wait until Velixo has fully completed its calculations before refreshing the pivot table. By default, the pivot table is configured to retain deleted data in order to preserve the aging bucket layout. Refreshing the pivot table while Velixo is still calculating can cause incorrect values and may lead to errors such as #CALC!, #REF!, and #VALUE! appearing in the table.

Version History