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.
6. Manual and Automatic Consolidation Process
Intercompany solution supports manual and automatic financial consolidation process.
- Manual Consolidation: Manual Consolidation enables user to perform consolidation manually on demand. To run the Manual Consolidation in the consolidation company, choose Consolidation-> Manual Consolidation.
- Automatic Consolidation: Automatic consolidation enables user to schedule the financial consolidation process at user defined frequency. Consolidation process is executed without any manual intervention. To setup the automatic consolidation process, choose Administration ->Setup -> Intercompany -> Consolidation – Automation
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.
- In User-Defined Fields window, go to Elimination (Row), choose Rule ‘Equal’ and select value ‘Y’ and Choose OK.
- Choose update in the expanded selection criteria window and Choose Ok in Document Journal – Selection Criteria window.
- Document Journal reports appears based on selection criteria.
From the Form Settings, display the Branch Code (Row), To Branch Code (Row) fields: - “Branch code” – represents the company originating the transaction
- “To Branch Code” – represents the company receiving the transaction
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.
Hi Peter,
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
thanks
Hi Smith,
I would recommend, you may go with the first option. Take the backup of live database and configure the same in the intercompany Solution to test the all scenarios.
Once your testing is complete then uninstall the intercompany solution, delete the intercompany Landscape database and perform the fresh installation of intercompany solution.
This will reduce the error chances and live user can work continuously on the live databases.
Thanks,
Pankaj Sharma
Hi,
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?
Hi Peter,
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.
Thanks,
Pankaj Sharma
Hi Pankaj,
What is the latest version for company Intercompany addon ? Is it compatible with SAP B1 9.2 Hana?
Regards,
Nick
Dear Nick, affirmative.
You can look up this info in SAP Note 1844555 - Troubleshooting in Intercompany Integration Solution for SAP Business One.
Best regards, Peter
Hi Peter
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?
Hi Adrian,
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
Thanks,
Pankaj Sharma
We are having a problem with the upload of journals into the consolidation company. Our consolidation company is Euros based. One of the companies is in Dubai and trades in AED and there are around 4 AED to 1 Euro. Unfortunately this company was set up with System currency of AED. From time to time a rounding journal is entered for 0.01 AED but when this is uploaded to the consolidation company it fails as the journal that it is trying to post is zero value. We don't want to change the decimal places as this would affect the reporting. Is there a way of not uploading these journals or of allowing zero value journals to be posted in the consolidation company?
Hi Adrian, your post will be kind of hidden here, not seen by anyone besides me. Please create a seperate question on it here: https://answers.sap.com/tags/01200615320800003530
Best, Peter
Thanks Peter - I had already posted there as well 🙂
Hi,
I have a few questions:
1. Elimination Entries.
Typically, we would eliminate the inter-company AR and AP, Intercompany revenues and expenses and intercompany stock ownsership (investement and establish minority interest).
Do these elimination entries happen automatically or system driven? or
These are done manually using system generated reports (eg. intercomapny sales and expense reports as basis for JE).
2. What's the purpose of Consolidation % (see attached image)?
Isn't it that if the investment from a subsidiary (51%-100% ownership) requires FS consolidation that line by line the accounts are simply added? The % of ownership is only to account for minority interest, is this the case?
3. Does the intercompany handle consolidation for Joint Venture?
Thank you.
Hi Eric,
1. Elimination entries need to pass manually by the user using system-generated reports. You can refer the section 7 “How to find the sales-type Intercompany transactions that need to be eliminated from the Consolidation Company” of this blog for more details.
2. The consolidation percentage indicates the percentage of shares that the organization holds in the company. The percentage value indicates how much of each account for this company should be included in the consolidation.
3. The intercompany solution allows you to proportionally consolidate (Using consolidation Percentage) into the group consolidation companies. You can create a Multi-level consolidation hierarchy based on business needs. For example, one company(X) can consolidate only 50% into the consolidation company at a lower level, and then this consolidation company and Company (Y) consolidated into a higher-level consolidation company.
Thank you,
Hi Pankaj,
Thank your very much for the reply.
I have 1 more clarification on #2.
So you mean to say if the Parent owns 80% of Subsidiary A that only 80% of the GL balance is consolidated?
Example if Subsidiary has $100 Cash and $100 Revenue only $80 cash and revenue will be consolidated?
I think this is wrong. 100% of the cash and revenue should be consolidated for ownership of 50% + 1 (general rule) to reflected as if they are 1 economic activity.
Part of the consolidating entries will be a manual JE to remove the parent’s investment account, subsidiary’s equity account and setup the minority interest. Something like this but the consolidated balances are simply added 100%.
Subsidiary Common Share xxx
Subsidiary Ret.Earnings xxx
Investemten in Subsidiary xxx
Non-controlling interest xxx
Thank you.
Eric
Hi Eric,
Yes, If 80% is defined in the consolidation percentage for a company in the intercompany Landscape, then during financial data roll-up only 80% value of the Debit/Credit side of journal entry will go into the consolidation company.
Thanks,
Pankaj Sharma
Hi,
When i perform Manual consolidation at Consol DB, click on Export from Branch, at the Status, i encounter error message-> Failure: At least one of the mapped accounts is not available in the Consolidation Chart of Account.
I am not able to know which account is not in consolidation COA, which screen can i check? Any idea?
I have setup 2 level of consol, this is level 1 consol. I have not mapped Consol1 COA to Consol2 COA, is this the cause of error? But i am trying to export branch JE to Consol1, between branch and Consol1 COA are mapped.
I have export Consol COA to branch and did the mapping in each branch company before i perform Export in consol DB. I have checked all branch COA is mapped to Consol COA, any screen that i can check this error, which account is not mapped?
Hi,
This is the same issue mentioned by you in another thread and it is resolved.
https://answers.sap.com/questions/13064651/interco-addon-export-from-branch-to-consol-db-erro.html?childToView=13080071#answer-13080071
Thanks,
Pankaj Sharma
Hi Pankaj,
I have been trying to analysis all the current aspect of the consolidation feature that currently Intercompany Integration 2.0 framework is offering. But I have a requirement which I would like to understand if it can be configured in the Company landscape. Please refer to below screenshot for
Would it be possible for me to do this configuration?
Thanks & Regards,
Kuldeep