So, between 8-12 times a year, we get the request to condense some pretty large QuickBooks Enterprise files, usually larger than 1.5gb size (recommended limit for QuickBooks Enterprise). And when files are these large they become practically unworkable, these are the type of things that you experience:
- Data corruption, you see transactions with the wrong customer/vendor and/or item information; seemed to have been crossed with other transactions
- “Unbalanced” Balance Sheet reports where the total debits do not match total credits
- Inventory Asset, Inventory Offset Accounts, Inventory Valuation Reports, and/or Cost of Goods Sold do not seem to work correctly
- Slow performance, load and save time
- Constant errors and getting kicked out of the file
My personal recommendation is, to not have a company file no more than more than 1gb and no more than 20,000 active items/customers/vendors; which means that you probably need to limit the history to “last year” and the current year and eliminate all inactive items/customers/vendors.
Now, we normally perform this work over 3-4 days for our customers (typically over the weekend to minimize down time) and while the file is being “condensed” the business will be WITHOUT QuickBooks for a couple of days. We have charged anywhere between $3,000 and $9,000 just because of the amount of possible things that can wrong during the process and the amount of attention we need to pay during the process… Anyway, we DO NOT recommend using the built-in condense feature; because it doesn’t seem to really solve any of the issues listed above, so I am going to tell you EXACTLY what is it that we do, step by step:
- We run a verify to see if there is any corruption, perform several rebuilds and correct corrupted transactions
- Once the file verifies and the balance sheet is balanced, we are ready to start the work
- We must then pick a START DATE, lets say, for example, it is 01/01/2017; which means that all data up to 12/31/2016 will be deleted.
- We create several reports that we export to excel, such as P&L, Balance Sheet, Inventory Valuation Summary, A/R Customer Summary, A/P Vendors Summary, Open/Uncleared Bank Transactions, Open Undeposited Funds, and others (based on circumstance) as of 12/31/2016
- We export ONLY All open Invoices, Open Bills, Open Sales Orders, and Open Purchase Orders dated 12/31/2016 or older in case we need to import any of the them back in.
- We also export the entire customer list, vendor list, and item list in case we need to import it back in
- Then we open the file with Quickbooks Enterprise ACCOUNTANT**** edition and create a Period Copy from 01/01/2017 until “today” – effectively deleting all transactions dated 12/31/2016 and older…*** At this point, this process takes anywhere between 4-16 hours, usually we have two team members on working together ***
- After the period copy completes, we make a backup of the new split file before we touch it*** If we are lucky, this only takes one try, and takes anywhere between 4 hours to 5 days, depending on the size of the file, and we leave it running in an independent machine – sometimes it errors out, and we have to try it on a different machine or perform the rebuild/verify functions again. In some cases we need to condense one year at the time; this could take WEEKS to complete ***
- We verify all the reports and check to see if the conversion did not get rid of any data from 01/01/2017 until today. None of the reports are ever right, because we are missing open A/R and A/R Balances, plus Inventory is surely going to be off
- We import all open A/R balances and open A/P balances by making we manual journal entries as of 12/31/2016 to make sure than all beginning balances for customers and vendors are ok. In some cases (based on the client needs) we will import individual invoices, bills, and open payments
- We will then apply all the old A/R and A/P to get your Aging Reports and Pay Bills Screen functional just like before
- We adjust the inventory valuation by ITEM QTY to be correct as of 12/31/2016; which requires an Inventory Qty Adjustment
- Then, we adjust the inventory valuation by ITEM Value to be correct as of 12/31/2016; which requires an Inventory Value Adjustment (must be done in these two steps)
- Then, because of how QuickBooks calculates COGS using Avg. cost, it is likely that Inventory Asset will be of as of 12/31/2017, so we make a journal entry to adjust it to the previous numbers and then we reverse it as of 01/01/2018. Effectively, there is a small margin of error that will carry over in 2018 for Inventory valuation errors due to corruption or miscalculations that QuickBooks made in the first place. We find these to be immaterial most of the time
- And that is it, 3-5 days sleepless nights later.. you will probably have a much smaller and functional QuickBooks File!
- If there are any missing customers, vendors, or items – they can be re-created or imported using our previous export
Tools that we might use, depending on the case:
- QuickBooks Desktop or Enterprise ACCOUNTANT editions only!
- Transaction Pro Importer
- Transaction Pro Exporter
- Transaction Pro Deleter
- Zed Axis
- Q2Q.us Data Transfer Utility
- Built-in excel importing tools, Add/Edit Multiple Entries, and Batch Enter Transactions
- IIF Import Templates
What could complicate things? (and make this whole unworkable or increase the timeframe)
- Short deadlines, not being able to wait a few days for the process to finish
- Wanting to keep too much data 2-3 years of data is probably already too much
- Asking us to do this on premises
- Not wanting to delete unused items
- Having a lot of Customers with open balances on the jobs
- Having a lot of subitems
- Having Multi-currency enabled
- Having Enhanced Inventory Receiving turned on
Any questions hector@garciacpa.com
If you want to hire us, we can review your file and give you a quote.
**** If you want to do this process yourself, and you do not have the ACCOUNTANT edition of QuickBooks Desktop, here is an alternative method using the regular Condense
