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
- Jobs - MYOB
- Class & Location - QuickBooks
- Tracking Categories - XERO
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
- P&L Report on Companies Side by Side over different Date Ranges e.g. Month to date v YTD
- 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 "then by" (Column) Dimension from Months to Company to see Company in each column
- Change the Transaction Date range to your preferred period
- +/- Col to hide the Company ID and Year from the Column header if they display
- Learn how to use Pivot - To see Multiple Dimensions at once
P&L Report on Companies Side by Side in Columns and different periods run a Pivot Type Report
- Run the P&L Forecast report to see multiple time periods e.g. Month to date v Year to date
- Convert the Report into a Pivot to also see Companies as columns
- Pivot - To see Multiple Dimensions at once
- +/- Col to hide the Company ID and Year from the Column header
+/- Col to to select what columns to display e.g. Month to date v Year to date v Last year etc
Consolidation Elimination Entries
To record Consolidation Elimination entries you have a few options:
- Create a new MYOB Company file called Consol Entries, enter all elimination entries as journals in this one Company file. Then add this company as another company in Business Intelligence and include it in your you Group Companies. 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 Business Intelligence 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. 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) for example.
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