1.1 QuickBooks Pro Export Please Read: This export function was specially designed to assist bookkeepers/accountants in the exporting of essential data and transactions from Finance Express with the sole purpose of importing into QuickBooks Pro. Finance Express highly recommends that if you intend to use this function, you consult with your bookkeeper/accountant as to its practicality. Finance Express can only assist you with the actual export and import procedures required to bring the data into QuickBooks.
Finance Express will not, in anyway, be able to assist you with any questions related to the use of QuickBooks Pro and how it pertains to your company’s daily operations, financial obligations and entries that may need to be performed once an export has been performed. Prior to using this function, you must meet the following requirements: • Have Finance Express security right permissions to perform the QuickBooks Pro export routine. Your Finance Express administrator will need to assign you the necessary security rights. • Currently use and have a good understanding of QuickBooks Pro (versions 2001 and greater)
1.1.1
Important Considerations (Please Read) Make sure you update the Finance Express General Ledger mappings to reflect your current chart of accounts. This will ensure that the export will match up GL account numbers correctly, otherwise, you will receive an error during the import process. It’s recommended that you setup a new QuickBooks Pro company. During interview process, it will ask you what “Type of Business” and will allow you to select the “Industry”. Choose “Other”. IMPORTANT: Each time an export is performed, an internal flag is set to prevent you from re-exporting the same transactions, thus creating duplicate entries. If you need to perform a re-export, you will need to contact Finance Express support so we can reset the flags. The recommended procedure for setting up QuickBooks Pro for the first time is to establish what your current balances are in Finance Express. This requires that you run an Inventory List Report and an Accounts Trial Balance report, preferably, after the close of business. This allows you to set beginning balances within QuickBooks Pro prior to importing transactions. Once you have made the necessary manual entries within QuickBooks Pro for all related GL accounts, you should then perform the following: •
Export Transactions on a daily basis. The first export you perform (and all subsequent daily exports) should then properly adjust the manual entries you initially entered. If you need to export transactions for an entire week, do seven exports, one for each day, not one export of seven days. This is important for reconciling and also in the event that you need to have your transaction flags reset.
Service Companies/Vendors: Any changes you make in QuickBooks to previously imported Service Companies/Vendors will be overwritten the next time you export and import these vendors/companies. In other words, if you update a Finance Express vendor phone number and address on a Repo Agent and then export Service Companies/Vendors it will replace that Repo Agents address information within QuickBooks.
Viewing the exported data prior to importing
If you would like to open and view the export file (.IIF) in its raw format, use Microsoft Excel. Although you should never change the file prior to importing, you’ll be able to examine the data that was exported from Finance Express.
This task should only be performed by someone versed in Excel as it requires knowledge on opening a non-Excel type file.
1.1.2
Initial Steps Prior to Exporting
It is very important that you review the following sections prior to exporting your first QuickBooks file.
1.1.2.1 Setup the Bookkeeping Company A Bookkeeping company is primarily used to track transactions based on an accounting entity. A good example might be a dealership also has a separate Related Finance Company (RFC). By setting up a bookkeeping company for the dealership and then a bookkeeping company, you can export payments and transactions separately for each entity. The bookkeeping company is assigned at the portfolio and sales location levels. If you don’t have an RFC, you will still need to setup and assign at least one bookkeeping company. Go to Settings > Channels > Add a new Channel > Channel Type. Select “Company - Business Books”. Next, enter the required fields and make sure to change Active to 'Yes’. Once complete, click the Update New Channel button.
1.1.2.2 Assign the Bookkeeping Company Assign to your Sales Location/Branch 1. Goto Settings > Channels > Channel Type. Dropdown the Channel type and choose “Sales Location/Branch”, click Find. 2. Location your Sales Location(s) and click on the name. 3. Click on the Sales Defaults tab 4. Dropdown the “Assigned Bookkeeping Company” and choose the bookkeeping company setup in the previous section 5. Click the Update Sales Defaults button to save Assign to your Portfolio(s) 1. Goto Settings > Portfolios and click on the name of the portfolio 2. Dropdown the “What Bookkeeping Company Owns This Location” and choose the bookkeeping company setup in the previous section 3. Click the Update Portfolio button to save
1.1.2.3 Prepare QuickBooks Pro for Imports In this example, we are assuming that you are going to create a new QB file from scratch. We often times will recommend that you start with a new QB file since the imports from Finance Express will automatically setup all of your Quickbooks GL accounts that relate to Finance Express transactions. 1.
Open QuickBooks > File > New Company > Next > Next > Next > Skip Interview > Enter company name, address, etc. > Next > Select (No Type) in the left hand column > Next > Click Save to create and then save your new .QBW file on your computers hard drive.
1.1.2.4 Perform an Initial Export to setup your GL Accounts in QB Now we want to import the chart of accounts to your new QuickBooks company you just setup. 1. 2. 3. 4. 5. 6. 7. 8. 9.
Go to Quickbooks > QuickBooks Pro Export >. Choose your Bookkeeping Company channel from the bookkeeping dropdown Change Export Chart of Accounts to ‘Yes’ Change Export Service Company/vendors to ‘Yes’ Enter today's date as the beginning and ending date Important! Leave all transactions selections set to 'NO' Click the Export Transactions button Save the file to your hard drive Unzip the file and Extract (save) the .IIF to your hard drive Import the file to your QB Company (File > Import > IIF Files) Note: when you extract and save the file to your hard drive, save it to a folder you’ll remember. When you navigate to this folder, the file may be located in components/dts/Release1/fcdObjects/tempfiles
1.1.2.5 Setup Initial Finance Express balances in QuickBooks (optional) If you’ve been using Finance Express for sometime and would like to setup initial balances in QuickBooks prior to exporting new transactions, follow these steps: 1.
Make sure you don't have any deals that are in 'contracted' status: Deals > Deal List. Identify any deals that are not ‘Contracted’ or ‘Booked’. The status should be either 'booked' or 'uncontracted' accordingly.
2. 3. 4. 5.
Accounts: At the close of business today or before you post any transactions tomorrow, goto Reports > Print Reports > Account Trial Balance by Portfolio with show detail set to 'No’. Click the Generate report Enter the total balances from the report into your corresponding QB chart of accounts. (i.e., Principal balances, tax balances, etc.) Inventory: Reports > Print Reports > Inventory List Report. Click the Generate Report button. Enter your total acquired cost and total expenses (only if you capitalize expenses) from the report into your corresponding QB chart of accounts.
Begin exporting your transactions the day after your beginning balances are entered. Do not pull transaction from the same day or any day previous to your account trial balance report. This is very important.
1.1.3
Export Steps
1
From the Command Center, select QuickBooks Pro Export.
2
Enter Beginning and Ending dates Choose the General Export selections • •
• •
Export Chart of Account: if selected, this will include
all associated GL accounts setup within Finance Express. Export Service Companies/Vendors: if selected, this will export all Repair Service Shop, Transport/Towing Company, Repo Agent, Dealer/Wholesaler/Auction, and Vendor Company channel types. Beginning / Ending Date: if applicable, enter the beginning and ending dates of the transactions you’d like to export. Export Detail Transactions: If set to Yes, then it will export a detailed listing of each and every transaction. If set to No, then it will add up and summarize each of the GL accounts and create one entry for each
Select which Transaction Types you wish include as part of the export. Current Transaction Types: •
•
•
•
•
Inventory Service Expenses: if selected, this will export all inventory expense related transactions that fall between the beginning and ending dates you specify. You can then elect to use the Post Date or Use the Service Date for those transactions. Inventory Transactions: if selected, this will export any vehicles and their associated purchase costs that fall between dates you specify. When you first enter a vehicle and assign it a Cost, this is the value that will be exported. Sales Transactions: if selected, this will export all transactions related to any sales that may have been booked during the time period you specify. This includes any finance deal (in-house or outside), Cash Sales or Wholesale deals. This will also include any trade-in’s as a part of those sales. Account Transactions: if selected, this will export all associated account transactions that fall between the dates you specify. This includes any payments, adjustments or account related transactions that took placed during that time period. Interest Accrual Transactions: depending on the type of loan account (simple or pre-compute), daily interest accruals will be exported.
3
Click the Export Transactions button
4
After a brief delay, the File Download popup window should appear. Click to "Save" button. NOTE: it’s recommended that you create an easy to find folder on your PC to place these files in. (ie., C:/QBExports)
5
Proceed to the Import Steps below.
1.1.4
Import Steps
After successfully exporting your data, the next step is to import the data into QuickBooks Pro. 1
Once logged into QuickBooks Pro, click on File > Utilities > Import. (this may vary depending on your version)
2
Locate the file to import. Depending on where you saved this file, you may need to navigate to its directory. Once located, click on the file then click Open. (The file will have a .IIF extension. Also, to identify the most recently exported file, place your mouse over the file to display the file properties. The “Date Modified” property should reflect the export date.)
3
Once imported, you should receive the following message, “Your data has been imported”.
1.1.4.1 Import Errors Depending on the data being imported, you may be prompted with import errors. This typically occurs if you are attempting to import duplicate Inventory Expenses from the same date range. During the import process, QuickBooks will scan to see if an unpaid inventory expense currently exists. If it does, you will get a Duplicate Number message similar to this one. Answer No to this message. If you answer Yes, the system will create a duplicate invoice to be paid. When you answer No to message above, the following warning will next appear. Click OK to continue. Repeat this process for any other duplicate invoices that are identified. Once complete, the system should return a message that import has completed.
1.1.5
General Ledger Account Numbers
Listed below are a list of pre-defined general ledger accounts number, their name and corresponding GL Account Type. The GL Account Type, as defined by QuickBooks Pro, exists for each number so that QuickBooks knows how to classify that particular GL account number. For example, a GL number that has an OCASSET account type would be classified as an OTHER CURRENT ASSET. Likewise, an Account Type referred to as AP reflects an ACCOUNTS PAYABLE account. If you would like to learn more about these account types, please reference the help area within QuickBooks Pro.
Acct # 103000 103500 103600 104000 105000 105100 105200 105300 105400 105500 106000 107000 129000 129500 202000 202100 226000 227000 230000 232000 242000 250000 301000 301500 302000 302500 303000 304000 305000
GL Name Undeposited Funds Contract A/R Clearing Transfer A/R Clearing DT Notes Receivable A/R Principal A/R Down Payments A/R Non Earning Principal A/R Misc Fees Due A/R Late Fees Due A/R Deferred Sales Tax A/R Accrued Interest A/R Interest Dealer Inventory Inventory Improvements Accounts Payable Sales Liabiltites Sales Tax Payable Deferred Sales Tax Inventory Clearing Trade In Payoff To Others Discount On A/R Unearned Interest Auto Sales Sales Other Interest Income Discount Earned Late Fee Income Financed Auto Sales Misc Fee Income
GL Acct Type OCASSET OCASSET OCASSET OCASSET OCASSET OCASSET OCASSET OCASSET OCASSET OCASSET OCASSET OCASSET OCASSET OCASSET AP OCLIAB OCLIAB OCLIAB OCLIAB OCLIAB OCLIAB OCLIAB INC INC INC INC INC INC INC
QB Account Name Undeposited Funds Contract A/R Clearing Transfer A/R Clearing Notes Receivable Notes Receivable:A/R Principal Notes Receivable:A/R Down Payments Notes Receivable:A/R Non Earning Principal Notes Receivable:A/R Misc Fees Due Notes Receivable:A/R Late Fees Notes Receivable:A/R Deferred Sales Tax Notes Receivable:A/R Accrued Interest Notes Receivable:A/R Interest Dealer Inventory Dealer Inventory:Improvements Accounts Payable AP Due Others Sales Tax Payable Deferred Sales Tax Inventory Clearing Account Inventory Finance:Other Discount On A/R Unearned Interest Sales:Auto Sales Sales:Other Interest Income Discount Income Sales:Late Fees Sales:Financed Auto Sales Sales:Misc Fees
306000 307000 410000 410005 415000
Trade-Ins Sales Add-On Products Make Ready Expense Sold Inventory Tags/Title/License
INC INC COGS COGS COGS
416000 417000 425000 440000 450000 451000 452000 453000 454000 455000 456000 460000
Sales Expenses Cost Of Add-On Products Repo Expenses Write Downs Prin Adjustment Interest Adjustment Late Fee Adjustment Misc Fee Adjustment Sales Tax Adjustments Down Payment Adjustments Non Earn Prin Adjustment Adjustments/Losses A/R
COGS COGS COGS EXP EXP EXP EXP EXP EXP EXP EXP EXP
Sales:Trade-Ins Sales:Add-On Products Cost Of Goods Sold:Make Ready Cost Of Goods Sold:Inventory Cost Of Goods Sold:Tags/Title/License Cost Of Goods Sold:Discounts and Commissions Cost Of Goods Sold:Add-On Products Cost Of Goods Sold:Repo Expenses Write Downs Write Downs:Principal Write Downs:Interest Write Downs:Late Fees Write Downs:MiscFees Write Downs:Sales Tax Write Downs:Down Payments Write Downs:Non Earn Prin Adjustments/Losses A/R
General Ledger Transaction Class Codes
1.1.6
Every transaction performed within Finance Express is tracked by a Class Code. This Class Code is associated with corresponding GL accounts to debit and credit. As a user, you should not be interested in the Class Code, but rather, take note of the GL Accounts that are being debited and credited during a particular transaction. These are the GL accounts that will be adjusted when the import is performed within QuickBooks Pro. To understand this table, reference the simple examples below.
Example 1 Let’s say you just entered a new vehicle into inventory that you recently bought for $15,000 and put it on a Floor Plan with the bank. At that point, the system logs a transaction with a type 70 and 73 Class Code. When you export “Inventory Transactions”, the system would create a file with the following entries by referencing Class Code 70 and 73 below. GL AccountDescription
DEBIT
129000 230000
Dealer Inventory Inventory Clearing Account
$15,000
231100 231200
Inventory Finance: Floor Plan Inventory Finance: ARM
$15,000
CREDIT___ $15,000 $15,000
Example 2 Let’s say you just took a $4,500 cash Down Payment on a loan account. The system tracks this event with a type 201 Class Code. When you export “Account Transactions”, the system would create and a file with the following entries by referencing Class Code 201 below. GL AccountDescription DEBIT 103000 Undeposited Funds 105100 Notes Receivable: A/R Down Payments
$4,500
CREDIT___ $4,500
Class Code 1 2 3 4 5 6 10 11 20 31 32 33 34 35 36 52 53 54 55 56 62 63 65 66 70 76 85 100 100 101 101 102 103 105 105 106 107 108 108 111 111 200 201 202 203 205 206 207 208 211 212 213
Debit GL Acct 103500 103500 103500 103500 129000 103500 410000 129000 425000 301000 301000 304000 301000 306000 307000 417000 415000 301000 306000 410005 202100 202100 202100 129000 129000 410005 230000 105500 202100 103000 227000 227000 105500 105500 226000 227000 105500 103500 227000 103500 227000 105100 103000 460000 105100 105100 103600 105100 103500 103000 455000 105100
Credit GL Acct 301000 301000 304000 301000 306000 307000 202000 202000 202000 103500 103500 103500 103500 129000 103500 202100 202100 226000 202100 129000 417000 415000 306000 410005 230000 129000 129000 103500 227000 105500 226000 105500 227000 103000 227000 105500 227000 105500 202100 105500 226000 103500 105100 105100 103500 103000 105100 103600 105100 105100 105100 455000
Class Code 226 227 231 232 233 235 236 237 1000 1001 1002 1003 1005 1006 1007 1008 1021 1022 1023 1025 1026 1027 1028 2000 3000 4001 4002 4003 4004 4005 4006 4007 4008 4010 4012 4013 4014 4014 4016 4017 4018 4019 8000 8001 8002 8003 8005 8006 8007 8008 9001 9002
Debit GL Acct 455000 105100 103000 455000 105100 105100 455000 105100 105000 103000 450000 105000 105000 103600 105000 103500 103000 456000 105200 105200 103600 103500 456000 105000 105000 103000 451000 106000 106000 106000 103600 106000 451000 107000 250000 107000 250000 106000 250000 107000 250000 250000 105400 103000 452000 105400 105400 103600 105400 452000 103000 453000
Credit GL Acct 105100 455000 105100 105100 455000 103000 105100 455000 103500 105000 105000 450000 103000 105000 103600 105000 105200 105200 460000 103000 105200 105200 105200 301500 301500 106000 106000 302000 302000 103000 106000 103600 106000 250000 107000 250000 107000 302000 107000 250000 107000 107000 303000 105400 105400 303000 103000 105400 103600 105400 105300 105300
215 216 217 221 222 223 225
1.1.7
105100 455000 105100 103000 455000 105100 105100
103000 105100 455000 105100 105100 455000 103000
9003 9005 9006 9007 9008 10001 10002 10005
105300 105300 103600 105300 453000 103000 129000 460000
305000 103000 105300 103600 105300 460000 460000 103000
Chart of Accounts – (Custom GL Mapping)
If you or your accountant uses QuickBooks Pro, there’s a good chance that you already established a chart of accounts using your own unique numbering system. If this is the case, you can use this area to supply Finance Express with your GL account numbers. When the system exports QuickBooks Pro data/transactions, it will then use the GL account numbers you supplied. If you elect not to update this area with your own GL account numbers, the system will simply use the default GL numbers already assigned.
Definitions for Custom GL Account Entries Master Acct #: this number represents Finance Express’s internal GL number. It can not be changed. Master Name: this value represents Finance Express’s internal name for a particular type of transaction. It can not be changed. Type: this value represents QuickBooks Account Type. These values are tied to specific types of transactions and can not be reassigned.
Valid values include: AR AP OCLIAB EQUITY EXP BANK INC EXEXP EXINC COGS OCASSET
Accounts Receivable Accounts Payable Other Current Liability Equity Account Expense Account Bank Account Income Account Other Expenses Other Income Cost Of Goods Sold Other Current Asset
Your QB Acct #: This value should reflect your GL number associated with that particular Account. This account should already exist in QuickBooks, otherwise, the system will automatically set it up. Your QB Account Description: This value should reflect the QuickBooks account description associated with Your Acct #. QuickBooks QB Account Name: This value should reflect the QuickBooks Account Name associated with Your Acct #. In the illustration above, note the change made to A/R Principal (105000) GL mapping. This account was assigned 19999 to the Your QB Acct # field, Principal Col – my was assigned to Your QB Account Description and Principal Col – QB was assigned to the QuickBooks QB Account Name. The illustration below shows what the account looks like in QuickBooks Pro.
