Consolidated XL

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 Xero using simple to master functions.

Xero == Consolidated XL + F.A.S.T. == Excel sheet

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

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

COST CENTRES

The FAST database summarises turnover by Cost Centres, known as Tracking Codes 1/2 in Xero, 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 "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( "ZXERO01", "200", 2023 )

Year 2023 period 1 turnover: =CXL.GLVal( "ZXERO01", "200", 2023, 1 )

YTD 2023 filter by Rep MARK: =CXL.GLVal( "ZXERO01", "200", 2023, 0, "MARK" )

      and for the previous year: =CXL.GLVal( "ZXERO01", "200", 2022, 0, "MARK" )

Year 2023 turnover filter by Rep MARK: =CXL.GLVal( "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( "ZXERO01", "#CalcNetProfit", 2023, 199)

Year 2023, filter by Rep MARK: =CXL.GLVal( "ZXERO01", "#CalcNetProfit", 2023, 199, "MARK")

      Periods 1 to 6: =CXL.GLVal( "ZXERO01", "#CalcNetProfit", 2023, 106, "MARK")

      Periods 7 to 12: =CXL.GLVal( "ZXERO01", "#CalcNetProfit", 2023, 712, "MARK")

Total Sales: =CXL.GLVal( "ZXERO01", "#SALES" )

Total Sales for MARK: =CXL.GLVal( "ZXERO01", "#SALES", 2023, 199, "MARK" )

Total Overheads: =CXL.GLVal( "ZXERO01", "#OVERHEADS" )

      for MARK: =CXL.GLVal( "ZXERO01", "#OVERHEADS", 2023, 199, "MARK" )

Current Balance:

Bank 090: =CXL.GLVal( "ZXERO01", "090" )

Total in all Bank Accounts: =CXL.GLVal( "ZXERO01", "#BANK" )

Total Liabilities: =CXL.GLVal( "ZXERO01", "#LIABILITY" )

Currenct + Long Term: =CXL.GLVal( "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( "ZXERO01", "200", 3, 12)

      and filtered by rep MARK: =CXL.GLValSpill( "ZXERO01", "200", 3, 12, "MARK")

      and aged by weeks: =CXL.GLValSpill( "ZXERO01", "200", 3, 12, "MARK","","W")

      and aged by Calendar Quarter: =CXL.GLValSpill( "ZXERO01", "200", 3, 12, "MARK","","CQ")

For All Sales GLs:

Last 12 months and 3 years summary: =CXL.GLValSpill( "ZXERO01", "#Sales.+^", 3, 12)

      and filtered by rep MARK: =CXL.GLValSpill( "ZXERO01", "#Sales.+^", 3, 12, "MARK")

      and aged by last 10 weeks: =CXL.GLValSpill( "ZXERO01", "#Sales.+^", 3, 10, "MARK","","W")

      and aged by 6 Calendar Quarters: =CXL.GLValSpill( "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( "ZXERO01", "#ReportPL", 3, 12)

      and filtered by rep MARK: =CXL.GLValSpill( "ZXERO01", "#ReportPL", 3, 12, "MARK")

      and aged by weeks: =CXL.GLValSpill( "ZXERO01", "#ReportPL", 3, 12, "MARK","","W")

      and aged by Calendar Quarter: =CXL.GLValSpill( "ZXERO01", "#ReportPL", 3, 12, "MARK","","CQ")

Current Balance under GL Heading:

Total Bank Balance: =CXL.GLValSpill( "ZXERO01", "#BANKS")

All Bank GLs Balance with total: =CXL.GLValSpill( "ZXERO01", "#BANKS.+^")

      the "." ask for all under the heading, "+" add totals and "^" add period headings

All Liabilities with total: =CXL.GLValSpill( "ZXERO01", "#LIABILITY.+^")

Other useful functions:

Hot Link GL Name: =CXL.GLName( "ZXERO01", "200" )

Get Company Name and Sync Status: =CXL.GetCompanyStatus( "ZXERO01" )

Receivable Age Debt age by 30 days in 5 columns: =CXL.AgeDebtTotalSpill( "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( "ZXERO01", "GLCodesByHead")

      Consolidated for 2 Companies: =CXL.CodesSpill( "ZXERO01,ZDEMO02", "GLCodesByHead")

      And remove GLs not used for 2 years: =CXL.CodesSpill( "ZXERO01,ZDEMO02", "GLCodesByHead", "Y2")

Stock Items: =CXL.CodesSpill( "ZXERO01", "ItemSelectCodes")

Tracking 1: =CXL.CodesSpill( "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