Multi-Company Financial Consolidation

 

Overview

xTuple ERP supports a multi-company chart of accounts and consolidated financial reporting against that chart of accounts. However, the application does not support requirements for certain multi-company deployments, such as privileges by company, sales account mappings by company, and year-end retained earnings roll over by company. To achieve company specific control in those areas, the most practical thing to do is create a separate database for each company. However, if a company chooses to go that route, it is highly likely that they would want to consolidate the financial statements of the various databases into a single consolidated statement.

The purpose of multi-company consolidation is to allow companies implemented on separate databases to report on a single consolidated financial statement. A utility that synchronizes trial balances across these databases has been developed for this purpose. Child databases maintain company-specific trial balance data and a parent database receives copies of the data from each child. The new parent data can then be used to generate consolidated financial statements using the xTuple Financial Reporting Engine (FRE).

Brief Explanation

The basic steps for multi-company consolidation are as follows:

  • Enable multi-company consolidation in the accounting configuration on a "parent" database.
  • Create company segments in the parent database that correspond with and are pointed to one or many "child" databases.
  • Create financial reports on the parent database that span all companies.
  • Conduct normal business transactions in all databases.
  • Synchronize companies.
  • View or print multi-company financial reports.

Multi-company financial consolidation is only available on xTuple ERP Distribution Edition or higher. Synchronization may be conducted between a Distribution, Manufacturing, or Enterprise Edition parent database and any child edition of xTuple ERP, including the PostBooks Edition. Synchronization with third party financial systems is not supported.

A typical synchronization scheme is pictured below, where the blocks on the left represent trial balances in separate databases being copied and consolidated together with another chart of accounts.

Multi-Company Consolidation Financial Flow

MultiCompanyDataFlow.png

Terms and Definitions

  • Child Database
    • A database where general ledger transactions for affiliated companies are generated and maintained.
  • Parent Database
    • A database where account and trial balance data from child databases is copied to. Child data can not be altered at the parent.
  • Synchronize
    • To update the parent database so its trial balance data set matches the child database.

Prerequisites

Users should be familiar with the basic accounting tools in xTuple ERP. In particular, this functionality leverages the company segment and the Financial Reporting Engine in the xTuple accounting module.

Additionally, multi-company support requires an understanding of how PostgreSQL database instances are distributed and installed. Users should be able to create and connect to multiple database instances of xTuple ERP.

Detailed Explanation

To use multi-company consolidation, users must create charts of accounts using distinct company segment codes in each database they want to consolidate. One company database is selected as the parent database, and company segments for external companies that reference the databases where the child data actually resides, are set up on the parent. When synchronized, the charts of accounts and trial balances for account numbers using the referenced company segment are imported from the child database(s) to the parent in order to enable consolidated financial reporting.

At the most basic level, multi-company consolidation must be enabled on the parent database. To do this, follow these steps:

  • Go to the "Accounting" module from the top menu 
  • Select the "Setup" submenu
  • Navigate to the left menu and click the "Configure" drop-down list 
  • Select "Accounting" from drop-down list
  • The check box titled "Enable External Company Consolidation" must be checked: 

Configure Accounting

configureGL.png

This option is only available on xTupleERP Distribution Edition or higher.

When multi-company consolidation is enabled, the set up window for company segments will appear as pictured below:

  • Go to the "Accounting" module from the top menu
  • Select the "Ledger Accounts" submenu
  • Choose the "Companies" option from the menu
  • The "List Companies" window will open, click the NEW button:

Company Number

company.png

Users will be able to check an "External Company" check box which requires server, port and database fields to be filled in when checked. Once G/L accounts have been created using this company segment, either manually or via synchronization, the external option will no longer be editable.

Once users have filled in all connection data to the remote child database, the TEST button will be enabled. When clicked, the user will be prompted with the standard log on screen asking for authentication for the external database—then a series of checks will be run to validate the connection.

Synchronize Companies

To synchronize the parent database with child databases, follow these steps:

  • Go to the "Accounting" module from the top menu
  • Select the "Utilities" submenu
  • Choose the "Synchronize Companies" option to open the "Synchronize Companies" window:

It will only be visible when external company consolidation is enabled—and only if users have appropriate permissions.

syncCompanies1.png

Users may make multiple selections of periods and companies to synchronize. Only company segments flagged as external will be listed under the companies list. When the user clicks the SYNCHRONIZE button, the system will first prompt for log in credentials for each database and validate the databases against one another. If the validation succeeds, the utility will then synchronize the chart of accounts and trial balances for each of the companies. For a synchronization to succeed, the following criteria need to be true:

  • The client must be able to connect to all child databases.
  • The child databases must be the same version.
  • The child databases must use the same base currency.
  • The child databases must have matching periods.
  • The child databases must have accounts with matching company segments.

If problems arise, the user will have to identify and resolve these issues in order to complete the synchronization.

Again, only account numbers and summary trial balance data is imported into the parent database. However, these account numbers and trial balances are not viewable or editable in the parent database except via the "Financial Reporting Engine." Any review of or changes that need to be made to these records should be conducted on the child database, then re-synchronized to the parent.

For accurate reporting, users of this functionality should be well-trained and implement procedures to ensure that company financial data is synchronized properly. It is easy to envision a scenario where users notice a mistake on a consolidated statement, fix the problem on the child company database, but forget to re-synchronize the parent and wonder why the consolidated statement on the parent doesn't reflect the change.

Since trial balance records are already summarized information, there should be nominal load requirements even when synchronizing across a WAN.

Sample Financial Report

The following screenshot shows an income statement that has consolidated a company—the general ledger accounts having the 01 prefix.

  • Go to "Accounting" from the top menu
  • Select the "Financial Statements" submenu
  • Click the "View Financial Report" option to open it's corresponding window
  • Select a time period and hit QUERY:

View Financial Report

incomeStatement.png

Here is the same report in printable form (can be found by hitting the PREVIEW button from the menu):

incomeStatementPdf.png

Multi-Currency Support

When data is imported to the parent from a child of a different currency, the general ledger transactions of the child are converted to the functional currency of the parent using the exchange rate found in the parent database. The difference between currency rates on the date of the transaction on the values at the end of the period for assets and liabilities should be recorded as unrealized gain/loss transactions.

New Terms and Definitions

  • IFRS:  International Financial Reporting Standards. These are used as the guidelines for the implementation of this feature.
  • Functional Currency: Also known as the "Base" currency in an xTuple database. It is the working currency that all financial transactions are recorded in for internal reporting purposes.

User-Level Functionality

A G/L account mapping has been added to company maintenance for "Unrealized Gain/Loss" during financial consolidation. This mapping will only be available on commercial editions of xTuple ERP. When a user elects to synchronize database financials the system will check for the existence of these mappings where appropriate and halt processing if they are not defined. 

IFRS standards require that companies fully disclose a variety of implementation specific calculation techniques and exceptions on financial statements, particularly when multi-company consolidation is involved. To help users comply with these requirements  a "Notes" tab has been added to the financial report definition window where users can add standard notes concerning financial reporting methods used on a particular report to comply with IFRS guidelines 3.23 and 8.2. Additionally, a NOTES button has been added to the "Financial Report" window that is enabled when a specific period is selected. When clicked, the user is presented with a dialog window where report definition and period specific notes can be written and saved. That will appear on printed versions of the report.
 
When a user elects to synchronize a child database with a parent, the application checks to make sure a currency conversion rate exists between the functional currency of the child and that of the parent for each transaction date to be imported. If any are missing, the process will stop and the user will presented with an error message describing the missing information. The conversion rates must be recorded in the parent database.
 
When all synchronization criteria are met, the synchronizer will import a copy of G/L transactions from the child database summarized by account, date and source into the parent's general ledger converted to the parent's functional currency. The parent database will keep a record of the conversion rate at the table level used for the import on the summarized transaction records. Whenever a period is re-synchronized, all data for that period and all subsequent periods for that company will be deleted and new data will be imported.
 
Trial balances are completely recreated on the parent database from the summarized import of G/L transaction data. This means that periods and years must be closed independently on the parent database from the child database. For this reason, account mappings that were previously defined at a global level including retained earnings, currency/gain loss and G/L discrepancy accounts must be defined at the company level to provide true company-independent trial balance support.
 
IFRS requires that transactions be converted to the parent functional currency "on the date of the cash flow," and that the difference between the value of assets and liabilities recorded on those dates, and the end of the period, be reported as unrealized gain/loss. The sum of these differences will be recorded as a system generated transaction entry using the "Unrealized Gain/Loss" account defined on company record. This will provide compliance with IFRS requirements 7.11-7.13. Rounding discrepancies caused by conversions will also be recorded transactionally using the G/L series discrepancy account defined on the company record in the parent database. All imported G/L transactions and transactions created by these automatic calculations will be visible the normal way on general ledger transaction reports.
 

Screen Samples

The screenshot below shows the G/L account mapping information that is available at the company level:
 
 
The following screenshot shows the location to enter notes for financial reports at the definition level. To create a new financial report, follow these steps:
  • Go to the "Accounting" module from the top menu
  • Select the "Financial Statements" submenu
  • Choose the "New Financial Report" option to create a new report
  • Row layout, column layout, and notes can be added to the new report:
 
This screenshot shows period specific notes being entered for a layout. Open the "View Financial Report" window and click the NOTES button from the menu:
 

Report Changes

All the standard OpenRPT report definitions for financial reporting included with xTuple have been updated to show both the layout notes and the period/layout notes including the column formatted, trend, and ad hoc versions: