Save Time by Combining Multiple Company Files Together
Multiple company files can be combined into a Group file and reported on as either a total consolidated group or showing individual companies totalling to the group. Data from each company is combined at transaction detail level and if in different currencies will be translated to the group Reporting Currency
BI4Cloud Business Intelligence can be used to combine the following data:
- Profit and Loss and Balance Sheet data from the GL
- Sales
- Purchases
- Jobs - MYOB
- Class & Location - QuickBooks
- Tracking Categories - XERO
- Receivables
- Payables
- Inventory
This article covers the following steps to setup and run consolidated reports:
- Report any data on Companies Side by Side using a Pivot Type Report
- Consolidation Elimination Entries
- Foreign Currency Translation
- Multi User Licence Required
- How to Setup a Consolidated Group
- Do I need to have a consistent Chart of accounts?
Report any data on Companies Side by Side in Columns using a Pivot Type Report
- Run a Pivot Type Favourite Report over any data e.g.
-
- P&L - Monthly is a Pivot with Months as columns
- Sales - Analyse Sales Monthly is a Pivot with Months as columns
-
- Change the "Pivot on" (Column) Dimension from Months to Company to see Company in each column
- Change the Transaction Date range in the Filters to your preferred period
- Learn how to use Pivot to see multiple dimension together
Pivot by Company
Pivot by Company - This Year v Last
- Run a Pivot Type Favourite Report over any data e.g.
-
- P&L By Mth TY v LY
-
- Change the "Pivot on" (Column) Dimension from Months to Company to see Company in each column
- Change the Transaction Date range in the Filters to your preferred period
Pivot by Company - Actual v Budget
- Run a Pivot Type Favourite Report over any data e.g.
-
- P&L By Mth TY v Budget
-
- Change the "Pivot on" (Column) Dimension from Months to Company to see Company in each column
Consolidation Elimination Entries
To record Consolidation Elimination entries you have a few options:
- Create a new Company file called Consol Entries, enter all elimination entries as journals in this one Company file. Then add this company as another company in BI4Cloud and include it in your Group Consolidation. The Consol entries will appear in your reports as a separate Column along with each of the other companies and the Total Group column will include the result of any Elimination entries.
- Export your Consolidation report to Excel from BI4Cloud where you can insert an extra column and enter in any Elimination entries.
- Filter to exclude Inter-company GL accounts - See Filter
- Tag Inter-company transactions then Filter to exclude from P&L, e.g. apply a job code or Tracking Option called say "Interco" to all inter-company Sales and Purchase transactions then simply Filter to exclude these transactions, or
- Tag Inter-company customers and suppliers then Filter to exclude these from Sales and Purchases reports, e.g. Assign any inter-company customers to a customer group called say "Interco" then simply Filter to exclude these customers from sales reports. Do the same for suppliers/vendors to exclude them purchases reports.
Foreign Currency Translation
Data from each company is combined at transaction detail level and if in different currencies will be translated to the group Reporting Currency . P&L accounts are translated at the average monthly exchange rate.
Multi User Licence Required
To access the Consolidation feature you will require a Multi User BI4Cloud Licence. For example a 3 User BI Licence will allow you to have 3 Users and let you connect 2 Company files + the Group Company (2+1=3).
How to Setup a Consolidated Group
See this link for instructions on Multiple Company Consolidation Setup
Do I need to have a consistent Chart of Accounts (COA)?
The group consolidation works out of the box if you use consistent codes in all of your Company files:
- GL Account Codes
- Item Codes
- Customer Card ID
- Supplier Card ID
If your GL Chart of Accounts is not consistent across company files then you can map them and report using the following Cool Stuff features:
-
- Account Alignment - Select one Company as the Master and map individual accounts from each company to the Master.
- Flexi Levels to Reorganise Sort and Group your Accounts - Streamline the COA further by creating your own Account Groups in BI4Cloud and drag and drop accounts using Flexi Levels