AC-GL-RT13 Consolidated Profit and Loss Summary

Applies to

• Product version: Velixo NX, Velixo Classic
• ERP: Acumatica, MYOB Acumatica, Cegid XRP Flex, Hotel Investor Apps, Haufe x360
• Functional area: Financials, Other
• Plan: All
• Template type: Production Report template

TABLE OF CONTENTS

• Applies to
• Description
• Coverage
• Typical audience
• Features
• Preview
• Download this template
• Documentation
  • Filters
  • Relative time
  • P&L Structure
• User Guide
  • Using the report
  • Using the Control report
  • Understanding the Report formula
  • Managing Account Structures
• Version History

Description

The AC-GL-RT13 Consolidated Profit and Loss Summary report offers a concise overview of the collective financial performance of the entire company group in a tenant. By consolidating the revenue, expenses, and net profit of all companies within the organization, this report provides a straightforward analysis of the group's overall profitability and financial success.

Coverage

This workbook includes the following sample reports:

• Consolidated Profit and Loss: the consolidated Profit and Loss report, at the granularity of account classes.
• Control: a control report that can be used to confirm that the data is accurate and balanced.
• Options: the template's settings sheet that is used for data validation lists and various lookups and configurations.
• Information: the information sheet of the report, used for storing useful details about that report such as: code, version, and 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 on the link and select Save link as

Documentation

Navigating and utilizing the AC-GL-RT13 Consolidated Profit and Loss Summary report is an intuitive process, designed for simplicity, genericity and efficiency. The template comes pre-configured with generic account classes streamlining the reporting process with minimal user effort.

Filters

• As of (Period): a financial period field in MM-YYYY format. This will default to current calendar period if left blank.
• Include unposted transactions: an option for user to include unposted transactions
• Relative time: the relative time periods or intervals commonly used in financial reporting and analysis.

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

Relative time

The filter's Relative time periods facilitate comparisons across various time frames, enabling effective performance evaluation and trend analysis. The "As of" filter setting serves as the reference point for calculating these relative time periods. Additionally, there are two distinct types of relative time periods aligned with Acumatica's Financial calendar setup.

• Calendar year relative time periods: calendar year relative time periods extracts balances according to the standard January to December calendar setup, disregarding the Master Financial Calendar configuration in Acumatica. While not recommended, these periods are provided for user's convenience should they require them for comparison purposes.

• Fiscal year time periods: fiscal year time periods extracts balances according to the Master Financial Calendar setup in Acumatica, which is the default setting in the template.

P&L Structure

Sections: Velixo has thoughtfully structured the consolidated P&L report using multiple common sections that should be generic, by default.

• Revenue
• Cost of Goods Sold
• Operating Expenses
• Non-Operating Expenses
• Other Income
• Other Expenses
• Tax
  
  

Generic Account Classes: To enhance the report's usability, Velixo has incorporated a set of predefined account classes. Users have the flexibility to either utilize them as is or tailor them to align with their specific reporting needs.

Acumatica Company list: this section enhances the report's comprehensiveness by presenting individual Profit and Loss reports for each company, with consolidated totals conveniently displayed on the left side.

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 Consolidated Profit and Loss tab, keep the Options sheet up to date. It should always and automatically capture the latest Company configuration on your Acumatica instance.

• Assign a Ledger for each of the Company on Column D.

• Choose the desired filters:after updating the Options sheet, select desired filters for the Consolidated Profit and Loss report.
  • As of: the financial period in MM-YYYY format. This will default to current period if left blank.
  • Include unposted transactions
  • Relative time

• Automatic report update: selecting any filter will automatically update report balances

• Validation and analysis: ensure accuracy by verifying balances in the Control sheet. Confirm that differences for each of the total are zero.

Using the Control report

Committed to upholding data integrity across the report and Acumatica, the Control sheet serves as a supplementary component to the primary Consolidated Profit and Loss tab. Its purpose is to validate the data extracted by Velixo on an account class basis by cross-referencing it with the extracted balances at the account code level for each of the company.

Given that the balances of the account codes aggregate to the account class, the Control sheet ensures the absence of discrepancies in the total for each account type and company. This verification process enhances the reliability and accuracy of the overall financial reporting template.

The Control sheet is divided into three sections:

• Account Code Balance Check: Extracting balances at the account code level to guarantee accurate capture and reconciliation of P&L balances with the account classes listed on the Consolidated Profit and Loss tab. The Account code series is highly flexible, and users may adjust the settings to match their specific requirements at any time.
• P&L Balances: Displays total balances from the Consolidated Profit and Loss tab for reconciliation with the Account Code balance section.
• Differences: Highlights reconciliatory differences between the Account code and account class sections, marked in red font for investigation.
  
  

Understanding the Report formula

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

• Account Class Validation
  1. In Column A, a validation formula was used to verify if the account class in Column B exists in Acumatica
  2. Invalid values (not in Acumatica) return as blank
  3. Ensure continuous use of this formula for any class additions or name changes; a correctly populated formula ensures accurate transaction amounts.

• Transaction Amount formula
  1. Used for all company columns, this formula's primary objective is to extract balances for the account classes or account codes from Acumatica.
  2. Velixo functions, specifically ACCOUNTTURNOVER, power these columns
  3. To facilitate flawless execution of Velixo functions, an IF statement is strategically placed. 
  4. In the account class validation formula, any invalid class yield blanks. The IF statement distinguishes whether the class is valid; if not (blank in Column A), the transaction amount formula sets the amount to blank. On the other hand, if the class is valid, it extracts the balance using the Velixo function.

Managing Account Structures

As outlined earlier in this document, users have the flexibility to tailor this report to their specific needs. This is especially useful when the pre-defined account classes do not quite fit the requirements.

• Adding New Account classes

If users desire a structure beyond the pre-defined account classes, they can leverage account classes set up in their ERP instance.

Users have two options when adding new account classes:

1. Re-purposing an existing pre-defined account class:

• Rename an existing account class, replacing it with the desired custom account class.

2. Adding a Row in the Required P&L Section.

• Use Excel functions to add a new row in the desired section.
• Be cautious with formulas in newly added rows; copying from existing rows in the same section ensures accurate results.

• Removing an Account Class

Removing a pre-defined account class is not really necessary as the report makes use of Velixo’s automatic hiding of rows with zero values, which has been configured on the Consolidated totals columns.

This feature eliminates the need for manual deletions of account class rows and ensures the report remains fully dynamic should usage of account classes change over years or transactions get created against new account classes.

 However, if an account class should really be deleted:

• Utilize Excel's delete row function for removal, followed by thorough data validation and reconciliation on the report.
• Always validate data accuracy using the Control sheet.

• Grouping Account Classes

If needed, the user can group multiple account classes into one and create a summary account class. Velixo Profit and Loss as one.

• On Column B of the Consolidated Profit and Loss tab, user can list all the account groups and using the semi-colon ( ; ) delimiter to grouped them.
• If needed, rename the pre-defined account class on Column E showing the consolidation of the account classes listed on Column B.

Please note that Account Class and Account Code cannot be used at the same time. Doing so will result in calculations errors for the transaction balances. If a structure is not used, make sure to leave them blank to not cause any errors.

Version History