Consolidate Multiple Company Files

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 - QBO
  • 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 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

Consolidation.png

 

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

    P&L Forecast - Ultimate Month End

  • 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

Consol_-_multip_period.png

 

Consolidation Elimination Entries

To record Consolidation Elimination entries you have two options:

  1. 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.
    or
  2. Export your Consolidation report to Excel from Business Intelligence where you can insert an extra column and enter in any Elimination entries.

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:

 

 

BI4cloud.com 

 

 

 

 

 

Was this article helpful?
0 out of 0 found this helpful

Articles in this section

Ask the BI4 Community
Need help using this report or feature?