AC-GL-RT3 - Trial Balance

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

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:

• Beginning Balance

  • Using the ACCOUNTBEGINNINGBALANCE function, this column returns the balances from the start of the fiscal month.

• Ending 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 are 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:

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

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