On every update, manually activated or automatically overnight, ConsoidatedXL syncs QuickBooks Nominal Transactions from the last date synced, and outstanding transactions to give Aged Debt/Credit. 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.
Three years of history is maintained for reporting.
Detail analysis is by QuickBooks Account (GL) sub account, Account (GL), Account (GL) Heading, Customer/Vendor, Customer sub accounts, Stock Item/Category Sales/Purchase Turnover Qty and Profit (see below). Plus, analysis by Class, Sub Class and Location.
In order to provide quick analysis in Excel, ConsoidatedXL builds a year summary record for each type of period analysis (Calendar Period/Quarter, Accounting Period/Quarter or Weekly Periods).
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.
Changes in QuickBooks Account code, Customer/Vendor, Customer Sub Account and Stock Item should not affect the ConsoidatedXL figures, since ConsoidatedXL uses a sophisticated relation database.
Our data is stored in Azure Cloud, with AES-256 encrypted databases with all the latest safe guards and encryption. Your QuickBooks 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.
ConsoidatedXL stores data using internal IDs for each Codes/Name. So it is immune to code changes. But changing the hieratical structure of the accounts will cause a automatic re-calc of the analysis.
Quickbooks allows you to edit Transactions after being committed. ConsoidatedXL will sync changes on next Sync
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 QuickBooks Account code, Customer/Vendor, Customer Sub Account and Stock Item should not affect the ConsoidatedXL figures.
If the transaction has been re-coded in QuickBooks, 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 QuickBooks 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.
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.
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 QuickBooks, then ConsoidatedXL will revalue the transactions automatically.
CurrencyUnrealised(CompCode,CurrencyCode).
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.