AC-GL-RT3 - Trial Balance

Created by Aljun Talle, Modified on Fri, 20 Sep at 5:29 AM by Aljun Talle

Applies to

  • Product version: Velixo NX
  • ERP: Acumatica
  • Functional area: Financials, Other
  • Template type: Production Report template


TABLE OF CONTENTS


Description

Please see Introduction to Report Templates for information about Samples, Demos, and Production Templates and the differences between them.  You many also want to refer to How to Modify Velixo Sample Reports.


The AC-GL-RT3 Trial Balance report serves as a financial tool, systematically presenting the debit and credit balances of a company's accounts. Its primary function is to conduct preliminary checks to verify the accuracy of recorded transactions. Additionally, this report details the opening and closing balances, providing a comprehensive financial snapshot that provides valuable insights into the company's fiscal standing at a specific moment in time.


Coverage

This workbook includes the following sheets:

  • Trial Balance: the Trial Balance report comprises Acumatica's GL account codes. Listed in order together with the corresponding balances.
  • Options: the template’s settings sheet that used for data validation lists and various lookups and configurations.
  • Information: the information sheet of the report, used for storing useful details about the report such as: code, version, article link 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


Preview



Download this template

Download


💡Having trouble downloading the file?

Simply right-click the link and select Save link as


Documentation

Navigating and utilizing the AC-GL-RT3 Trial Balance report is a very straightforward process, designed for simplicity, genericity and efficiency. The report has conveniently included the account codes from Acumatica ERP, enhancing user convenience.


Filters

Located in the upper left of the sheet, the filters include:

  • Branch: lists all the Branches present in Acumatica Instance including any custom group defined in the options sheet. For ease of use, Velixo shows you the Branch ID that you may be familiar with along with the Branch name. This Display name is configurable in the Options sheet.
  • As of (Period): a date field. Please use the last date of a financial period/month
  • Ledger: the ledger to pull the balances from.
  • Include unposted transaction: an option for user to include unposted transactions



Trial Balance Structure

 

Accounts Codes: Velixo used a VX function to structure the Trial Balance report to list all the available account codes from the Acumatica ERP. The corresponding account names are included as well for a better overview.



Metrics: The report includes the following metrics:

  • Opening Balance
    • Using the ACCOUNTBEGINNINGBALANCE function, this column returns the balances from the start of the fiscal month.
  • Closing Balance
    • Using the ACCOUNTENDINGBALANCE function, this column returns the balances as of the date specific on the As of filter.
  • Debit / Credit
    • The Debit and Credit balances per account extracted using the functions ACCOUNTTOTALDEBITS and ACCOUNTTOTALCREDITS.



Time Period


The incorporated time period encompasses the following:


Current Year:

  • Provides the accumulated balance from the start of the fiscal year.
  • To obtain the full year position, it is crucial to specify the last date of the fiscal year.



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 Trial Balance tab, keep the Options sheet up to date. It should always and automatically capture the latest dimensions and structures from your Acumatica instance; however, you can also customize it.



  • Specify the Default Net Income Account - the default Net Income account can be found on Acumatica ERP by going to General Ledger Preferences:



  • Choose Desired Filters - After updating the Options sheet, select desired filters for the report:
    • Branch / Company
    • As of (Period) in MM-YYYY format
    • Ledger
    • Include unposted transactions



  • Automatic Report Update - Selecting any filter will automatically update report balances. Begin analyzing the report with the latest data.



  • Validation and analysis: ensure accuracy by verifying balances in the Trial Balance totals.
    • Totals for Opening and Closing Balances should be zero
    • Debit and Credit should have matching balances



Understanding the Report Formula


Velixo created the template using a combination of Excel functions and Velixo functions for the Trial Balance report. The report incorporates two primary formulas on the Trial Balance tab:


Account Code listing

  • In Column B, the function EXPANDACCOUNTRANGE was used to extract all the active account codes on the Acumatica ERP.



  • In Column C, the function ACCOUNTNAME was used to extract the name of the listed account codes on Column B.



Transaction Amount formula: Velixo has used multiple financial functions to extract the balances per column:

  • Opening Balance – the function ACCOUNTBEGINNINGBALANCE was used to extract the balances for this column. This function retrieves the balances for the account at the start of the fiscal year.



  • Debit / Credit – the functions ACCOUNTTOTALDEBITS and ACCOUNTTOTALCREDITS were used to extract the balances for these columns. These are movement balances from the start of the fiscal year up to the specified date on the As of date filter.




  • Closing Balance – the function ACCOUNTENDINGBALANCE was used to extract the balances for this column. This function retrieves the balances for the account as of the date specified on the as of date filter.



Customizing the template


Reviewing the Options sheet


The Options tab comprises Acumatica dimensions, and other attributes and structures used in the report. This information updates in real-time with Velixo app refresh, facilitating immediate integration of newly added ERP data into the report as filters.


This includes:

  • Connection Name
  • Branch
  • Company
  • Default Net Income Account



The Connection Name is subject to variation depending on the user input provided through the Velixo login portal. For convenience, users may add the connection name used on cell A2. This cell has been designated as a named range (Connection), ensuring its automatic application to all formulas throughout the sheet.



Users can also customize Branch by adding their preferred custom values, using a list and ranges as described in this article.


To do so:

  1. collapse the column groups for the Branch dimension 
  2. then simply input the desired values in the Group formula and Custom group name columns, highlighted in light blue for quick identification.

 


Version history

 

Version

Released on

Changes

1

January 18, 2024

N/A. Initial version of the template.

1.1June 10, 2024
  • Enhanced the "As of" field to accept both financial periods and specific dates.2. Introduced support for non-calendar fiscal years.
1.2September 20, 2024
  • Updated the date logic to accurately support non-calendar fiscal years.

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