QuickBooks FAQ

What data does it Sync?

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.

What ConsoidatedXL Analysis does it offer?

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

Cash/Accrual Reporting

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.

What if I change my Account codes, names etc.

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.

How safe is customer data?

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.

What if I recode transactions or restructure QuickBooks sub class/ accounts etc.?

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.

What if edit a Transaction?

Quickbooks allows you to edit Transactions after being committed. ConsoidatedXL will sync changes on next Sync

Aged Receivable/Payable

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.

Future Payment KPI - Pro

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.

Potential Errors

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.

Trial Balance Checked

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

Budgets are synced within a time window of -18 months and 2 years ahead.

Stock Nett Profit

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.

Currency

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

GL Account Major Headings

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.

Code Description
Profit and Loss - Sales
#REVENUE Revenue account
#SALES Sale account
#OTHERINCOME Other Income account
Profit and Loss - Cost of Sales
#COS Cost of Sales
Profit and Loss - Overheads
#OVERHEADS Overhead account
#EXPENSE Expense account
Balance Sheet - Assets
#BANK Bank account
#INVENTORY Inventory Asset account
#CURRENTASSETS Current Asset account
#FIXEDASSETS Fixed Asset account
#LONGASSET Long Term Assets
Balance Sheet - Liabilies
#CURRLIAB Current Liability account
#LIABILITY Liability account
#PREPAYMENT Prepayment account
#SUPERLIAB Superannuation Liability account
#LONGLIAB Long Term Liability account
#PAYG PAYG Liability account
#SUPERANNU Superannuation Expense account
#DEPRECIATION Depreciation account
#WAGESEXPENSE Wages Expense account
Balance Sheet - Equity
#EQUITY Equity account

Calculated Headings

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.

Code Description
Profit and Loss
#CalcTotalSales Total Sales
#CalcTotalCOS Total Cost of Sales
#CalcGrossProfit Gross Profit
#CalcTotalOverhead Total Overhead
#CalcNetProfit Net Profit
Balance sheet
#CalcTotalCurrentAsset Total Current Assets
#CalcTotalFixedAsset Total Fixed Assets
#CalcTotalAsset Total Assets
#CalcTotalCurrentLiab Total Current Liabilities
#CalcTotalLongLiab Total Long-Term Liabilities
#CalcTotalLiab Total Liabilities
#CalcTotalLiabEquity Total Liabilities + Equity
Trail Balance
#CalcTotalTrialBal TB Total