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.
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.
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, 199, "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
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.
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.
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.
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.