Excel Consolidated Financial Reporting for Xero and QuickBooks

Hot Linked Financial Reporting in Excel

Consolidated XL provides a powerful set of simple Excel functions, used on Mac, PC and Web Excel to facilitate Consolidated Financial Reporting, and Analysis, by hot linking one or more cells, in your sheet, to your chart of accounts in QuickBooks or Xero using simple to master functions.

Hot Link Excel to your Accounts

Create Financial Wizardry - Flexibility, not rigidity!

Consolidated/Bespoke Financial Reports

From one or more Xero, QuickBooks or Sage Accounting companies on PC, MAC or Web Excel 365.

Save Time - Use Consolidated XL Wizards

Using our 10+ wizards, you can quickly create Hot Linked Excel reports from one company or consolidate multiple companies. More about Wizards

Quick Charts to Visualise Data

Click on CXL values in the sheet to analyse Sales and Cost trends visually in the Consolidated XL Quick Charts tab. More about Drill Down

Online Training

Extensive online training is provided to help you understand CXL functions. More about Online Training

F.A.S.T.

The FAST database (Financial Accounting Summarization Table) summarizes financial data by years, quarters, months, and weeks. Providing FAST data retrieval for the CXL functions.

Your Secret Wand for Financial Wizardry

Use the 60+ CXL Excel functions to generate your clients/business's ultimate Financial Management Reports and Cash Flow Reports.

Differentiate your Financial Skills from the crowd.

Example of CXL Functions

Granular Functions - One value returned

Pull General Ledger, Customer, Vendor,and Stock data directly into Excel — summarised, filtered, and always in sync, with drill down to transactions and trend graphs.

For GL Account "200", in COMP_A, sales by period: GL examples
Current year turnover:	=CXL.GLVal( "COMP_A", "200" )
Year 2023 turnover:	=CXL.GLVal( "COMP_A", "200", 2023 )
Year 2023 period 1 turnover:	=CXL.GLVal( "COMP_A", "200", 2023, 1 )
Year 2023 turnover filter by Rep MARK:	=CXL.GLVal( "COMP_A", "200", 2023, 0, "MARK" )
Year 2023 period 1 to 6 turnover:	=CXL.GLVal( "COMP_A", "200", 2023, 106 )
For Total SALES and PROFIT, in COMP_B, sales by period:
SALES Year 2023 period 1 turnover:	=CXL.GLVal( "COMP_B", "#SALES", 2023, 1 )
GROSS PROFIT Year 2023 period 1 turnover:	=CXL.GLVal( "COMP_B", "#CalcGrossProfit", 2023, 1 )
SALES 2023 period 1 to 6 turnover:	=CXL.GLVal( "COMP_B", "#SALES", 2023, 106 )
PROFIT for Rep Mark in 2023:	=CXL.GLVal( "COMP_B", "#CalcNetProfit", 2023, 0, "MARK" )
See Major Headings for these '#' codes. #OTHERINCOME,#COS,#OVERHEADS,#BANK, #ASSET, #LIABILITY, etc.
For GL Account "200" , in COMP_A, sales between Dates:
Feb 10 to March 20 turnover:	=CXL.GLValDate( "COMP_A", "200", 10 Feb 2023, 21 March 2023 )
Get Balance in COMP_A:
For all Banks:	=CXL.GLVal( "COMP_A", "#BANK" )
Bank Account GL 21010:	=CXL.GLVal( "COMP_A", "21010")
All Liabilities:	=CXL.GLVal( "COMP_A", "#LIABILITY")
Get GL Account name, from COMP_A: GLName examples
Get name:	=CXL.GLName( "COMP_A", "200" )
Stock Item PROD1, in COMP_B: Stock examples
Year 2023 period 1 sales turnover:	=CXL.ItemSalesVal( "COMP_B", "PROD1", 2023, 1 )
Qty Sold period 1 to 6 turnover:	=CXL.ItemSalesQty( "COMP_B", "PROD1", 2023, 106 )
Year 2023 profit margin:	=CXL.ItemProfitMargin ( "COMP_B", "PROD1", 2023)

Spill Functions - One function returns a report

Pull Profit and Loss, Age Debtors/Credits, Stock Turnover, Customer/Vendor Transation report, etc.

Get GL Account Code List from COMP_A: Code Spill examples
Heading (P&L order), Code order:	=CXL.CodesSpill( "COMP_A", "GLCodesByHead" )
2 consolidated companies list:	=CXL.CodesSpill( "COMP_A,COMP_B", "GLCodesByHead" )
by code order for selection list:	=CXL.CodesSpill( "COMP_A", "GLSelectCodes" )
Spill Profit and Loss Report, from COMP_A, from one function: GL Spill examples
Profit and Loss Report from one function:	=CXL.GLValSpill( "COMP_A", "#ReportPL^>" )
By Rep MARK, show last 2 years and 6 periods:	=CXL.GLValSpill( "COMP_A", "#ReportPL^>", 2, 6, "Mark" )
See Calculated Headings for these '#' codes. #ReportBAL,#CatCOS,#CatOVERHEADS,#CatSALES,#CatCurLiab etc.
Spill Aged Debt Report, from COMP_A, from one function: Aged Receivable/Payable examples
Age Summary by 10 days intervals, 8 columns:	=CXL.AgeDebtTotalSpill( "COMP_A", "Receivable", 10, 8 )
All Outstanding Receivable Transactions:	=CXL.ContactTransSpill( "COMP_A", "Receivable" )
Spill Top Stock Sales, from COMP_A, from one function: Stock Item Spill
2 years totals, last 6 months:	=CXL.ItemBySpill( "COMP_A", "Sales", 2, 6,"","",20 )
For Rep MARK, in Calander Quarters:	=CXL.ItemBySpill( "COMP_A", "Sales", 2, 6,"MARK","",20,"CQ" )

All by Financial Period, Calendar Month, Week, or Date (GL only). Filter by Tracking 1/2 or QuickBooks Class or Location

Functions Reference Manual

Consolidated XL - Your Secret Wand for Financial Wizardry

5 day free trial | MS Excel for PC, MAC or Web | Xero, Sage and QuickBooks Online

Getting Started

Kick Start with Wizards

Standard Wizards:

Get GL Values Example - how to get GL data various ways.
Balance Sheet - Single or Consolidated Company
Sales Report + Analyse by Cost Centres
P&L / Income Statement + Analyse by Cost Centres

Pro Wizards:

Multi-Company Dashboard - Single or Multi-Company
Age Debt/Forecast Payment - Single or Multi-Company
Stock Turnover/Profit for selected Company
Customer/Vendor Turnover for selected Company
Invoices, Stock Lines and Journals for selected Company

With our 10+ wizards, you can quickly create the above Excel reports from one company or consolidate multiple companies. Optionally broken down into Cost Centres.

Customize, add charts, additional data, conditional formatting, formatting, notes, and establish your own identity or unique financial service.

Create Financial Wizardry in seconds!

A video tells a 1,000 words!

Because seeing is understanding.

Click here for demo videos

60+ XL Excel Functions

Use our functions in your own management reports, simply open your own bespoke Reports created in a workbook and Excel will refresh data in seconds with accounting data synced daily.

Standard Functions:

Company Information Get Name, Last Sync Date...
GL Codes and Headings and Names Spill *
GL Period Convert Year and Period
GL Name Get GL names
GL Turnover/Balance to Spill *
GL Turnover/Balance or Credit/Debits for Period
GL Budgets Get Budgets or just keep them in Excel

Pro Functions:

Age Receivable/Payable + Predicted Age Analysis Spill *
Get Cust/Vendor Turnover to Spill *
Get Stock Qty/Turnover/Profit to Spill *
Get Transactions/Journals to Spill *
Get Customer/Vendor Info and Customer/Vendor Turnover
Get Stock Item Info Info, Qty, Turnover/Profit analysis
Get Currency Info Get Name, Rates...

* - Spill functions returns many Rows and Columns using one function.

Training - A guide of how to use CXL functions

Online Training using demo companies always available | Training Video

Online Training

Quick Charts and Drill Down

Click on a CXL figure in the Excel sheet and select "Charts" to see a Quick Chart relating to the selected cell. Or select "Trans" to show Transaction which make up the current figure, and drill down to Transaction Detail.

General Ledger:

History - Shows 6 Months Weekly, 2 yrs Monthly, 3 yrs Quarterly, 5 yrs Year history column chart.
Year on Year - Shows 6 Months Weekly, 2 yrs Monthly, 3 yrs Quarterly, 5 yrs Year history column chart with 3 years shown side by side for comparison.
Segments - Shows a Pie Chart for the last 5 Periods showing all Segments with in the current GL Heading.
Cost Centres - Shows a Pie Chart for the last 5 Periods showing all Cost Centres breakdown for GL Account.

Age Debt:

Age Debt - Shows Future and outstanding debt in column chart.
Forecast Debt- Shows when payments will be made graphically.

Stock/Contact:

History - Shows 6 Months Weekly, 2 yrs Monthly, 3 yrs Quarterly, 5 yrs Year Turnover history column chart.
Year on Year - Shows 6 Months Weekly, 2 yrs Monthly, 3 yrs Quarterly, 5 yrs Year Turnover history column chart with 3 years shown side by side for comparison.
Cost Centres - Shows a Pie Chart for the last 5 Periods showing all Cost Centres breakdown for Stock Item.

More information on Transaction Drill Down

Other Consolidated XL Features

Consolidated XL has many great features:

Cross Platform

Excel functions, for Mac, PC and Web Excel 365.

Up to date

The data is automatically synced every night, at the hour of your choice, so you have the latest figures at your fingertips, with manual data sync in seconds.

5 day free trial

Want to get start, then download, connect to data, sync, and start creating financial art.

3 Example Companies

As a demonstration and tutorial tool, Consolidated XL provides three examples of demo companies.

Fast Calculation

Consolidated XL maintains a FAST Financial Summary (Financial Accounting Summarization Table) based on years, quarters, months, and weeks, updated every night. Providing the CXL functions with FAST data retrieval.

Multiple Accounts Packages

For Web based accounting packages such as Xero, QuickBooks or Sage Accounting.

Security

Your accounting data is held in a separate encrypted Database for added security.

Easy Reconciliation

Consolidated XL functions can return analysis at a group level, such as total Revenue, Cost of Sales, Liabilities, Assets, Equity. So, you can easily reconcile your analysis.

Auto Generate

Use Consolidated XL wizards, which at the click of a button are the sheet with your Financial Data, to get you up and running fast.

Build Once and Recalculate

Once created, simply reopen the CXL sheet to refresh with the latest data, charts, etc.