To produce the financial reports, we use Consolidated XL functions to hot link the financial data from Xero, QuickBooks or Sage Accounting by Accounting Period (default)/Quarter (AQ), Calendar Month(C)/Quarter(CQ) (Jan-Dec), or 52 weeks (W) of the year (Jan-Dec).
The Wizard "Get GL Values Example Wizard" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.
By default, Consolidated XL will return figures based on Accrual Accounting. This can be changed by pressing ... button on the Consolidated XL side panel next to the appropriate company, and selecting "Settings". Then change "CXL Reporting Mode" to "Same as system" or "Cash Accounting". This change can then be forced to all companies by pressing To All button. Please read this if using Xero
"CompCode" is a short code assigned to each company. We provide three examples of companies, EXAMP01-03. Your Consolidated XL will apply "MARK01" to "Marks Chocolate Company", but you can rename it if you wish using the options menu against each company listed in the UI.
We will use EXAMP01 in the following examples:
In the following functions GLBal(), GLVal(),GLCr() and GLDb() are used to get figures for periods, both for financial periods and calendar months, weeks, and quarters from QuickBooks, Sage Accounting or Xero to a single cell, usefull for Multi-company consolidation. We also have a GL function which gets many periods using one function call for one company.
Get GL Values using GLDb, GLCr and GLBal GLVal negates the appropriate values for easy reporting Get GL Values using GLVal which negates the appropriate values P&L or Income Statement We are also filtering by Rep "Sam", and have two columns to get YTD figures (period 0) GetPeriodName() takes care of all the column headings for us automatically We are using #REVENUE to get total for all under the revenue heading. We now have the same report but for Weeks by simply changing the Options paramter to "W" And no by Quarters by simply changing the Options parameter to "AQ" Xero, QuickBooks or Sage heading list.
The Balance (Debits-Credits) for GL Accounts can be obtained using this function. For trial balance reporting, etc., this is used.
For a tutorial on this function, click here
It is often the case that we want Sales figures that GLBal would return as negative values, since they are Credits in the Chart of Accounts. For such cases, it is better to use GLVal function, which returns negated values when approprate.
Balance period Credit/Debits can be extracted from Xero, Sage Accounting and QuickBooks.
Selecting CXL figures in the sheet and then selecting "Chart" menu shows CXL Quick Charts:
The "Trans" menu shows all outstanding transactions for the current selected period. Clicking on the chart columns wil drill down to the transactions in each period.
ToDo: Tutorial and Video Demo of this
GL 310 Sales Clothing - COS 410 Clothing Cost of Sales
Add some sales less Cost of Sales (310+315+320)-410
We Summarise parent categories to give “#SALES”,”#COS”,”#PROFIT”,”#OVERHEAD”. See list for Major Headings
Consolidated XL powerful functions can also analyse the accounting data by Cost Centres and Departments in Sage Accounting, known as Tracking Codes in Xero, Class/Locations in QuickBooks.
We have called them Cost Centres and Departments for consistence across the different Accounting Software packages, and to use the same functions.
This principle can then be applied to all the other previous examples. So..
An option in the Consolidated XL “Company” pane will copy Cost Centres and Department (Tracking codes/Classes) codes to the clipboard for you to paste into the sheet, or use the autogenerate function.
We have seen examples of this above. [Options] is left blank or "A".