QuickBooks 2012 introduced some big changes in how you can condense/compress your company file. I talked about this last September in an overview article about Condense and Period Copy. Since that time there has been a lot of discussion about this feature, so I thought it would be a good time to go back and look at it in more depth. Does Condense and Period Copy really work? Let’s take a look at what is going on, using a small sample file as well as a much larger “real” company file.
To “Condense” a file you are trying to accomplish a couple of things. You want to reduce the number of transactions (and possibly list entries) if your file has been used a long time. Large numbers of transactions may impact performance and bog things down, and in some cases you may find that you are reaching the limits to sizes of your lists. You also may be looking to make the physical size of your company file smaller. We’ve had variations of this feature over the years, although the name has changed from time to time. Last year it was the “Clean Up Data” function, rather than “Condense”.
Those of us who have tried the condense or cleanup functions in QuickBooks in the past (before the 2012 release) multiple times generally have the opinion that this hasn’t worked very well. It didn’t reduce the file size enough, it didn’t get rid of enough transactions. Many people found that they got better results by sending the file to www.qbornotqb.com and letting Matt Clark condense it (for a fee). Also, I’ve seen a lot of complaints about the integrity of the file after the QuickBooks “clean up” process.
So this year there were a number of changes and improvements in the process. In addition, if you have the Accountant version of QuickBooks 2012 you have a new option that we refer to as “Period Copy”. This allows you to create a company file that is HIGHLY condensed – to the degree that you have no transactions before your beginning cutoff date (well, almost no transactions) and none after the ending cutoff date. This is something that many people have been asking for, and it may be applicable to certain audit situations.
As important as reducing the number of transactions and shrinking file sizes, we need these functions to work accurately and give us a file that accurately represents the financial status before the condense.
I’m going to present two test cases. There are many variables in how you would run a Condense. I’m going to focus on two variations – dropping transactions before a cutoff date and having QuickBooks create a journal entry for the older information, and doing a hard cutoff “period copy” where I have no transactions before or after the period I’m working with.
A Simple Test Case
I’ll work through a very simple test case to start with, using Enterprise V12.0 R6 (it works the same in Premier and Pro). We won’t see a savings in file size, what I’m looking at here is how this affects transaction counts and if it does the work correctly.
I’ve created a brand new empty file, enabled inventory control, added a bank account, an item, a vendor and a customer. I receive an item, sell that item, receive payment, deposit the check, pay the bill. I do that sequence once for 2010, 2011 and 2012. A very simple and unrealistic scenario, but it keeps things to a manageable size for a test.
Here is the entire list of transactions. Note that if you click on any of the images of reports you should see a larger copy that is easier to read.
Let’s also look at a balance sheet, which I have modified to show all dates and have a column for each year.
I’ll start the Condense to remove transactions before 2011.
I choose to have one summary journal entry.
I also set this to summarize inventory transactions, remove all transaction types, and remove unused list entries (be careful with that, as it can really change your Chart of Accounts).
After running the Condense we can look at the transaction report and see that there are a number of inventory adjustments and journal entries. My test case is a very artificial one, so in this case it doesn’t look like we have saved any space or removed many transactions. But in “real life” you should get better results. Note, though, that you do see that the details (bills, sales receipts etc.) are gone from 2010
More important, you see that the balance sheet matches the one from before the Condense. That is the key issue I’m looking at in this test, and it looks fine.
Now, how about a “Period Copy”? Let’s say that I just want a copy of 2011 to give to someone. I’ll start with my original un-condensed file and select the option for a date range. Note that this option is only available in the Accountant editions.
In addition, I am not going to create a summary posting. I want this to be as clear of entries as possible, so that there is no view into the past.
Ah, now we see that the transaction list is considerably smaller. No 2012 transactions, and the only ones in 2010 are some inventory adjustments as QuickBooks fights to keep things in balance. There are no journal entries, as I requested, but you still have some inventory adjustments so that your inventory balances are kept accurate.
Wait, the balance sheet is all wonky?
However, that is OK – keep in mind that we chose to get rid of EVERYTHING, not even allowing a journal entry. To get a proper balance sheet we have to manually enter balance forward adjustments for December 31st of the prior year (2010). The details of how to make this all work out properly is something that you should discuss with your accounting advisor. For this simple test I created a clearing account in Equity and created a posting for each of the accounts to make things match. I just had to look at the ending balances for 2010 in my balance sheet that I printed earlier. This isn’t necessarily the exact process that you would take. However, as you can see, if I adjust the 2010 accounts, my 2011 accounts now balance properly.
So far so good! With this very simple test we’ve show that the Condense and Period Copy options are giving us the kinds of results we expect. I can’t show you that the file size has changed much, as this was just too simple of an example.
Condense with Real Data
Now, let’s take this further. I have a “real” account that has been in use since 2008. Let’s see if we still get the accuracy, along with some file size compression. In preparing this file I did a “Rebuild” and then another “Verify”, and examined the log files for errors. I didn’t find any.
Here are some statistics from the beginning file.
Here is a balance sheet, for all dates and with years as columns. I’m just focusing on one part of the statement.
First, a normal Condense. As before, removing transactions before a date (01/01/2011), summarizing with one journal entry, summarizing inventory transactions, removing unused list entries.
Here are the results from the Condense. Note that the file size and number of transactions is reduced. Also I’ve removed a lot of customers and vendors.
However, if we look at the Balance Sheet, we see that Inventory Asset doesn’t match the prior statement. This is the primary change that I saw.
I have not dug into this in detail, so I can’t say what exactly went wrong here. The file is too complicated for me to do a simple analysis. The only account that is off is Inventory Asset (and Retained Earnings to balance it). Admittedly this type of Condense is a complicated thing to do when you have a perpetual accounting system like QuickBooks. However, if I understand the goal here, this shouldn’t be occurring. I expect the Condense feature to maintain the proper accounts at the very least. Sure, I want to compress the file and reduce the number of transactions, but if it messes up my Balance Sheet then what have I gained?
So at this point, as far as a regular “Condense”, I’m not yet convinced that this is something worthwhile. If you need to perform this kind of operation I recommend that you make backups, and that you check your reports before and after as I did here so that you can double check the results. I would create more reports than just the balance sheet – I would look (at the very least) at an Accounts Payable and Accounts Receivable aging. I would also want to watch my balance on hand for the inventory list.
Again, my simple test worked, and I cannot say if there was some sort of problem with the heavily used “real” data file.
Period Copy with Real Data
Now let’s look at a Period Copy. This is a new feature in 2012, and only found in the Accountant editions. I think that this may be a lot more useful than the regular Condense. Certainly if you have an audit, or if you are providing another person (perhaps an investor) with detailed financial information about a period of time. Also, this should give you the best results if you are looking for file compression.
I started off with the same beginning data that I used for the “Condense with Real Data” above. This time I selected a date range.
This results in even BETTER compression and reduction in transactions, names and items than before. File size down from 653420 K to 250844 K, transactions down from 83834 to 27662. Good!
Now, as you recall from the simple test, I know that the Balance Sheet will be off because there are no balance forward entries. So, I went back to the original balance sheet for the end of 2010, and entered adjustments for those accounts. I used the same approach as before, making adjustments to bring the balances back up to the value from the end of the period before the Period Copy/Condense.
As you can see if you compare it to the earlier Balance Sheet, my 2010 balances have been adjusted to match. However, you’ll note that my 2011 Inventory Asset account does NOT match the one from before.
If the 2011 transactions are the only transactions saved, if I adjust my balance forward for the prior year, why doesn’t my 2011 Balance Sheet match?
Another discrepancy, perhaps related, was in the inventory balances as of 12/31/2011. The quantity on hand was slightly different between the “before” and “after” files, as was the asset value reported on the Inventory Valuation Summary. However, the difference did not match the discrepancy in the Inventory Asset account in the Balance Sheets.
This is not a detailed, definitive test. It would take me days, weeks to work out why this is happening. I don’t have the time for that, do you? I’ll admit that I’m not a CPA, and perhaps I’m misunderstanding how this should work. There could be things that I’m missing, or some corruption in the database that I have not detected. Also, this particular file has a number of inventory balances that are negative, which does cause problems sometimes. However, from what I see here, the Condense process isn’t bulletproof. Yes, I was able to condense the file size, reduce the number of entries in lists, reduce the number of transactions, but I still have to chase down this discrepancy in Inventory Assets and figure out how to correct it. If the values from the condensed file don’t match, it doesn’t serve my purpose.
If any readers see that I’ve missed something here, or don’t understand how to deal with this, please leave a comment!