Print This Post

Does QuickBooks 2012 Condense and Period Copy Really Work?

February 16, 2012 | By | 36 Replies More

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.

Transaction Detail

Let’s also look at a balance sheet, which I have modified to show all dates and have a column for each year.

Balance Sheet before condense

I’ll start the Condense to remove transactions before 2011.

Condense settings: Date

I choose to have one summary journal entry.

Condense settings: Summary

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

Transactions after Condense

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.

Balance Sheet after Condense

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.

Selecting a Period Copy

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.

No Summary

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.

Transaction after Period Copy

Wait, the balance sheet is all wonky?

Balance Sheet after Period Copy

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.

Adjusted Balance Sheet

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.

File statistics at start

Here is a balance sheet, for all dates and with years as columns. I’m just focusing on one part of the statement.

Starting Balance Sheet

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.

Statistics after Condense

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.

Balance Sheet After Condense

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.

Period Copy

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!

Statistics after Period Copy

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.

Adjusted Balance Sheet

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!

Tags: , ,

Category: For Consultants/Accountants, QuickBooks Tips/Tricks, Technical QuickBooks

About the Author ()

Charlie Russell is the founder of CCRSoftware. He's been involved with the small business software industry since the mid 70's, and remembers releasing his first commercial accounting software product when you had a one-floppy disk drive system, loading the program from one floppy and then replacing that with the other floppy to hold the data. He has a special interest in inventory and manufacturing software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor with additional certifications for QuickBooks Online and QuickBooks Enterprise. He also is a Xero Certified Partner. Visit his CCRSoftware web site for information about his QuickBooks add-on products. Charlie can be reached at [email protected] He is also the author of the California Wildflower Hikes blog Connect with Charlie at Google

Comments (36)

Trackback URL | Comments RSS Feed

  1. William "Bill" Murphy says:

    Charlie – Thanks for testing these features again, I have made several comments in the past few weeks in various forums about my ‘change of mind’ regarding this functionality, and it appears you also now have much the same mentality (on this issue).

    I know that we were both ‘big fans’ of the new Condense versions during and immediately after the Beta tests; and I am hard pressed to explain why it seems that things have gone bad (indeed backward) with these features now that we are in ‘production versions’.

    In your real-life example there are only moderate variations, still the same they are variations and if you had compared pre-condense inventory valuation reports with post-condense reports you might be able to isolate the cause of the differences and then post a manual adjustment, but this is not the point. We still do not have a sound feature that is working as it should…that is the point.

    In reviewing these processes using the exact same file I used during Beta- when it tested perfectly, production versions do not produce the same results (despite there being no activity in those file copies since the earlier test).

    I also continue to hear more problems reported in using the feature in other forums. In many instances, Users are reporting that QB condense will leave hundreds of transactions that should be ‘condensed’ (but that are not being eliminated because of the algorhythm QB is using to try to determine what is ‘open’ vs. what is ‘closed’)….oh you can run a report to see what QB thinks it will change ahead of time, but in many cases you can’t track down why an item is open (or isn’t).

    While Intuit has made some improvements in this functionality over the prior years’ clean-up “CRAPPY” feature, it still isn’t what it should be. It needs more work.

    Murph

    • Thanks, Murph – one of those discussions prompted me to review this feature (plus, having a good “real” test database in hand to try). I’m not allowed to post links to my article in that one particular discussion forum…

      • William "Bill" Murphy says:

        I wish I had more ‘free time’ to play around testing some other huge files, but condense consumes so much machine time on those big files, that even running it on one of my ‘secondary’ machines still puts a crunch in total resources to do my ‘real work’.

        Murph

        • Dewanapagal says:

          I spent weeks trying to condense company file but it always crashes with a message “Not enough memory to complete this task” and computer freezes. Maximum it has gone up to 33% of verifying data and that’s he end of it. I tried with multiple computers. It never worked. QB is not becoming any interesting tool anymore.

  2. Peter says:

    We have never condensed our company data. I have transactions in Quickbooks dating back to the late ’90’s and would like to get things cleaned out/up a bit.

    I have 2012R6 installed on one machine as a test bed with a copy of our companys data file to play around with prior to going “live” on the other 20 workstations at our facility.

    I have been playing around with the condense feature to see clean up the company file (Now at 624,327MB) It gets past the first stage of 4 fine but as soon as it attempts to proceed to the second stage it errors out.

    I have done rebuild and verify, sorted all my lists prior to that, even ran the Q-books diagnostic tool on the file, no errors found BTW, and the condense still errors out. Now am going to try a year at a time starting with the oldest I can find. Anyone got any other suggestions to try?

    • Peter, I’ve not had my hands on a system that errors out like that so I can’t add much.

      If your company file is on a server, either run the condense from the server or bring a copy of the file to your local system. Don’t Condense across a network.

      You may find, with that old of a database, that you are better off talking to the folks at http://www.qbornotqb.com to do the compression. They charge a fee, but they have an excellent reputation and a reasonably quick turnaround.

    • William "Bill" Murphy says:

      Peter – while you say the verify runs fine, have you reviewed the QBWin.log to see if there are any issues that still pass verify.

      By the way, just remember if you take a year by year approach, just make sure to keep copies of any file that makes it thru on clean-up prior to moving on to the next clean-up session.

      Murph

      • Peter says:

        Bill, I did check the logs and there was nothing suspect that stood out. The very last line reads “Message 3228 … LVL_error–Quickbooks has encountered problem and must shut down”

        Now I am reading on other blogs and firums that the condense feature in Enterprise is “broken” and that it is a known issue.

        Charlie: FYI: I was attempting the condense on a stand-alone machine, off the network.

        • I’ve seen other reports on crashes, I’ve just never had it happen here (nor do I have a client who has run into that issue). And there isn’t an “official” word on this from Intuit (not that I would expect that). Just more reason to be worried about this feature at this point.

  3. One thing to keep in mind with a period copy for an audit situation is that the saved pdfs of bank reconciliation reports will NOT be altered since they are a snapshot of the conditions that existed when the reconciliation was performed. So it is likely that any period copy will have some information about transactions outside the period available through these reports.

  4. Haiden Turner says:

    The “Don’t create a summary” option sounds like such a great way to truly shrink a file, but watch for the unexpected, undocumented gotchas.

    I guess they shouldn’t be completely unexpected when you think it out, but apparently no one at Intuit did.

    Ok, you’ve chopped off all of the open A/R and A/P as of your cut-off date; so what happens to the payments and credits that statisfied those receviables and payables after the cut-off date? Well, they are still there and will need to have those open items from the before the cutoff imported back in as part of the beginning balances, and then these now unapplied payments and credits will need to be reapplied.

    No problem you think, that’s what CDR in the Accountant’s edition is for; however, you will find that although the CDR seems to work it really doesn’t because those unapplied items still have a ghost-like link to the chopped off transactions to which they had been applied to originally.

    So the CDR seems to connect the dots, but then you look at the payments in the customer center and find that they are overapplied (doubled). They fix for this is to open each received payment, uncheck the invoices it thinks that it is applied to, then recheck them and save. Pretty miserable when you had approximately $2 million in A/R at the cut-off.

    This is just the tip of the iceberg because unless you are very careful and only have a simple circumstances to handle the CDR will misapply things; especially where you have prepayments, lots of transactions of the same amount, etc. And, try to imagine how you are going to fix the undeposited funds account.

    Bottom line: a 1 GB file went to 300 MB retaining one year of history, but this is not for the faint of heart. I’ll probably do it again since I know what to expect and now have some strategies on how to handle it.

    • Thanks, Haiden.

      My intent in this article wasn’t to provide a detailed outline of how to perform this task AND do all of the necessary cleanup. You pointed out some additional aspects to this task,

      What I wanted to do here was to show that this feature is not the simple fix that everyone was hoping for, that there is a lot more going on here than people realize. It isn’t just a slam dunk, it takes a lot of work. And, even with that, I still feel that there are some things going on here that are just flat wrong.

  5. Nancy Smyth says:

    Great article Charlie. I’ve been having concerns about the Condense feature myself and asked Shannon Tucker {QuickBooksUsers.com} about it. He did some test comparisons and actually wrote a guest blog post for me on my blog. You can read Shannon’s thoughts here – http://blog.sunburstsoftwaresolutions.com/2012/02/14/how-well-does-the-quickbooks-2012-new-condense-work/#.Tz2CeI6Q30c

    • Thanks, Nancy! I’m behind in my reading, so I haven’t looked at your blog for about a week and I didn’t see that you were wondering about the same kinds of things I’ve been worrying about! Thanks for pointing out your article to me.

      I mentioned one third party data repair company in my article, I should mention that Shannon Tucker at AccountingUsers Inc. also does a great job in providing this kind of service (http://quickbooksusers.com/file-shrink.htm)

  6. We used the condense feature as a means to create new files with no transactions for 2012 (for files that were getting “large” by the end of 2011) vs our previous method of starting completely from scratch and importing the available QB lists via .iif. We were thrilled that we saved a huge amount of time because we didn’t have to set up all of the employee payroll data that doesn’t carry over using the .iif method. We also preserved all of our memorized transactions and reports, which are a pain to recreate and all the users, their permissions and preferences and customized icon bar settings were left intact. For us the fact that SmartVault documents attached at the customer/vendor/employee/account level (vs attached to transactions) are visible from either our old 2011 file for the same company and our new 2012 file was a bonus. The same was true for ViewMyPaycheck data–end user employees see uploaded data from both years there when they log in.

    • Stacy, that is another variation of how you can use the product. I wasn’t covering every aspect of the program. You point out a great use for the feature. That will avoid the issues I’m concerned about here. Great to know how it works with SmartVault, one of my favorite add-on products.

  7. Andrea Lee Carter says:

    Nothing was mentioned here, but my past experience with the QuickBooks condense feature is that the journal entries that are created as a result of the condensing can create issues down the line, especially if A/R or A/P is involved. Those journal entries cannot be deleted or edited. They are firmly cemented in place, right or wrong. And if they are wrong, good luck explaining them to your boss! And I have seen comments on the QuickBooks Community about bank reconciliations being messed up, especially the beginning balances for the next reconciliation. To me, the little space that is saved in the condensation isn’t worth the headache if something goes wrong, especially if a large data file is involved.

    As for the Period Copy, if you go and make adjusting entries to make the Balance Sheet “work”, don’t you think someone is going to ask what was that adjusting entry about? Just wondering…

    Andrea

  8. Charlie, we’ve condensed files where the inventory valuation account balance changes in a small way. It’s because of rounding errors that accumulate when all the old inventory transaction detail is consolidated into summary entries. The change in the inventory account balance is almost never of accounting significance, though. In your real data example, that’s obviously not the case; something much more than rounding errors is happening. Some of the pre-cut off years are off in inventory by $1M+.

    Which brings up a question: For the real data test, QB Condense used the single journal entry method. So how would one be able to report prior years’ ending balances at all? For example, if it got rid of most of the 2009 transactions, but created a single journal entry as of the end of 2010, how could it run an accurate balance sheet or P&L for 2009? In the real data example, it looks like cash is way off in every year too. Do you think that users are supposed to be able to run accurate financials for pre-cut off years, or are those supposed to be off limits after the Condense? Or am I not thinking straight about this?

    • Shannon, I didn’t test out all variations – I either selected NO journal entry or only ONE journal entry, and in these cases you don’t expect to be able to see financials for prior years. That is another variation, which I didn’t go into here. I would expect that you would see less of a variation if you left incremental journal entries, but I didn’t test that here.

      This wasn’t an extensive diagnostic test. I didn’t run more than the two files through this (the simple test and the “user data” test). I didn’t look at all of the variations. That is something that is way beyond what I have time for at this point. This article took a lot more time (and effort) to generate than I expected as it was!

      • Charlie, yes, there are lots of variations and it would take forever to examine all the combinations in detail. Just imagine how much test time Intuit’s QA/debug team must have put in! I just hadn’t thought about the implications of prior year reporting until I read your post, and it got me curious.

      • …I should have said that I hadn’t thought about how the QB Condense would handle it. When we do these, we tell the client that they should run historical reports for earlier years from their original file, not the compressed one. Thanks for your review.

  9. What happens to the systems audit trail after the period copy?

    Thanks

    • The audit trail, in my earlier experiments, matched the resulting file. That was good. I’ll be honest and admit that I didn’t look at the audit trail after THIS test. I was too unhappy with the effect on the balances…

  10. Donna says:

    QuickBooks period copy does not currently work as intended and is a known issue at Intuit. I attempted to do a period copy for an IRS audit and it only partially did what it was supposed to do. I just re-checked with Intuit last week and was told they were still working on it.

    • Donna, several people have commented that Intuit Tech Support said something along that line. I’ve not seen any “official” announcement of that (not that I would expect to hear something from them like that). So I’m writing about this so that people are aware that there is an issue. If they give us an update for it then I’ll try my same test data…

  11. I think there’s one additional step that would be required to fix this issue. Period Copy is just creating transactions in the period you specified. You can create a summary journal entry to bring forward the beginning balances as you did. However if inventory is off, then the inventory adjustments QB created may be off. I would create a new inventory adjustment as of 12/31 of the prior period to correct and bring forward the correct quantities and values. That is what drives the inventory account. It should be correct after that…though this is theory and I have not tested it.

    • Actually, if we were looking at the Trial Balance as well, then Cost of Goods sold should be off by about the same amount if it is indeed the beginning inventory quantities and values which are throwing everything off. ASSUMING everything for the period copy dates does come over correctly, then it “should” just be a matter of correcting the beginning inventory quantities and values via an inventory adjustment.

  12. Diane Offutt says:

    Charlie,

    Rather than rely on Intuit’s Period Copy (for an IRS audit purpose) wouldn’t it make sense to just buy Karl Irvin’s product and pull out the year under audit and create an “audit year company file”?

    That was what I planned on doing if ever the issue comes up. Then Intuit came out with this Period Copy. Personally I feel it is an issue we should fight in court for IRS should not be allowed to just call for a company’s COMPLETE accounting system when undergoing an audit that covers certain years. One would not tell IRS to go on over to where we have all our archived disks and give IRS access to all of the info since the beginning of time?

    I guess someone will fight them (maybe me – ha ha), meanwhile we accountants need a program that will allow us to pull out the information (transactions, audit trail, void delete report and financials) for a particular year. And yes, the bank reconciliations will definitely have items that have not cleared on them which MAY trigger an IRS agent to request backup on that particular transaction. I would just look at the last bank statement and see what exactly is STILL not clear and discuss with the client as to WHY. Now, that discussion should occur before the audit begins.

    Great article Charlie. And this discussion is full of great information. I plan on saving the whole discussion for future reference.

    Diane Offutt, Enrolled Agent, MAcc
    Woodstock, GA 30189
    Accounting Connections, LLC

  13. Diane, I’ll leave the fight with the IRS to you. There is another article in this blog that talks about the IRS issues. I’m happy to say that I stay away from that.

    Certainly there are many different ways to use third party software or services to extract the information. This involves extra expense, and possibly a lot of experience in using a tool. Karl’s excellent products (for example) can’t always get ALL data transfered out, due to limitations of the QuickBooks SDK (Intuit doesn’t give us access to ALL data in the database, unfortunately).

    The issue here is that there is a built-in feature that should do all of this work for no extra charge – but it just isn’t as simple as it should be.

  14. fran reed says:

    Charlie,
    Great article…And thank everyone for their comments…I have a client that I will try Condense and period copy with…..

  15. Brian Sweat says:

    “Wait, the balance sheet is all wonky?” :)

    As always, great article!

    The built-in Condense function is the best way of reducing a QuickBooks file when using a third-party application that relies on the “Last Condense Date” via the QuickBooks SDK.

    I really appreciate your thorough analysis and will pass this on to our clients looking for more information on the Condense function.

Leave a Reply