SI-GL-RT3 - Trial Balance

Applies to

• Product version: Velixo NX
• ERP: Sage Intacct
• Functional area: Financials, Other
• Plan: Free, Essentials, Professional, Advanced
• Template type: Production Report template

TABLE OF CONTENTS

• Applies to
• Description
• Coverage
• Typical audience
• Features
• Preview
• Download this template
• Documentation
  • Filters
  • Trial Balance Structure
• User Guide
  • Using the report
  • Using the Control report
• Understanding the Report Formula
• Customizing the template
  • Reviewing the Options sheet
• Version history

Description

The Velixo SI-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 Sage Intacct's GL account codes. Listed in order together with the corresponding balances.
• Control: a control report that can be used to confirm that the data is accurate and 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 Velixo SI-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 Sage Intacct ERP, enhancing user convenience. 

Filters

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

• 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.
• Department: lists all departments present in Sage Instance (excluding groups) including any custom group defined in the options sheet. For ease of use, Velixo shows you the Department id that you may be familiar with along with the department 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 
• Books: lists every type of books defined in your Sage Instance in a dropdown list. The report defaults to the Accrual book, but user can add more than one book by concatenating books in the filter value. For example: Accrual;GAAP will return amounts for those two books

These filters are seamlessly linked to the data on the Options tab.

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 Sage Intacct ERP. The corresponding account names are included as well for a better overview.

Metrics: The report includes the following metrics :

• Opening Balance
  • Using the SI.OPENINGBALANCE function, this column returns the balances from the start of the fiscal month.
• Closing Balance
  • Using the SI.CLOSINGBALANCE 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 SI.DEBITS and SI.CREDITS.
• Adjusting Debit / Credit
  • The Adjusting Debit and Credit balances per account extracted using the functions SI.ADJDEBITS and SI.ADJCREDITS

Time Period

The incorporated time period encompasses the following:

Current Year:

• Provides the accumulated balance from the start of the fiscal year (depending on the user input for the fiscal start month on the Options tab) up to the indicated as-of-date.
• For example, if a user selects 6/30/2019, and the specified fiscal start month is April, the returned balance spans from April 1st, 2019, to June 30th, 2019.
• 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 Sage Intacct instance; however, you can also customize it. Additionally, specify the Fiscal Start Month on the same sheet as it will be the basis of the calculation for the YTD balances.

• Choose Desired Filters- After updating the Options sheet, select desired filters for the report:
  • Location
  • Department
  • As of (Period) in MM/dd/YYYY format
  • Books (single select or multiple using semi-colon as delimiter)

• 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 and Control Sheet.
  • Totals for Opening and Closing Balances should be zero
  • Debit and Credit should have matching balances
  • Adjusting Debit and Credit should have matching balances

Using the Control report

Committed to upholding data integrity across the report and Sage Intacct, the Control sheet serves as a supplementary component to the primary Trial Balance tab. Its purpose is to validate the data extracted by Velixo on an account code level and ensure that the following conditions are met:

• Debits and Credits totals should be matching and sum up to zero
• Adjusting Debits and Credits should be matching and sum up to zero
• Opening and Closing Balances should have a total of zero

For convenience, Velixo outlined the balances in a summarized format by account type. User will have the flexibility to modify the account ranges on Column C to meet their ERP configuration.

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 A, the function SI.EXPANDACCOUNTRANGE was used to extract all the active account codes on the Sage Intacct ERP. Please note that as default, Velixo excluded the Statistical accounts on this list.

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

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

• Opening Balance – the function SI.OPENINGBALANCE 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 SI.DEBITS and SI.CREDITS 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.

• Adjusting Debit / Credit – the functions SI.ADJDEBITS and SI.ADJCREDITS were used to extract the balances for these columns. Same as Debit and Credit, 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 SI.CLOSINGBALANCE 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 Sage Intacct 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
• Location
• Department
• Books
• Fiscal Year Setup

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 Location and Department dimensions 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 either the Department or Location. 
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