On every update, manually activated or automatically overnight, ConsoidatedXL syncs nominal transactions from the last date synced. If Pro version all Approved Invoices and Credit notes are synced, to give Stock Item, Customer/Vendor/Stock Item Turnover Analysis and Stock Profit. Predicted future Receivable/Payable based on average pay days of Customer/Vendor or predicted payment date.
Five years of history is maintained for reporting.
Detail analysis is by Account (GL), Account (GL) Heading, Customer/Vendor Account Turnover, and Stock Item Sales/Purchase Turnover Qty and Profit (see below). Plus, analysis by Tracking category 1 or 2.
In order to provide quick analysis in Excel, ConsoidatedXL builds a FAST (Financial Accounting Summarization Table) year summary record for each type of period analysis (Calendar Period, Accounting Period or Weekly Periods).
see GLVal() and GLValAnal() Functions for more detail.
By default, Consolidated XL will return figures based on Accrual Accounting. This default 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.
This can also be overridden on each function by specifying "CASH" or "ACCRUAL" in the options parameter.
When a stock item is tracked in Xero, the purchase price is moved from Inventory to Cost of Sales when it is sold at the Cost Price. It's unfortunate that this value and the FIFO database aren't available in the Xero API. Thus, the Trial Balance will not agree with Xero for the Stock Inventory and Cost of Sales GLs.
ConsoidatedXL stores data using internal IDs for each Codes/Name relating to Account, Contact or Stock Item and thus it is immune to Code/Name changes.
Xero allows you to edit Transactions after being SUBMITTED. ConsoidatedXL will sync changes on next Sync
Our data is stored in Azure Cloud, with AES-256 encrypted databases with all the latest safe guards and encryption. Your Xero accounts data are stored in a separate database to our billing and license information. We also encrypt Customer/Supplier name and address, with different encryption per data file.
The list of outstanding transactions is kept in sync to enable Cash Flow and KPI reports on money owed. An array of outstanding receivables and payables can be extracted or totalled for the company. Future Predicted Payments are based on a Customer/Vendor's average payment days over the last 6 transactions. If account has no history the Company Average is used, based on te last 30 days of payments. A forecasted payment date is overridden by entering a predicted date in Xero
see AgeDebtTotalArray() and AgeContactDebtArray() Functions for more detail.
ConsoidatedXL also predicts the amount receivable based on the average payment days for each specific Customer, and on the Vendor side the Payable amount based on your payment history. The function to retrieve these figures uses a user defined period range, 7 days by default. So you can get totals for next few weeks. A total of 42 periods can be calculated.
Changes in Account code, Customer/Vendor, Customer Sub Account and Stock Item should not affect the ConsoidatedXL figures.
If the transaction has been re-coded in Xero, then you should activate “ReSync All Data”, in company setting, to completely clear all data and recalculated the figures.
The ConsoidatedXL figures are tested again the Xero Trial Balance at the end of every sync. We are notified if there is an error for us to investigate.
Budgets are synced within a time window of -18 months and 2 years ahead.
see GLBudget() Function for more detail.
Profit is calulated as Sales Price - Last Cost Price of stock item and can be analysis based on Tracking codes. Currency Variance is then accounted for when paid.
Stock Adjustments are not synced, use Settings Sync Accounts options to resync Stock Qtantiitys which is very quick..
All functions, with the exception of specific currency functions list below, retrieve figures in home currency unless told otherwise. If the Option parameter contains an ISO currency code, then any movement in that currency is returned for P&L values.
For Example: If your home currency is GBP and we have some sales in USD, then specifying USD will return all turnover made in USD. If we Specify “toUSB”, our home currency values of GBP will be converted to USD using the hourly updated exchange rate.
If the option is “toUSB202204”, the all functions return USD at the rate as of the end of the accounting year 2022 period 4.
Cust/Supp P&L Turnover + Stock Item Turnover and Nett Profit figures are updated at the time the Invoice is Approved in Currency and Base Currency. When fully paid, the figures are then adjusted to account for any Currency Variations.
The Currency Variance GL account will also return the Currency Variance at the period requested, together with adjusting Accounts Payable/Receivable on the Balance sheet
If a Currency is revalued in Xero, then ConsoidatedXL will revalue the transactions automatically.
see CurrencyUnrealised() function for more detail.
If CurrencyCode=”” then returns all Unrealised currency gain/loss
When using the Get GL Name/Turnover and Balance figures the following codes can be used to get the totals for Major Headings, and by Cost Centre and Departments. This allows the sheet to ensure the total is the total of the system and not an addition within the sheet which may have missed a GL code from the list of GL codes.
In order to produce Profit and Losss/Income statement, Trail Balance or Balance sheet we prvide a number of calculated totals for reporting. These will return a total as checked by Consolidated XL against the accounts and thus if an account is missing from the report, unfortunetly you have to maintain these after report creation if a new GL account is added to the system, these totals will be correct.
You could add you own cacluations to check your totals, against these value to verify all GL accounts are listed.