BC-AR-RT6 AR Aging Summary by Customers

Applies to

• Product version: Velixo NX
• ERP: Dynamics 365 Business Central
• Functional area: AR
• 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
  • Control report
• User Guide
  • Using the report
• Version History

Description

The BC-AR-RT6 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 balances.
• Invoice and Payment tabs: these tabs contain all AR transaction records in Dynamics 365 Business Central.
• 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

Template will be available for download soon

💡Having trouble downloading this file?

Simply right-click on the link and select Save Link As

Documentation

The BC-AR-RT6 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:

• Customer: 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 and transaction filtering. 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
• Reference or Document number
• Dates
• Amount
• Dimensions

Important: The report reflects dimensions set at the header level of the invoice. Dimensions applied at the line level are not included in the report.

Note: The dimension list updates dynamically based on the number of dimensions configured in your instance. However, the template can process a maximum of 15 dimensions per company. If your instance includes more than 15 dimensions, please contact Velixo Support for assistance.

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

• 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, two tables have been created to represent Invoice and Payment AR transactions in the Dynamics 365 Business Central instance. The data in all of these tabs is filtered based on the parameters set in the AR Transactions sheet.

Invoice: contains all records that increase the Accounts Receivable balance, such as invoices and debit memos.

AR Payment: contains all payment records that decrease the Accounts Receivable balance.

Other AR Payments: contains all payment records based on the specified Source codes on the Options sheet.

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 identifying how each transaction impacts Accounts Receivable within the Pivot Table, Velixo introduces the AR Impact Pivot Slicer. This feature allows users to filter the table by transaction effect, showing either records that increase AR (such as Invoices and Debit Memos) or those that decrease AR (such as Payments and Credit Memos) in Dynamics 365 Business Central.

Charts are added as well to aid users in visualization of the data.

Control report

This tab provides a basic comparison between the Accounts Receivable GL balance and the Aging Report balance. To use it, specify the corresponding GL account codes in cell B4.

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 filters on the AR transactions sheet, keep the Options sheet up to date. It should always and automatically capture the latest dimensions and structures from your Dynamics 365 Business Central instance.

• Velixo have set multiple aging bucket by default in columns C and D. However, user can always customize it to fit their requirements.

• Specify the Source codes for the Other AP Payments (Optional): this will drive the transactions that will be extracted on the Other AP Payment tab

• Choose Desired Filters: after updating the Options sheet, select desired filters for the report:
  • Customer
  • 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