How to Convert Enterprise-Sized Clients to QuickBooks Enterprise Solutions
Some time ago, I was approached regarding a promotional products company that was using an antiquated accounting and ordering system. It wasn’t just one system; more accurately, it was a mishmash of several disjointed systems, including an old software package that no one could figure, manual records and Excel.
The resulting backlog and errors were downright scary. The company needed to bring its accounting system into this millennium, but there were many lists and balances to bring over to a new system. For example, the company had almost 20,000 customers. Remember, promotional products companies have many customers who order once, and may/may not ever order again. That’s why their customer list was long and was going to grow at a crazy pace as time went on. Obviously, there’s a lot more to this story, but you get the picture.
Given the number of customers the company wanted to preserve (yes, they wanted to keep all of them), it was obvious that this was a job for QuickBooks® Enterprise Solutions.
My major tasks, after creating a skeleton QuickBooks Enterprise data file with appropriate company preferences (such as turning on account numbers) and sales tax items, were to:
- import the lists (chart of accounts, vendors, customers and internal sales reps);
- enter the opening balances for A/R and A/P, opening balances and quantities for inventory; and
- enter the rest of the balance sheet’s opening balances.
I needed to do all this over a weekend, so that the client could “hit the ground running” and start invoicing and receiving payments on its receivables Monday morning. My more elaborate tasks, such as creating custom reports, could wait until the file was live and populated with current transactions.
The rest of the tasks, such as creating users, customizing invoices and other templates, and entering employees and calendar year-to-date payroll and liabilities, fell to the part-time bookkeeper who wanted to complete those parts of the project. She started with a very simple invoice template on Monday morning so that invoices could be emailed out and continued customizing from there.
Getting the Lists Into QuickBooks
The main lists I wanted to import were the:
- chart of accounts (pretty lengthy);
- customer list (at almost 20,000 names, it was extremely length); and
- the vendor list.
For the chart of accounts, I had no choice but to export what I could from the combined existing systems, and copy and paste the columns into an IIF file. I imported that IIF file into the skeleton data file I created. It wasn’t pretty, but I had no choice for creating GL accounts unless I wanted to enter them manually. There was another choice, as it turned out, but read on!
For the uninitiated who must import an IIF file into a QuickBooks file to populate a list, that’s for another article … maybe. I hesitate to spend too much time on IIF files because they are considered a “deprecated technology” by Intuit® who carries with it no safeguards, such as error logs.
My favorite list to import by far was the customer list. I had the bookkeeper give me a spreadsheet of the entire 20,000-name list of customers. I made some edits to the spreadsheet, including adding two columns in that spreadsheet for the tax code and tax item based on each customer’s location. I had sorted the spreadsheet by state and used some logic formulas in Excel to populate those columns.
In QuickBooks, I then used the feature in the Customer Center called “Add Multiple Customer:Jobs.” In that screen, I customized the columns to add the Tax Code and Tax Item columns for each customer, then it was just a matter of copying and pasting columns from the spreadsheet into the Add/Edit Multiple List Entries screen. I accomplished it in no time.
The vendor list was pretty much the same type of work as the customer list, except there was no tax code or tax item and there were about 19,500 fewer names to import.
Getting the Opening Balances Into QuickBooks
For the opening balances for accounts payable and accounts receivable, I used Transaction Pro Importer by Baystate Consulting (TPI).
Baystate’s TPI, available for $199.95, allows the QuickBooks’ user to map spreadsheets to import information into QuickBooks. These spreadsheets can include lists – I slapped my forehead when I realized I could have imported the chart of accounts using this tool – and transactions of various types. The mappings can be saved for future imports of similar spreadsheets.
I was able to import the open A/R and A/P transactions that were sitting in an exported spreadsheet, telling TPI to post them all to Opening Balance Equity, which I would later zero out to Retained Earnings. The same was true of the inventory items, because TPI’s allowed transactions include inventory adjustments. The rest of the balances were entered via a general journal entry, again using TPI and a spreadsheet exported from the old system. This was the best $200 I ever spent!
I realized in retrospect that I could have used TPI for importing the lists, including the chart of accounts, for which I thought I had no alternative but using the antiquated IIF method.
Naturally, there were more things to do, such as changing the opening the bank balance to the statement amount as of the conversion date, and then entering all transactions that had not yet cleared the bank to Opening Balance Equity. This ensured that the bookkeeper’s first bank reconciliation would be flawless.
This conversion was a multi-stage process – first the skeleton file, then the chart of accounts, and then the customers – as you can see. I should point out here that any time that I completed a stage, I created a backup file that I named very distinctly, starting with a stage number and then a description of what the stage included. For example, step 1 was the skeleton data file with the preferences and account number set up. Its backup file was named “1 Smith skeleton and preferences.QBB.”
Step 2 was the importation of the chart of accounts, so its backup was named “2 Smith skeleton preferences COA.QBB”… and so on. This was important in case I made a mistake anywhere along the way or in case the client came back with an “oops I forgot to tell you we entered more transactions after we said we wouldn’t,” which clients often do. I used backup rather than portable files, because it was easy to change the name before the backup was made and backing up was faster than making a portable file.
Afterwards, we had cleanup to do, such as zeroing out Opening Balance Equity to Retained Earnings, but those tasks weren’t urgent. The key here is the client got the data file and the bookkeeper was able to start using it right away after the weekend.
The conversion was fantastic; all it took was a little planning and some extra tools. True, it was something of a lost weekend, but it was totally worth it.