Guided Tour
Details on the QuickBooks Exports
ConsignPro sends data to QuickBooks using the "QuickBooks data bridge" which is found above the Main Menu. In that menu, you have the opportunity to export sales data and/or consignor payout data. Each is done separately.
The data export from ConsignPro creates two transactions files on your computer hard drive, typically to the C:\Qbooksw folder. You can specify another location if you choose. The name of the Sales extract is cpmmddyyyy.iif (mm being the current month, dd current day, yyyy current year). The name of the Payouts extract is cpcmmddyyyy.iif. Each of these files can be examined using Notepad if you choose.
Once the extract (or export) is done, the next step is to import those transactions into QuickBooks. More on that below.
The Sales Export
The Sales Export from ConsignPro updates up to 5 accounts in QuickBooks. Those accounts are: Accounts Receivable, Merchandise Sales, Sales Discounts, Sales Tax Payable, and the Vendor account you setup as the payee for State Sales Tax. You align these account names between the two programs on the "QuickBooks Accounts" tab within the data bridge in ConsignPro. It is important that names are exact or the proper accounts will not be updated correctly.
Below is a sample transaction of 4 items sold on a single invoice in ConsignPro and how each account is debitted and creditted in QuickBooks
TRNS INVOICE 02/12/16 Accounts Receivable Walk In Customer 263.08 55124 N N Walk In Customer Credit Card Y Invoice AUTOSTAX
SPL INVOICE 02/12/16 Merchandise Sales -157.45 1805 Bronze Statue N Resale
SPL INVOICE 02/12/16 Merchandise Sales -9.40 35 Disney N Resale
SPL INVOICE 02/12/16 Merchandise Sales -55.00 34 Double Stroller N Resale
SPL INVOICE 02/12/16 Merchandise Sales -55.00 32 C.O.M. N Resale
SPL INVOICE 02/12/16 Sales Discounts 25.00 N Discount
SPL INVOICE 02/12/16 Sales Tax Payable FL Dept of Revenu -11.23 55124 N
ENDTRNS
The names of the accounts used, other than Accounts Receivable (which is fixed) come from the account names you put into the ConsignPro data bridge
The Payouts Export
The consignor payouts export from ConsignPro updates one bank checking account and one account for the cost of goods sold. Both of those names from QuickBooks are entered into the ConsignPro data bridge "QB Checking account name" and "QB COGS account name".
Below is a sample transaction of a single check created by ConsignPro for Nameka Ambrose for $70.99 (check 1054)
TRNS,,CHECK,02/07/2016,Checking,Nameka Ambrose,class,-70.99,1054,,N,N
SPL,,CHECK,02/07/2016,Purchases - Resale Items,Nameka Ambrose,class,70.99,,N
ENDTRNS
In this transaction, name of the checking account in QuickBooks is simply "Checking" and the name of the COGS account is "Purchases - Resale Items". Both check and cash payouts to consignors are passed to QuickBooks using the data bridge
Other Setup Steps
The following steps in QuickBooks are important before importing data from ConsignPro.
- Under Customers, add a new customer to QuickBooks. The name of the customer should be "Walk In Customer"
- Under Vendors, add a new Vendor for your state sales tax (ex. State Dept of Revenue)
- Under Edit / Preferences / Sales Tax / Company Preference, be sure to turn sales tax OFF
Importing the Data
Once you've created the transaction file (or files) using the "Export Data" button within the ConsignPro data bridge, and have completed the Other Setup Steps, you are ready to bring the data into QuickBooks. If this is your first import, it is strongly recommended that you backup your QuickBooks company file. That way, if the results are not what you expect, you can restore that company file.
To import the data into a desktop version of QuickBooks, use "File / Utilities / Import / IIF Files". On the Import IIF dialoge, be sure to click "Import it for me. I'll fix it later." at the bottom. Locate the file on your hard drive (C:\QBooksw folder) that was created in the export from ConsignPro, select it, and click Open. The file should be imported with no errors.
To import the data into the online version of QuickBooks, you'll need to buy and install IIFImporter for QB Online by AaaTeX. See the QuickBooks Online section on this page for details.
You can now examine your accounts, check register, P&L Statement, etc within QuickBooks to understand where the data landed.
QuickBooks Online
Sharing data between ConsignPro and QuickBooks online is accomplished thru the use of a 3rd party software named IIFImporter by AaaTeX. Once you have purchased that software, follow the setup steps below to ensure a smooth integration.
- Purchase the IIFImporter "Standard Edition" from the AaaTeX Order Page.
- In ConsignPro, under "CustomizeCP", "Store Settings" / "General" tab, be sure to check "Using QuickBooks Online".
- Turn off automated sales taxes in QBO. How do I do this?
- Define "SALES TAX" as a QBO tax rate. How do I do this? (Select "Add a tax rate and agency" and be sure to name the tax rate "SALES TAX" when prompted)
- Add "Undeposited Funds" to your chart of accounts. Account Type = "Other Current Assets", Detail Type = "Undeposited Funds", Name = "Undeposited Funds". How do I do this?
- If you converted from the Desktop version of QB, an "*Undeposited Funds" account may have been created. If so, you must remove the "*" from the name.
- Once complete, you should be able to export your QuickBooks data from ConsignPro and use the AaaTeX software to pass those .iif transactions into your QuickBooks Online company file.
Other Thoughts
Some bookkeepers don't like the fact we're putting the money into Accounts Receivable but would rather have that received into either Cash or a bank account. If that is your preference, you can create a journal entry to move the money to any account you'd like in QB.
***Visual Horizons Software LLC (and its creator Brian Wilson) are typically absolved of any liability associated with mistakes, omissions, or errors that may be in the ConsignPro program. This release of liability is discussed in detail in the DISCLAIMER that you accepted when you installed ConsignPro. For particular wording of this disclaimer, please click "Help" / "Disclaimer" on the top of the ConsignPro Main Menu.
