SI-GL-RT13 Consolidated Profit and Loss Summary

Applies to

• Product version: Velixo NX, Velixo Classic
• ERP: Sage Intacct
• Functional area: Financials, Other
• Industry: General, Services
• Plan: All
• Template type: Production Report template

TABLE OF CONTENTS

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

Description

The SI-GL-RT13 Consolidated Profit and Loss Summary report offers a concise overview of the collective financial performance of the entire company groups. 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 categories.
• 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 this file?

Simply right-click on the link and select Save link as

Documentation

Navigating and utilizing the SI-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 categories streamlining the reporting process with minimal user effort.

Filters

• Department: giving users option to filter the report by specific department only.
• As of: a date field. Please use the last date of a financial period/month. This will default to current date if left blank.
• 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.

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

QuickStart Account Categories: Built on the foundation of Sage Intacct out-of-the-box account categories, the template is ready for use post-ERP implementation. 

Sage Intacct Location 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 Sage Intacct instance.

• Assign a fiscal start month for each location in Column E. This step is optional; if all locations follow the calendar year as the fiscal year, you can leave the field blank, and it will default to January.

• Choose the desired filters: after updating the Options sheet, select desired filters for the Consolidated Profit and Loss report.
  • Department
  • As of
  • 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 Sage Intacct, 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 category basis by cross-referencing it with the extracted balances at the account code level for each of the location.

Given that the balances of the account codes aggregate to the account category, 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 categories 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 category 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 Profit and Loss report. The report incorporates three primary formulas on the Profit and Loss tab:

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

• Account Code Validation
  1. Column B contains a validation formula designed to retrieve mapped account codes to the corresponding QuickStart category in Column C. If no account codes are mapped to the specified QuickStart category in the ERP, it will return a blank value.
  2. The retrieved account codes serve as the foundation for the Transaction Amount calculation formula.
  3. This process exclusively applies to QuickStart categories. If an account group is specified in Column B instead, it will return a blank value, prompting the Transaction Amount formula to reference Column A for calculation purposes.

• Transaction Amount formula
  1. Used in all Location columns, this formula’s primary objective is to extract balances for the account categories from Sage Intacct ERP
  2. Velixo functions, specifically SI.TURNOVER, power these columns
  3. To facilitate flawless execution of Velixo functions, an IF statement is strategically placed.
  4. The validation formulas for account codes and account groups ensure that any invalid inputs result in blanks. The IF statement discerns the validity of the input: if both columns A and B are blank, the transaction amount formula sets the amount to zero. Conversely, if the category is valid and has mapped accounts associated with it, or if the account group is valid within the ERP system, the balance is extracted using the Velixo function.

Managing Account Structures

The calculated transaction amounts are determined solely by the account codes mapped to the designated QuickStart category in Column C. This ensures the report is fully ready for use by anyone, 100% out-of-the-box, provided the account codes are accurately linked to the QuickStart category in Sage Intacct.

However, as outlined earlier in this document, users have the flexibility to tailor this report to their specific needs. This is especially useful when the default QuickStart categories don't quite fit the requirements.

• Adding New Account Section

If users desire a structure beyond the provided account categories, they can leverage account groups set up in their ERP instance. However, caution is crucial to avoid overlap between account groups, preventing inaccuracies. Opting for a granular account category list ensures data is accurate.

Users have two options when adding new account structure:

1. Re-purposing an existing Account Category:

• Rename an existing account category line, replacing it with the desired custom account group.
• Ensure exact naming consistency with the ERP instance for Velixo to accurately return amounts.

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 category

 While users have the freedom to remove an account category, it's strongly discouraged as the report makes use of Velixo’s automatic hiding of rows with zero values, which has been configured on the Total column. 

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

However, if an account category 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 Groups

The user can group multiple account groups into one to create a summary account. Velixo enabled this feature so that user can consolidate similar account groups and present them in the Profit and Loss report as one.

• On column C of the Profit and Loss tab, user can list all the account groups and using the semi-colon ( ; ) delimiter to group them
• If needed, rename the account category on column E showing the consolidation of the account groups listed on column C

Note: User also have the ability to group QuickStart categories to form custom ones. However, currently, merging QuickStart categories with Account Groups is not feasible. This functionality will only be available if the QuickStart category aligns precisely with an account group. Although this is not recommended as it may lead to data inaccuracies.

• Using of Account Code/Range
  

As an additional feature, Velixo added the ability to use Account Code or Account Code ranges to extract the balance for the category:

• On column D of the Profit and Loss tab, users can list all the account code or ranges they wish to use or consolidate. Same as account groups, the semi-colon delimiter should be used as well for the groupings.
• If needed, rename the account category on column E showing the consolidation of the account codes listed on column D.

Please note that Account Group and Account Code cannot be used at the same time. If both have values, it will result into calculations errors for the transaction balances. If a structure is not used, make sure to leave one of them at least blank to not cause any errors.

Version History