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.

Brief Explanation

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

• Enable multi-company consolidation in 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.

All child databases must share the same base currency as the parent.

Multi-company financial consolidation is only available on xTupleERP Standard Edition or higher. Synchronization may be conducted between a Standard, Manufacturing or Enterprise edition parent database and any child edition of xTupleERP, including 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

Terms and Definitions

• Child Database
• Parent Database
• Synchronize
  • A database where general ledger transactions for affiliated companies are generated and maintained.
  • A database where account and trial balance data from child databases is copied to. Child data can not be altered at the parent
  • 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 (FRE) in the xTuple Accounting module.

In addition, 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. 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 to enable consolidated financial reporting.

The set up for multi-company consolidation is as follows:

Configure Accounting

Multi-company consolidation must be enabled on the parent database. This can be done in System > Configure Modules > Accounting. The check box titled "Enable External Company Consolidation" must be checked. This option is only available on xTupleERP Standard Edition or higher.

Company Number

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

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, the user will open a window called "Synchronize Companies." It may be found under Accounting > Utilities and will only be visible when external company consolidation is enabled--and only if users have appropriate permissions.

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.

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

Example:

This image shows an income statement that has consolidated two companies: the first with general ledger accounts having the 01 prefix, the second having the 02 prefix.

... and the same report in printed form:

Multi-Currency Support

xTuple Standard Edition will be enhanced to allow synchronization of financial data between databases where the currency of the parent database is different from the child.  When data is imported to the parent from a child of a different currency, the general ledger transactions of the child must be converted to the functional currency of the parent using the conversion rates based on the date of the transactions.  The difference between currency rates on the date of the trasaction on the values at the end of the period for assets and liabilities should be recorded as unrleazed gain/loss transactions.

Functional Requirements

• Add standard notes to the financial report definition that print on reports.
• Add period specific notes to a financial report and period that print on reports.
• Import financial data from a child database general ledger to a parent while converting to the parent functional currency.
• Report Unrealized Gains/Losses for asset and liability accounts that result from transaction to end of period currency fluctuations on a specific account.
• Differences in rounding during the conversion process that would cause an imbalance should be rectified using the G/L Discrepency account.
• Support will need to be added to set Retained Earning Accounts, Currency Gain/Loss acounts and G/L Discrepency Accounts by Company on the parent database.

New Terms and Definitions

• IFRS:  International Financial Reporting Standards.  These are used as the guidlines 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 new G/L account mapping will be added to Company maintenance for "Unrealized Gain/Loss" durring financial consolidation.  This mapping will only be available on Standard Edition (aka commercial) 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.  

Window changes

Report Changes

Release Considerations

This functionality is targeted for version 3.7 of xTuple ERP.