Instructions to Import New Business clients to QB customers file (written for Quickbooks Premiere 2010)

Importing New Clients
• GETTING DATA INTO AN EXCEL FORMAT

This is obviously for a format used especially for Ethan Selph. By no means are these instructions able to work in every situation because your software program might be much different. I would strongly encourage getting a bookkeeper to do this for you or you can contact Randy Russon, his contact information is within this blog, and he can give you some tips on making this work for you.

1. Open the New Business commission statement.
2. Save the commission statement to the desktop.
3. Open the Able2Extract program from the desktop.
4. Click on the folder to open the commission statement - go to the desktop and find the file.
5. Click Edit, Select all pages in order to convert all pages at once.
6. Click the excel icon so that it will convert to excel.
7. When asked which option you prefer, click custom.
8. The commission statement should now appear like a grid with a bunch of lines.
9. The only section you care about is the “Advance on Issue” section. The other sections don’t matter at this point. You want to make sure the vertical lines are positioned correctly for each column in the “Advance on Issue” section. If they are not positioned correctly (meaning that date will be cut off or not put in the right column, you can move the line by right-clicking your mouse and moving the line). If there is an extra line that you need to delete—double click the right mouse. If you need to add an extra line, simply position the mouse where you want the line and click the right mouse once.
10. Once you have the vertical lines positioned correctly, click the excel icon again.
11. The data should convert to excel and pull up the converted excel spreadsheet.

• SORTING DATA IN EXCEL SO THAT YOU CAN IMPORT IT

1. As previously mentioned, the only information that you are currently concerned about is the information listed in the “Advance on Issue” section.
2. Delete all the rows above the “Advance on Issue” section and delete all the rows below the “Advance on Issue” section.
3. Highlight all the remaining information from the commission statement.
4. Click on Data, Sort.
5. Select the column for Writing Agent that is in the “Advance on Issue” section. (probably column E)
6. Delete all rows with information for agents other than your name
7. Delete any total rows.







• GETTING THE REMAINING INFO IN EXCEL READY TO IMPORT

1. Insert a column after Column B Policyholder.
2. Go to Data, Text to Columns.
3. Selected Delimted, Next, check the “Other” box and put a column in the box. Click next and then finish. You should now see the last name and first name in separate columns.
4. Insert another column after Column C (first name)
5. Go to a blank excel worksheet and type in the following formula:
=CONCATENATE(B4,”,”,C4,” “,A4).
6. Copy the above formula into the commission spreadsheet next to the first name of the first client. You should see the following information in that cell now LAST NAME, FIRST NAME, ID NUMBER.
7. Copy this formula down so that each row has the combined name and policy number.
8. This formula will combine the name and the policyholder into one column so that it will be easier to find in QB.
9. Once each line shows the combined data, select all the data in that column (except the header) for Column D. Right click the mouse and select Copy. Right click and select Paste Special and then select Values. Now in column D, you should see the same data as before, but it will be the actual data and not a formula.
10. Insert a column after column D and make a header called Contact. Type in the contact person for each policy.
11. Insert another new column after column F and call that column Start Date. Rename Column H Issue Date instead of New Business Period.
12. Copy the dates from Column H to Column G as well.
13. Delete the Writing Agent column
14. Insert 4 columns after the Premium column so that you have a blank column J. Call these new columns Job Description, Phone, Alt Phone, E-mail. Input the phone and email information if desired.
15. Delete all other columns except the amount of the commission advance.
16. After the commission advance column, add a column for Opening Balance As of Date. Copy the Issued Date into this column.
17. Select the data in the commission column (N). Right Click and select Format Cells. Change to general format instead of Accounting format.
18. Change the format in columns G, H, and O to a Date formal. Select the data, right click and select formal cells, select Date.

• Importing/Copying into QB

1. Go to Quickbooks and select Lists, Add/Edit Multiple List Entries.
2. Go to the first blank line at the bottom of this list.
3. Copy the data from the excel sheet (do not include column headers). Select the data in the excel spreadsheet and then go to the first blank line in the list in Quickbooks, right click and select paste.
4. Make sure all the information was pasted to the correct columns in QB. Make any error corrections in necessary. The commission advance should be in the Customer Balance column. This will show up as an opening balance invoice.
5. Click Save changes.

CONGRATULATIONS—THE DATA IS NOW IN QUICKBOOKS.

6.

• Final step for the weekly renewal reports – add the chargeback information to Quickbooks.

1. If there were any chargebacks on the commission statement that you imported, these will need to be posted as a payment to the customer account in order to clear out the balance.
2. In Quickbooks, Click on Home, then Click on Receive Payments in the Customer Section.
3. In the Received from box, type in the last name and find the correct policy.
4. The date should be the date of the report.
5. The amount is the “Advanced Commission” amount minus the “Earned Credit” amount. This amount is usually different from the chargeback amount listed due to timing differences of when AIO finds out about the chargeback.
6. In the “Deposit To” line, select the appropriate insurance company for the commission advance account.
7. Put “chargeback” in the memo line.
8. Click Save and Close. IF there is an overpayment, an alert will come up, simply click OK.

You can delete the report that you saved on the desktop. You no longer need it.