Intercompany integration solution for SAP Business One – Financial Consolidation

Financial Consolidation Processing

Operational visibility and risk management are key considerations for any business with multiple operational units. The corporate financial controller needs an accurate and timely rollup of GL accounts for financial consolidation. Intercompany Solution provides in-depth insight in to consolidated financial data across the organization without intensive manual reconciliation or duplicate data entry.

The Intercompany consolidation feature broadly makes use of the base SAP Business One functionality to present the consolidated financial statements. User need to set up additional companies in SAP Business One, one per level of consolidation. The new “consolidation” company is used only for financial consolidation reporting.

In this article, we are covering following broad sections on financial consolidation process:

When to choose consolidation source as Local Currency or System Currency in Intercompany Landscape.

How to setup Multi-Level financial consolidation

Usage of consolidation translation method

Consolidation Account Mapping

When are exchange rate gain or loss accounts triggered during the consolidation processes

Manual and Automatic Consolidation Process

How to find the sales type Intercompany transactions that need to be eliminated from the Consolidation Company

How to filter reports by Branch and Group Code

1. When to choose consolidation source as local currency or system currency in Intercompany Landscape.

The consolidation source field in the landscape indicates whether the financial data for consolidation is retrieved from the branch company in the company’s local currency or in its system currency.

If you choose local currency in the company details for a branch company, the system picks the value from the debit (LC) and credit (LC) of journal entry from the branch company and defaults the value in consolidation company debit (LC) and credit (LC) fields of journal entry. If Branch Company and Consolidation Company have different local currencies, the application applies the exchange rates based on translation method applicable to G/L accounts in the branch company.

If you choose System Currency in the company details for a branch company, the system picks the value from the debit (SC) and credit (SC) of journal entry from the branch company and defaults the value in consolidation company debit (LC) and credit (LC) fields of journal entry. In this case, system assumes that the system currency of Branch Company is same as the local currency of Consolidation Company.

Depending on reporting requirement, user can set the consolidation source in the landscape for the branch Company.

2. How to setup Multi-Level financial consolidation

Multi-level consolidation hierarchy can be setup based on the organizational structure. Intercompany Solution allows for reporting at any of the level keeping in consideration the minority interests of the participating companies. There is no limit to the number of companies you can consolidate. Account balances can be rolled up to a group level within the consolidation hierarchy

The structure of the hierarchy depends on the business scenario. In the example shown above, you can see that ACME RIO (100%), ACME SAO (100%) and ACME Texas (30%) is consolidating in to the Group Consolidation company. ACME Texas (70%), ACME California (100%), ACME New York (100%) and Group Consolidation (100%) is further consolidating in to organization consolidation Company.

From the above example, let’s discuss how to setup ACME California and ACME Texas branch companies for multilevel consolidation.

First, user needs to set up two additional companies in SAP Business One – Group Consolidation and Organization Consolidation, one for every level of consolidation and define the consolidation percentage and consolidation source against the company to which Branch Company is consolidating.

ACME California company is consolidating in to Organization consolidation company. To achieve this define consolidation percentage as 100 against the ORG-CON (Organization Consolidation) Consolidation company in landscape. Refer image below.

ACME Texas company is consolidating in to both consolidation companies i.e. 30 % consolidation in Group consolidation and 70% in to Organization consolidation. To achieve this, define consolidation percentage as 70 against the ORG-CON (Organization Consolidation) Consolidation company and 30 against the GROUP-CON (Group Consolidation) Consolidation company in landscape. Refer image below.

At any given time, you can check the consolidation hierarchy setup in the system by clicking the consolidation hierarchy button available in the intercompany landscape.

3. Usage of consolidation translation method

If the local currency of the branch company is different than the local currency of the consolidation company, the system uses translation methods for exchange rates while translating the account balances from branch to Consolidation Company.

The following three translation methods are available in the system:

Historical Rate – The system will use the exchange rate defined for the date of the journal entry.

Closing Rate – The exchange rate defined for the closing date of the consolidation period. For example, if consolidating between Jan 1st and Jan 31st, the closing rate will be the exchange rate for Jan 31st.

Average Rate – A user-defined exchange rate is set in the Average Rates window.

Exchange rates for translation are determined from the consolidation company.

Historical and closing rates are determined from the Exchange Rates and Indexes window of SAP Business One. To define Average Rates, press Define Average Rates button on exchange rates and indexes window and define average rates there.

4. Consolidation Account Mapping

You prepare the consolidation chart of accounts for generating financial reports, either to meet the requirements of statutory authorities or to accommodate corporate-internal reporting requirements.

Follow the steps below for consolidation account mapping:

Once the consolidation chart of accounts is ready, you distribute it to the branch companies.

To distribute the chart of account in the consolidation company, choose Administration -> Setup ->Intercompany -> Chart of Account Distribution.

After you have distributed the consolidated chart of accounts to the branch companies, you need to go into each branch company to specify the mapping of the branch accounts to the consolidation accounts.

For each active G/L account in each branch company, you must specify an active G/L account in the consolidation chart of accounts to which the balance will be transferred on consolidation.

The account mapping is performed using the Consolidation Account Mapping window in the Consolidation module in each branch company.Please note this step must be completed in all companies that will consolidate into the consolidation Company.

If the chart of accounts structure in a branch company is identical to the chart of accounts in the consolidation company, choose the Auto Map Account option to automatically map the G/L accounts to the consolidation chart of accounts.

After consolidation account mapping you will be able to export financial data from the branch companies and import it into the consolidation company.

5. When are exchange rate gain or loss accounts triggered during the consolidation process.

Intercompany solution uses exchange rates gains and losses accounts in the system when consolidation source is set to local currency in the landscape for the branch company and the local currency of branch company is different than the local currency of consolidation company. You can define the exchange rate gains and loss accounts in consolidation account setup window (Administration -> Setup -> Intercompany -> Consolidation Accounts Setup) in the consolidation company.

While doing Journal Entry posting in consolidation company, when sum total of debit side is greater than sum total of credit side for the journal entry, then system post this value in to the exchange rate gains account as credit.

While doing Journal Entry posting in consolidation company, when sum total of credit side is greater than sum total of debit side for the journal entry, then system post this value in to the exchange rate loss account as debit.

Once the financial data from all branch companies has been imported into the consolidation company using either manual or automatic consolidation process, user can run the standard SAP Business One financial reports in the consolidation company to view the following consolidated financial Statements:

Consolidated trial balance

Consolidated profit and loss statement

Consolidated balance sheet

7. How to find the sales type Intercompany transactions that need to be eliminated from the Consolidation Company

In the consolidation company, user can run the document journal report to find the intercompany transactions that need to be eliminated in Consolidation Company.

To run the report, follow the below steps:

In the SAP Business One Main Menu, choose Financials –>Financial Reports –>Accounting –>Document Journal.

Choose select All and choose Expanded Button. In the expanded selection criteria window, choose User-Defined Fields check box and choose the button next to User-Defined Fields.

This report provides the detail of intercompany sales transactions with information of transacting branch companies. User can create the elimination journal entries with the help of intercompany sales data provided in this report.

Please note this reporting will be available only on intercompany solution version 2.0 PL 00 and above, if any customer upgrades from the old intercompany version then this data will not be available for the transactions posted on old version.

8. How to filter reports by Branch and Group Code

You can view the data of an individual company by filtering the SAP Business One financial reports in the Branch Code field of the consolidation company.

To view the Trail balance report on branch code, follow the steps below:

In the SAP Business One Main Menu, choose Financials –>Financial Reports –>Financial–>Trail Balance.

Choose select All and choose Expanded Button. In the expanded selection criteria window, choose User-Defined Fields check box and choose the button next to User-Defined Fields.

In User-Defined Fields window, go to Branch Code (Row), choose Rule ‘Equal’ and select any branch code and Choose OK.Please note to view the report of entire group in case of multi-level financial consolidation, you can select the Branch Code of that group to view the report.

Choose update in the expanded selection criteria window and Choose Ok

The data appears will be filtered based on the selected branch code (group code). Similarly, you can filter other consolidated financial Reports.

(This blog content was created by the development team of the intercompany integration solution for SAP Business One)

Find a list of all blogs on Intercompany integration solution for SAP Business One in this central blog.

Assigned tags

Related Blog Posts

Related Questions

Thanks for publishing this article which is very useful. One question I have about the consolidation is how best to setup a test environment and test. Do you normally create a separate consolidation structure with test companies. For example you can create TESTCOSOL, TESTBRANCH1,TESTBRANCH2 that are copies of the live companies and only deploy the test databases. Once tested un-tick the above companies in deploy screen and use CONSOL,BRANCH1.BRANCH2 to deploy the live entities. Another approach is to backup the live companies and test with transactions and then roll back to the pre-transaction backups – not sure if this works. Any thoughts are welcome

Test databases are an important question, I am wondering how to use them when Intercompany is already deployed.

Is it a good idea to have Company Templates also for testing purpose, assigned to the test companies in the landscape, and limiting master data replication with Global Data Distribution settings in order to happen separately between test databases and production databases?

Meanwhile I have found out, that unfortunately it seems to be impossible to have a test system for Intercompany, because may original idea would not be working due to the fact that assigning a distribution template to a Busines partner, item or anything else is done forever, cannot be modified later.

I anyone having Intercompany working also with his test databases? If yes, how to do? Am I the only one to be missing this opportunity?

Currently, it is not possible to work with the test and live environment of intercompany together. The master data replication and transaction replication can not be controlled based on the system type.

I am in the process of consolidating companies going back to 2014 and thought I had all the posting periods set up ok for Document Dates and Due Dates. However a ‘rogue’ journal in one of the companies had a Document Date outside the range of the Posting Period and has caused an import failure. I had exported several months of data for this company in one go and it failed on the 4th of 5 export instances. I have fixed the issue in the Posting Period and my question now is if I re import the existing export file will it recognise that instances 1-3 have already been posted and only import 4 and 5 or will it re import the existing journals again?

When user re-import the financial data in the consolidation company, the system checks each journal entry before importing into the consolidation company. If journal entry exists in the consolidation company then system ignore that journal entry otherwise it imports.

The user doesn’t need to worry about the duplicate data, the system is designed to handle such type of scenarios