Reconciling QuickBooks

After you export your information from OnSite and import it to QuickBooks, confirm that the accounts are balanced. To do this, it's important to know how these numbers and accounts relate to each other. This topic outlines each export file, the GL accounts that are affected, and how they relate to other GL accounts. You should do the initial setup of the GL accounts (your Chart of Accounts) with the aid of a Chartered Accountant before you do any accounting integration with OnSite.

We recommend that you export data from Lightspeed to QuickBooks daily, and with Detailed exports. This makes any discrepancies easier to spot, and reconciliation can be done quickly. As you become accustomed to the process and understand which amounts correspond between the two software packages, you can choose to export on a weekly basis, and, depending on the level of detail you want in QuickBooks, choose to do Summarized exports.

There are up to five regularly-exported files sent from OnSite to QuickBooks: invoices, payments, inventory adjustments, supplier invoices and transfers (for Multi-Stores). The table shows which GL accounts are affected by each of these files, and their type.

Export Accounts Type
Sales (invoices)

income

inventory/asset

COGS

AR

sales tax

income

asset

cost of goods sold

accounts receivable

liability

Payments

undeposited funds

AR

bank

accounts receivable

Purchases (supplier invoices)

accounts payable

inventory/asset

payable expense

accounts payable

asset

other expense

Inventory adjustments

inventory/asset

COGS

asset

cost of goods sold

Transfers

inventory/asset

transit

asset

other asset

The type assigned to each account is not used in OnSite though it is displayed in the GL Accounts setup window. The type is used in QuickBooks for Mac, and should accurately reflect the kind of account being used in OnSite. For a glossary of accounting terms go to About accounting.

There are two additional numbers that are affected by these exports: accounts receivable and accounts payable. AR represents the overall amount that a retailer is owed (or owes) their customers. AP represents the overall amount a retailer owes (or is owed by) their suppliers. If you purchase an item for $10 from a supplier, your AP is $10. If you sell an item for $10 to a customer without them paying for it, your AR is $10 until they pay, when it returns to $0.

About the sales (invoices) export

The Invoice export (also called Sales) contains data about an invoice in OnSite. For example, an invoice taxed at 10% selling an item for a $100 sell price totals $110. Assuming a cost average of the product of $50, the GL accounts are affected like this:

  • Income: +100
  • Inventory/Asset: -50
  • COGS: +50
  • Accounts Receivable: +110
  • Sales Tax: +10

The retailer's revenue increases by $100, their inventory level decreases by $50, which is offset by an increase in the cost of their goods sold by the same amount. Assuming a customer didn't pay, the retailer now has an outstanding AR balance of $110, which is made up of the $100 revenue (income) and $10 tax collected, which is a liability that must be paid to the Tax Vendor from QuickBooks.

About the payments export

If the customer pay for their invoice, the payments file is affected so that:

  • Undeposited Funds: +110
  • AR: -110

Therefore, the AR balance increases by the sale ($110), is decreased to zero by the payment of $110, and the account the retailer uses to track their payments (Undeposited Funds, in this example) increases by $110.

About the purchases export

If a retailer buys an item from their supplier, their purchases export is affected. For example, a retailer now needs to replace the item sold, so they buy it from their supplier, and have 30 days to pay for the purchase. The supplier does not charge freight, so the payable expense account is not affected. Therefore:

  • Accounts Payable: +50
  • Inventory/Asset: +50

When a retailer receives products, the supplier invoice affects the inventory/asset account by increasing it ($50 in this case), and the retailer owes the supplier for the cost of what they bought, which is also $50.

Although the supplier invoice originates in OnSite, it is imported into QuickBooks and ultimately is paid from QuickBooks. The retailer makes a manual adjustment to the supplier invoice in OnSite when it is paid.

About the inventory adjustments export

When a retailer makes an inventory adjustment in OnSite, whether it's to adjust items in or out, the inventory/asset and COGS accounts are affected. If a product is, for example, lost or damaged and adjusted out of inventory, either by a manual adjustment in the product card, or by count inventory, the inventory/asset account is decreased by the value of the item but the cost of goods sold (COGS) account is increased. A retailer should make a manual entry in QuickBooks to offset this COGS entry, as it is an inaccurate picture of what has happened.

Verifying information in your accounts

Verifying information between OnSite and QuickBooks is important. Confirming that your accounts are balanced and accurately reflect the states of your inventory and sales is an essential part of a store's success. Knowing how to use Lightspeed reports to verify the accuracy of your data is very beneficial to retailers who want reliable information in OnSite and QuickBooks.

There are several reports you can run to determine the current and past value of your inventory.

Report type Location Description
Inventory Reporting > Inventory Valuation > General current inventory and its value
  Intelligence > Inventory Valuation by Date (GL Asset) inventory as of midnight on the date specified shown per GL Asset account
Sales Reporting > Sales > By GL Income

Sales that should match the Sales/Income account in QuickBooks

COGS

COGS Reporting > Sales > By GL COGS costs that should match the COGS account in QuickBooks
Sales tax Reporting > Taxes > General value of Sales Tax collected
Purchases Tools > Reporting > Inventory History > By Date (Purchase Orders) PO History report to track all Purchase Orders
Export history Tools > Export to QuickBooks > History You can resave a file that was exported by clicking the History button in the Export to QuickBooks window. 
Have more questions? Submit a request