- App Store
Multi-Company Financial Consolidation
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.
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
- 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.
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.
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:
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.
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.
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.
For accurate reporting, users of this functionality should A) 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 resynchronize the parent and wonder why the consolidated statement on the parent doesn't reflect the change.
Because trial balance records are already summarized information, there should be nominal load requirements even when synchronizing across a WAN.
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:
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.
Functionality should work in accordance with IFRS 2009 SME guidelines (www.ifrs.com) for multiple company financial reporting. That specification is attached to the driving feature request for this development #12351; specifically sections 3.23, 7.11-7.13 and 8.2 apply to this feature.
Additional facility should be added to:
- 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.
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.
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 will be 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. In addition a "Notes" button will be added to the Financial Report window that will be enabled when a specific period is selected. When clicked the user will be 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 will check 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 the 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 will be 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 Discrepency 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 discrepencies caused by conversions will also be recorded transactionaly using the G/L Series Descrepency 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.
All of the changes listed above strictly concern internal workings with regards to the syncronization process. Other than the additional setup requirements and independent closing of periods on the parent, the actual execution of the synchronazation procedure and subsequent trial balance and financial reporting will be unchanged from previous versions.
The screen shot below shows new G/L Account mapping information that will be available at the company level:
The screen shot below shows the location to enter notes for financial reports at the definition level:
The screen shot below shows period specific notes being entered for a layout:
All the standard OpenRPT report definitions for financial reporting included with xTuple should be updated to show both the layout notes and the period/layout notes including the column formatted, trend and ad hoc versions:
This functionality is targeted for version 3.7 of xTuple ERP.
Tue, 03/10/2009 - 11:00#1
I still strongly feel that this development is slightly off mark.
The requirement for Mantis '6200 Year end equity and other global accounts need to operate by company' should not be necessary. What would happen if the parent company starts a new company mid way in a financial year.
For Example : Parent company starts another wholly owned subsidiary on 1st June 2008. But due to some reasons the Subsidiary is closed as on 2008-11-30. Would it be possible to consolidate this new companies (subsidiary's) Trial Balance and Balance Sheets? I am asking this because the Subsidiary's Financial year will start from 2008-06-01 (and not 2008-01-01) and end as on 2008-11-30.