Tying out a QuickBooks to NetSuite migration
Balance the migration twice, at the initial load and at final cutover, with a Power Query model and a checklist for both passes.
How to balance a QuickBooks to NetSuite migration in two passes, so the problems show up before go-live instead of after.
This is part of our guide to Moving from QuickBooks to NetSuite. Loading the data is only half the job. The other half is proving it came over right, and that happens twice.
Initial load and final cutover
Balancing isn’t one step at the end. It happens twice.
The initial load is a full dress rehearsal as of the last month QuickBooks has closed, usually the month before cutover. Everything goes in: customers, vendors and items, the history trial balances, open AR and AP, open POs and SOs, inventory, bank items and intercompany. Then you tie all of it out. This is where the mapping problems show up, and you want them to show up now, while nobody’s waiting on NetSuite to do their job.
The final cutover happens once QuickBooks closes the cutover month. Customers, vendors, items and history are already in and already tied, so this pass is smaller. The last month’s activity comes over as one more summary JE. For the open items, load only what changed. New invoices, bills, POs and SOs from that month come over. Anything that got paid, received or filled in QuickBooks gets applied or closed in NetSuite. Inventory gets one more adjustment for the change in quantity, and bank items get updated for what cleared and what’s new. Everything that didn’t change is already sitting there from the initial load. Then you tie out again, and it should go a lot faster because the mapping has already been proven.
We build the tie-out as a small Power Query model in Excel. It pulls in the QuickBooks trial balance, the NetSuite trial balance and the mapping sheet, rolls the QuickBooks accounts up to the NetSuite accounts, and shows the difference for every line. If you loaded monthly trial balances, it does the same for each month, so you can see exactly which account and which period is off instead of hunting for it. Since it gets used for both passes, and every time you fix something and reload, you just refresh it.
Tie the income statement by month too, not just the balance sheet. A balance sheet can come out close while a month’s P&L is off because something got expensed to a different account or a different month.
On both passes, check the pieces that feed the trial balance:
- AR aging in NetSuite matches the QuickBooks AR aging, by customer.
- AP aging matches, by vendor.
- Inventory valuation matches the inventory asset account, and the opening inventory clearing account is zero.
- Bank balances match QuickBooks, and the bank clearing account is zero.
- Intercompany balances net to zero across subsidiaries, and the intercompany clearing account is zero.
Keep QuickBooks read-only for at least the next audit or tax filing. Somebody is going to ask for an old invoice.
Want to talk?
If you’re partway through a migration and the trial balance won’t tie, send us where it’s off and when you need to go live. We’ll help you find it. Book a free hour.
Author: Renee Chandler, Fourth Wave Consulting. NetSuite accounting and finance.
