Once your information has been exported from LightSpeed and imported into QuickBooks, you must confirm that the accounts are balanced. To do this, it's important to have a basic understanding of how these numbers and accounts relate to each other. This article outlines each export file, the GL accounts that are affected, and how they relate to other GL accounts. It must be noted, however, that the initial setup of the GL accounts (your Chart of Accounts) should be done with the aid of a Chartered Accountant, and decided on prior to any integration with LightSpeed.
There are, in fact, up to five regularly-exported files being sent from LightSpeed to QuickBooks - Invoices, Payments, Inventory Adjustments, Supplier Invoices, and Transfers (in the case of Multi-Store use). The table below illustrates which GL accounts are affected by each of these files, and their type.
The "type" assigned to each account is not used in LightSpeed, though they are displayed in the GL Accounts setup window. However, they are used in QuickBooks for Mac, and should accurately reflect the kind of account being used in LightSpeed. Please click here for a complete list of accounting terms and their definitions.
There are two additional numbers that are affected by these exports: Accounts Receivable and Accounts Payable. A/R represents the overall amount that a retailer is owed (or owes) their Customers, while A/P represents the overall amount a retailer owes (or is owed by) their Suppliers. If you purchase an item for $10 from a Supplier, your A/P would be $10. If you sell an item for $10 to a Customer without them paying for it, your A/R would be $10 until they paid, when it would return to $0.
The Invoice export (also known as Sales) contains data pertaining to a LightSpeed Invoice. For example, an Invoice taxed at 10% selling an item for a $100 sell price would total $110. Assuming a Cost Average of the Product of $50, the GL accounts would be 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 the Customer didn't pay, the retailer now has an outstanding A/R 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.
If the Customer paid for the above Invoice, it's the Payments file that would be affected. So:
- Undeposited Funds: +110
- AR: -110
Therefore, the A/R balance increased 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.
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 above, so they buy it from their Supplier, and has 30 days to pay for the purchase. The Supplier is not charging freight, so the Payable Expense account is not touched. Therefore:
- Accounts Payable: +50
- Inventory/Asset: +50
When a retailer receives product, it's the Supplier Invoice that affects the Inventory/Asset account by increasing it ($50 in this case), and they owe the Supplier for the cost of what they bought, which is also $50.
It is important to remember than although the Supplier Invoice originates in LightSpeed, it is imported into QuickBooks and ultimately is paid from QuickBooks. The retailer would make a manual adjustment to the Supplier Invoice in LightSpeed once it has been paid.
When a retailer makes an Inventory Adjustment in LightSpeed, whether it's to adjust the item(s) in or out, both 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 will be decreased by the value of the item, naturally, but the Cost of Goods Sold (COGS) account will be 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.
1. I have a retail store. I need to stock the shelves, so I buy a Product for $10. After posting and exporting my Purchases file and importing it into QuickBooks, my Chart of Accounts looks like this:
I now owe my Supplier $10, so my A/P account is at $10. My stock levels have risen, so my Inventory/Asset goes to $10.
2a. I put the Product on my shelf and it sells for a price of $100 to a Customer who has Net 30 terms. My sales tax is 10%. My A/P hasn't changed - I still owe my Supplier. My A/R is now $110, since I am owed $110 for the Invoice. And my Inventory/Asset account has decreased to zero, since I no longer have anything in my inventory.
b. My COGS account has increased $10, since there's a cost associated to the Product I've sold.
c. My Sales (Income) account increases to $100, since that's how much revenue the sale generated.
d. My Accounts Receivable account has increased to $110, which is the Income plus the sales tax.
e. My Tax account has also been modified $10, since that value must be tracked separately from my general A/R value.
3. The Customer later sends me a check to cover the sale, so I go back and apply the check directly to the Invoice, even though the sale has already been posted and exported. Again, my A/P balance hasn't changed. However, my A/R balance has gone from $110 to zero, because I have been paid and am no longer owed anything. The account that tracks my payments - Undeposited Funds - has increased $110.
4. I find a Product under my desk and must adjust it into inventory. My Inventory/Asset account increases to $10, since I've written the Product into my inventory. However, my COGS account has automatically been decreased by $10, so I would need to make a manual entry offsetting this adjustment to correctly portray what has happened.
Verifying information between LightSpeed and QuickBooks is important. Confirming that your accounts are balanced and accurately reflect the various states of your inventory and sales is an essential part of a store's success. Knowing how to use various LightSpeed reports to verify the accuracy of your data is extremely beneficial to retailers who want reliable information in both LightSpeed and QuickBooks.
There are several reports that can be run to determine the value of your inventory, either at the time, or at a point in the past.
INVENTORY
Reporting > Inventory Valuation > General - a breakdown of all current inventory and its value
Intelligence > Inventory Valuation by Date (GL Asset) - a breakdown of all inventory as of midnight on the date specified broken out per GL Asset account
SALES
Reporting > Sales > By GL Income - a breakdown of Sales that should match the Sales/Income account in QuickBooks
COGS
Reporting > Sales > By GL COGS - a breakdown of costs that should match the COGS account in QuickBooks
SALES TAX
Reporting > Taxes > General - to help verify the value of Sales Tax collected
Reporting > General (Supplier Invoices) - if applicable, can be used to verify Sales Tax paid to Suppliers
PURCHASES
Tools > Reporting > Inventory History > By Date (Purchase Orders) - PO History report to track all Purchase Orders
HISTORIES
You can resave a file that was exported by clicking the History button in the Export to QuickBooks window.
It is recommended that when you begin to export data from LightSpeed to QuickBooks, you do it 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 may choose to export on a weekly basis, and, depending on the level of detail you want in QuickBooks, choose to do Summarized exports.
