Consolidated XL
by Comtek Accounts Ltd
Hot Link Excel to your QuickBooks or Xero Chart of Accounts,Sales,Stock, and Debtor values to 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 QuickBooks or Xero using simple to master functions.
Xero ==> Consolidated XL + F.A.S.T. ==> Hot Link Excel sheet
Quickbooks ==> Consolidated XL + F.A.S.T. ==> Hot Link Excel sheet
BENEFITS:
• Create Bespoke Custom Excel Reports Hot Linked to your QuickBooks or Xero data
• Consolidate Multiple Companies
• Refresh on Open
POSSIBLE REPORTS:
• Your own Company Dashboard
• Sales By Periods Analysis and by Cost Centres, Tracking Codes, Class and Locations
• Income Statements Or Profit & Loss
• Budget versus actual analysis
• Balance sheet Current Balance
• Age Debt Analysis and Forecast future Payments for Cashflow Analysis
• Customer Turnover by Periods
• Stock Turnover by Periods
• Stock Margin and Profit by Periods
• Cashflow
• Currency Variance in QuickBooks or Xero
• Journal details between two dates
• Invoice details between two dates
• Stock movement details between two dates
WIZARDS TO QUICKLY CREATE REPORTS
Use one of our 10+ wizards to quickly create the above Excel reports, customise, or use the 60+ CXL functions we provide to create Financial Wizardry to create Consolidated Management reports, which are optionally broken down into Cost Centres.
Customize, add charts, additional data, conditional formatting, formatting, notes, and establish your own identity or unique financial service.
Flexibility, not rigidity!
BUILD ONCE AND RECALCULATE
Once created, simply reopen the CXL sheet to refresh with the latest data, charts, etc.
QUICK CHARTS TO VISUALISE DATA
Click on CXL values in the sheet to visually analyse sales and costs in the CXL Quick Charts tab to visualise the data and trend analysis quickly. Copy and paste the graph into an e-mail, and send it quickly to the client or boss for discussion.
ONLINE TUTORIAL
A complete online tutorial, with videos, which goes through creating reports and customising the reports generated by wizards.
See consolidatedXL.com/start for tutorials, in depth video demos and reference guide.
DRILL DOWN
Click on CXL values in the sheet to drill down into related transactions and transaction details.
F.A.S.T.
A FAST (Financial Accounting Summarization Table) summarizes all financial information by financial year, quarter, period and week. As a result of this FAST database, Consolidated XL performs very quickly. As Scotts Manley once said, "Better to calculate before flight than during, for data on rails to the Moon and back" for fast reporting and analysis.
Xero - Tracking Codes 1/2
The FAST database summarises turnover by Tracking Codes 1/2 in Xero. Therefore, CXL will return filtered Cost Center figures at optimal speed.
QuickBooks - Classes and Locations
The FAST database summarises turnover by Class and Locations in QuickBooks, and Cost Center and Department in Sage. Therefore, CXL will return filtered Cost Center figures at optimal speed.
SUBSCRIPTION
5 days evaluation + 3 Demo Companies always available for testing, evaluation and training + Monthly Subscription can be cancelled at any time.
SECURE
Your data is encrypted and Securely held in Azure MS SQL Databases.
DAILY SYNC
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.
FUNCTION EXAMPLES
In the following examples we are going to get data from our examples company which has the code "ZQBO01 or ZXERO01"
Hot Link Single values:
The function "CXL.GLVal()" will Hot Link a single GL, or total, value for a period, whole year or YTD, by Financial/Calander Period, Quarter or by Weeks optionally filtered by Tracking code 1/2 from the F.A.S.T database.
For GL Account "200" Sales:
Year 2023 turnover: =CXL.GLVal( "ZQBO01 or ZXERO01", "200", 2023 )
Year 2023 period 1 turnover: =CXL.GLVal( "ZQBO01 or ZXERO01", "200", 2023, 1 )
YTD 2023 filter by Rep MARK: =CXL.GLVal( "ZQBO01 or ZXERO01", "200", 2023, 0, "MARK" )
and for the previous year: =CXL.GLVal( "ZQBO01 or ZXERO01", "200", 2022, 0, "MARK" )
Year 2023 turnover filter by Rep MARK: =CXL.GLVal( "ZQBO01 or ZXERO01", "200", 2023, 199, "MARK" )
Period 199 = total for year, 0 = YTD based on current period, 104 = Periods 1 to 4
Get Calculated Totals Net Profit:
Year 2023, filter by Rep MARK: =CXL.GLVal( "ZQBO01 or ZXERO01", "#CalcNetProfit", 2023, 199)
Year 2023, filter by Rep MARK: =CXL.GLVal( "ZQBO01 or ZXERO01", "#CalcNetProfit", 2023, 199, "MARK")
Periods 1 to 6: =CXL.GLVal( "ZQBO01 or ZXERO01", "#CalcNetProfit", 2023, 106, "MARK")
Periods 7 to 12: =CXL.GLVal( "ZQBO01 or ZXERO01", "#CalcNetProfit", 2023, 712, "MARK")
Total Sales: =CXL.GLVal( "ZQBO01 or ZXERO01", "#SALES" )
Total Sales for MARK: =CXL.GLVal( "ZQBO01 or ZXERO01", "#SALES", 2023, 199, "MARK" )
Total Overheads: =CXL.GLVal( "ZQBO01 or ZXERO01", "#OVERHEADS" )
for MARK: =CXL.GLVal( "ZQBO01 or ZXERO01", "#OVERHEADS", 2023, 199, "MARK" )
Current Balance:
Bank 090: =CXL.GLVal( "ZQBO01 or ZXERO01", "090" )
Total in all Bank Accounts: =CXL.GLVal( "ZQBO01 or ZXERO01", "#BANK" )
Total Liabilities: =CXL.GLVal( "ZQBO01 or ZXERO01", "#LIABILITY" )
Currenct + Long Term: =CXL.GLVal( "ZQBO01 or ZXERO01", "#CalcTotalLiab" )
Hot Link Multiple Values:
The function "CXL.GLValSpill()" will Hot Link multiple GL values or sections of the chart of accounts. Or the whole income statement with one function. Also, return multiple columns aged by weeks, periods, months, quarters or years. One function can hotlink the last 12 months of turnover broken down by months very fast. Optionally filtered by Tracking code 1/2 from the F.A.S.T database.
For GL Account "200" Sales:
Last 12 months and 3 years summary: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "200", 3, 12)
and filtered by rep MARK: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "200", 3, 12, "MARK")
and aged by weeks: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "200", 3, 12, "MARK","","W")
and aged by Calendar Quarter: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "200", 3, 12, "MARK","","CQ")
For All Sales GLs:
Last 12 months and 3 years summary: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#Sales.+^", 3, 12)
and filtered by rep MARK: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#Sales.+^", 3, 12, "MARK")
and aged by last 10 weeks: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#Sales.+^", 3, 10, "MARK","","W")
and aged by 6 Calendar Quarters: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#Sales.+^", 3, 6, "MARK","","CQ")
the "." ask for all under the heading, "+" add totals and "^" add period headings
Whole P&L:
Last 12 months and 3 years summary: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#ReportPL", 3, 12)
and filtered by rep MARK: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#ReportPL", 3, 12, "MARK")
and aged by weeks: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#ReportPL", 3, 12, "MARK","","W")
and aged by Calendar Quarter: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#ReportPL", 3, 12, "MARK","","CQ")
Current Balance under GL Heading:
Total Bank Balance: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#BANKS")
All Bank GLs Balance with total: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#BANKS.+^")
the "." ask for all under the heading, "+" add totals and "^" add period headings
All Liabilities with total: =CXL.GLValSpill( "ZQBO01 or ZXERO01", "#LIABILITY.+^")
Other useful functions:
Hot Link GL Name: =CXL.GLName( "ZQBO01 or ZXERO01", "200" )
Get Company Name and Sync Status: =CXL.GetCompanyStatus( "ZQBO01 or ZXERO01" )
Receivable Age Debt age by 30 days in 5 columns: =CXL.AgeDebtTotalSpill( "ZQBO01 or ZXERO01", "Receivable", 30, 5 )
This also returns forecast payments based on overall/contact average pay days
Get List of data:
GL codes sort by headings: =CXL.CodesSpill( "ZQBO01 or ZXERO01", "GLCodesByHead")
Consolidated for 2 Companies: =CXL.CodesSpill( "ZQBO01 or ZXERO01,ZDEMO02", "GLCodesByHead")
And remove GLs not used for 2 years: =CXL.CodesSpill( "ZQBO01 or ZXERO01,ZDEMO02", "GLCodesByHead", "Y2")
Stock Items: =CXL.CodesSpill( "ZQBO01 or ZXERO01", "ItemSelectCodes")
Tracking 1: =CXL.CodesSpill( "ZQBO01 or ZXERO01", "TRACKING1")
See consolidatedXL.com/start for tutorials, in depth video demos and reference guide.
CXL = Your Secret Wand for Financial Wizardry - unleash your Financial Creativity.
Establish your own identity or unique financial service
App capabilities
- Can read and make changes to your document
- Can send data over the Internet