Share on facebook
Share on twitter
Share on linkedin

Xero Consolidation Reporting Multiple Accounts into Power BI and Excel

The Xero accounting system is getting widely accepted in larger organisations with requirements to create consolidated financial statements for multiple Xero companies. In this article I would like to explain how you can completely automate the consolidation process and add  analytics options with Power BI or create dynamically updating reports in Excel.
There are a variety of web apps out there to address this requirement, but these typically are rigid and cannot compete with the analysis functionality in market leading analytics platforms. The approach that we are covering here is based on an enterprise grade data warehouse approach using Microsoft’s Azure SQL Server where all Xero data is stored securely in the cloud.

Advantages of Multi-Account Xero Consolidation

• You have a full backup of your data in a format that can be accessed by nearly every client tool from Power BI, Excel, Tableau, Qlik to custom built solutions
• The platform is optimized for reporting and analytics requirements and enables the fastest possible query times
• The platform is nearly infinitely scalable as the cloud capacity can be instantly adapted to the needed capacity
• You have the option to choose in what jurisdiction your data is deployed and have full control over it

Acterys enables you to automate the load for any number of Xero companies to the Azure SQL data warehouse and provide you with all requirements necessary for consolidation. Here are the necessary steps

Xero Consolidation Reporting In A Few Steps


1. Sign up and start a free Acterys trial with an expert at your side

adding Xero company for consolidation

2. Add one or many Xero companies or accounts for consolidation

Acterys Power BI Apps QuickBooks Xero WorkflowMax

3. Install the Acterys Power BI App or the Acterys Excel Template

Account Mapping Xero Power BI

4. Maintain account mappings e.g. map single accounts to a group chart of accounts

a. Details of multi-currency conversion: automated integration of currency services or manual maintenance
b. What FX rates (spot, historic) to use for what account types

6. Configuring your preferred method for inter-company elimination.

Acterys Xero consolidation and reporting tool is the only solution the enables you to use 5 different methods -that can be used in combination- and in most cases completely automate the process of eliminating transactions:
a. By specifying dedicated inter-company accounts
b. Specifying inter-company contacts (setting a flag per group company that will automatically eliminate transactions between them)
c. Using Xero Tracking Categories for inter-company transactions
d. Manual adjustments as required

Inter-Company Elimination For Xero
7. Customizing your reports with the front end tool of choice (Power BI, Excel, other options)

Easy Way to Consolidate Xero to Power BI

One of the biggest benefits of this approach is that consolidation is just one of many aspects that the solution covers. It can be easily extended with other business requirement from planning and budgeting to project management and the addition of nearly any data source.
The shown approach natively supports widely used authentication standards like Microsoft accounts and active directory and instantly integrates with Office Productivity solutions.
Finally, the option to easily extend Acterys with new no code app development platforms like PowerApps and workflow automation like Microsoft Automate enable you to realize nearly any workflow requirement from approvals to email notifications.
Please feel free to contact us for any questions or start a trial for consolidating Xero companies and accounts.

Recent Posts

Top 5 Tips & Tricks For Planning, Budgeting and Forecasting in Power BI & Excel In this blog I am

Planning and Analytics [Infographic]

It is not uncommon for companies to have separate planning and analytics processes or legacy solutions that result in more

Materialize and Synchronize Power BI with a Data Warehouse “Materialize” is a database concept that refers to storing the result

I can’t help smiling when I see legacy BI/Planning application vendors -that were not so long ago laughing away Power BI- coming out with their own “Power BI Integration” after Microsoft has turned around the industry. These are more often than not nothing but “a marketing spiel” to build on Power BI’s awareness and of very limited use in real life application scenarios.

Across many mid-sized and large companies, finance and operations teams are at crossroads. This was one of the findings from the recent BPM Pulse 2021 survey which revealed that only a third of companies had truly harmonized strategic, financial, and operational plans.