SI-AP-RT11 AP Aging Summary by Vendors

Created by Aljun Talle, Modified on Fri, 4 Oct at 9:10 AM by Aljun Talle

Applies to

  • Product version: Velixo NX, Velixo Classic
  • ERP: Sage Intacct
  • Functional area: AP
  • Industry: All except Construction
  • Plan: All
  • Template type: Production Report template


TABLE OF CONTENTS


Description

The SI-AP-RT11 AP Aging Summary by Vendors provides a snapshot of the outstanding balances owed to vendors, categorized by aging bucket. It provides essential insights for managing cash flow, prioritizing payments, and maintaining healthy vendor relationships. By highlighting overdue invoices and aging status, the report aids in financial analysis, decision-making, and process improvement within the accounts payable function.


Coverage

This workbook includes the following sample reports:

  • AP Aging Summary: the summarized report showing the AP transactions by aging buckets. This is in Pivot table format and charts are available as well for better visualization.

  • AP Transactions: the detailed report showing the information for each of the AP transactions. Filters are available as well for to further drilldown the required data.

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

  • Financial functions
  • List functions
  • Query functions


Preview




Download this template

Download


💡Having trouble downloading the file?

Simply right-click the link and select Save link as


Documentation

The SI-AP-RT11 AP Aging Summary by Vendor report offers a comprehensive overview of outstanding payables categorized by vendor accounts and aging periods. With a user-friendly interface, accessing the required information about accounts payable status requires minimal effort.


Filter

  • LocationLists 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.
  • Periodoptional 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.
  • Vendor namegiving users options to filter the data to a specific customer only. 
  • As ofa date field. The report will extract all open AP transactions as of the date specified on this field. If left blank, this is defaulted to current date.
  • Aging bywill 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:


 


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

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



AP Transactions source tables

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


The tabs are as follows:

  • AP Bills: contains all bills recorded in Sage.



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



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



  • Credit Card: shows all the credit card transactions created in Sage.



  • AP Payments: displays all payments made in Sage Intacct, used to determine the amount paid for each type of transaction. This is crucial for accurately capturing both partial and full payments.



AP Aging Summary Report

The summarized report is derived from the AP 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 AP Pivot Slicer. This feature allows users to seamlessly filter the table to exclusively display negative amounts, which may be categorized as Advances, Payments, or Adjustments 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 sheetcrucial 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 Sheetas the foundation for all filters on the AP 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 AP 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 AP Transactions sheet, choose the desired filter:
    • Location
    • Period
    • Project
    • Vendor name
    • As of
    • Aging by*



  • Automatic report update: selecting any filter will automatically update the AP Transaction table



  • Once everything is set on the filter and the AP transactions tab has been fully refreshed, go to the AP Aging Summary tab to refresh the Pivot table. Refreshing the Pivot table will automatically refresh the charts.



Version History


VersionReleased onChanges
1April 30, 2024N/A. Initial version of the template.
1.1July 23, 2024
  • Streamlined the report by eliminating hardcoded and concatenated filters from the query, enhancing maintainability and clarity.
  • Removed unnecessary dimensions that were not required for the report, simplifying the report structure and improving performance.
  • Implemented logic to verify the existence of the Project dimension, preventing potential errors if the Project dimension is not available in the instance.
  • Automated the AP GL code selection to reduce manual input from users while still allowing for account code overrides for user customization.
1.2August 07, 2024
  • Added an option to select whether the location should be at the Parent level or Child level.
1.3October 04, 2024
  • Restructured the report by creating separate tabs for each type of AP transaction in Sage Intacct.


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article