Print This Post

QuickBooks 2012 Condense and Period Copy

September 15, 2011 | By | 37 Replies More

One of the big changes in QuickBooks 2012 is the Condense Data feature, and an option that is being called Period Copy. This feature has been one of the most requested improvements when I talk to both QuickBooks users and accounting professionals, and it looks like Intuit did a good job here. Is your QuickBooks company file getting too big? Condense it! Do you need to give someone a copy of your file that contains only one year of data? Use Period Copy!

Intuit likes to change the names of things sometimes, just to keep us on our toes. Are you paying attention? Last year this feature was Clean Up Company Data, found in the File/Utilities menu. This year it is Condense Data, found in the same location. We have all the features that we had before, but there are some new options.

Note that some of the new features are only found in the Accountant versions, QuickBooks Accountant (the “Premier” level product) and QuickBooks Enterprise Accountant. I’ll point these differences out. You will also find the Condense function in the new Accountant menu.

I would like to recommend that you also look at the excellent article by Michelle Long, QuickBooks Accountant 2012 – Create a Period Copy. An excellent article.

Condense Step by Step

When you start the feature you are asked what type of condense process you would like to perform. The first two options are the same as before, Transactions before a specific date and all transactions. If you have one of the Accountant versions, though, you will see a third option, Transactions outside of a date range. This new feature lets you set both a beginning and ending date, and all transactions outside of that range will be removed. This is a welcome improvement.

QuickBooks 2012 Condense Data

Why would you need this? There are several reasons. You may have a legal dispute over the financials for one year. Perhaps you are undergoing an IRS audit, and you want to hand over JUST that year’s worth of data. There is hope that the IRS will accept a file that has been processed this way if you are submitting information to an audit. We don’t have any way of knowing at this time if they WILL accept it, but there is hope. For more information on IRS audits and QuickBooks files, see our discussion at http://www.sleeter.com/blog/2011/06/irs-audits-and-your-quickbooks-data-file/.

Moving forward, if you have one of the Accountant versions, you will see the following window. Prior to 2012 the program I believe that QuickBooks always did the middle option, creating a summary journal entry for each month. Now we have three options.

QuickBooks 2012 Transaction Summary

  • If you choose to create one summary journal entry, you cannot run historical monthly or quarterly financial statements. If you want to do that, select the option to create a summary journal entry for each month.
  • If you choose to not create summary transactions you then have to manually enter “opening balances” for your accounts. This is an important option because it will chop off EVERYTHING outside of those dates. You still have to  enter a beginning balance to get things to balance, but this is very important.

The next window is a new option (and this is available in all versions of QuickBooks 2012). Certain types of inventory related transactions can be deleted, and the inventory activity they represent will be replaced with an inventory adjustment. Prior to this the “clean up” feature would not be able to remove these kinds of transactions, and in many businesses they represent a significant portion of all transactions. The hope is that this feature will lead to significant file size reductions.

QuickBooks 2012 Inventory Condense

The first option will remove inventory transactions and replace them with inventory adjustments – but it will still “retain all open inventory transactions”.

If you choose the second option then the program DOES remove inventory transactions, but when the process reaches an “open inventory transaction” it will then stop condensing inventory transactions. An adjustment will be entered that will summarize inventory activity up to that point, then no more summarizing will occur.

Continuing on, the next window gives you options as to the kinds of transactions that you can delete. New this year is the ability to include non posting transactions, those that don’t affect your financial statements. Another change I noticed is that the check boxes are checked by default, where last year they were un-checked by default.

QuickBooks 2012 Transaction Condense

Next we have the list entries window. Classes are a new addition here. Accounts is one you have to think about, do you really want to get rid of unused accounts in your chart of accounts? I was a bit surprised at how small my Chart of Accounts was after a condense in one test.

QuickBooks 2012 List Condense

Finally, we are ready to start the condense.

Begin Condensing

Cautions

  • Payroll transactions are retained from the current quarter and from the prior 4 quarters.
  • If you choose to create one summary journal entry, you cannot run historical monthly or quarterly financial statements. If you want to do that, select the option to create a summary journal entry for each month.
  • If you choose to not create summary transactions you then have to manually enter “opening balances” for your accounts.
  • If you are using the various options in Advanced Inventory with QuickBooks Enterprise you may come across additional warnings. I haven’t tried all the variations – here is what you get if you have serial numbers enabled:

Condense Warning

Let’s Try It!

Test data is hard to come up with. I can’t use any of my client’s data (for this blog), and my own test files tend to be messy. So, I created a quick set of test data just for this test. Three years (2009 through 2011) and just a few sample transactions. This is just being used to illustrate the process – I don’t cover all of the variations that we could have.

Before you jump in and start using this feature, I recommend that you print several reports for comparison. The choice depends on your business, but you may want to print a set of financial statements, a receivables listing, a payables listing. You want to make sure that things balance out when you compare the “before” and “after” reports.

Let’s start by modifying a Profit & Loss Standard report to show Year columns. (Hey, another 2012 change, the button is customize report instead of modify report).

Modifying a Profit/Loss Report

Here’s my P&L, showing three years of activity.

Starting Profit & Loss

In addition, I’ll run a Balance Sheet, modified the same way.

Balance Sheet Before Condense

There are numerous other reports that you can run, you need to determine what is important for your business. Inventory reports, aged receivables and payables, and more.

For our test, here is a QuickReport on the bank account.

Bank Account before Condense

And here is the inventory asset account before condensing.

Inventory Asset before Condense

The audit trail is a report that is very important, particularly in audits. Unfortunately, they are very large and hard to show here. So, here is a portion of the audit trail, just showing Bills.

Audit Trail before Condense

Condense!

I’m going to run the condense to get a “period copy” just for 2010, but setting things up so that I can still get prior period financials.

Condense QuickBooks for 2010

I’m selecting the create one summary journal entry option, as they recommend.

Condense Summary

 

Here is the “after” profit & loss statement. Note that you STILL get information for the prior condensed years. The balances are identical to the pre-condense report, OTHER than there not being a 2011 column.

Profit & Loss AFTER condense

Here is the balance sheet after. Again, all the numbers match, and 2011 is gone.

Balance Sheet AFTER condense

How about that bank account? You can see that a journal entry is entered for 2009, the 2010 records are intact, the 2011 records are removed.

Bank account after Condense

Here is the inventory asset account after condense. The list is shorter, but there are a number of transactions still there. These inventory transactions are ones that affect the quantity on hand, so you need the adjustments and other transactions to keep inventory counts correct.

Inventory Asset after Condense

How about the audit trail? It IS updated to match the condense activity. The older bill that shows in this report is still in the system, as it was an unpaid (open) bill. Closed bills were condensed, and aren’t here. All the 2010 activity is here. This is exactly what I would expect.

Audit Trail Bills after Condense

Here is the journal entry for 2009 that is entered.

Journal Entry closing 2009

One of the key issues we are looking at is reduction of file size, list size and number of transactions. My simple test file didn’t have enough information to be useful for that, so I ran the same kind of condense on a larger test file. Not a big file, but it had more activity.

Here is the starting information:

Starting file information

Here is what I ended up with after the condense. The file is smaller, the lists are smaller, there are fewer transactions. That is what we are looking for. Note, however, that you might want to un-check the option to condense “accounts” – I wasn’t thinking about that in my test and I was very surprised to see my Chart of Accounts being pruned heavily.

File info AFTER Condense

So, it looks like it works!

Period Copy Dropping ALL Transactions

As you have seen, this still leaves you with information from the prior periods. That might not be what you want if you are facing an audit situation. So, let’s take a look at the same starting file and see what happens if I select Don’t create a summary.

Condense Without Summary

Here is the P&L. The numbers are the same for 2010. Good! However, we still get SOME activity in 2009, with some item adjustments.

Condense Profit and Loss

Now, the balance sheet. The numbers are different! However, that is expected as we don’t have any beginning balances.

Condense Balance Sheet

 Looking at the bank account, we see that only the transactions for 2010 show. No period adjustment. Again, this is what we want.

 Condensed Bank

 The inventory asset account is interesting. There are two inventory adjustments with 2009 dates on them. These are left over from the build assembly transactions in 2009, one adjustment for the inventory assembly and one adjustment for the component. We haven’t been looking at the quantity on hand for inventory in detail in this article, but I’ll note that this inventory adjustment was a quantity and total value adjustment used to adjust the inventory levels to the proper quantities for the beginning of 2010. They are, essentially, the one begining period adjustment that QuickBooks makes for you.

Condensed Inventory

 How about the audit trail? It’s clean! No transactions other than the ones for that year, plus the two inventory adjustments (I’m only showing a part of the audit trail here).

 Condensed Audit

 This is really clean. You need to do some manual journal entries to get beginning balances set up, and that might be a bit tricky considering that there are SOME adjustments already done for inventory. But, other than those few inventory transactions, the 2010 file is complete and separate from any other year, and the audit trail accurately reflects what you have in that year. Note that this is NOT a copy of the file, this is the original file with the originally entered transactions. All the changes are before and after the period you select.

It Works!

Condensing has been a PITA issue in QuickBooks for a long time. I pretty much have given up on using it up until now. With QuickBooks 2012 it looks like they may have done it right finally. I will reserve final judgment, though, until we can see how this works in different situations, with different options. I ran some simple tests, it looks good, but it is still early.

There are many options in here that you need to consider when condensing. What are you trying to achieve? In one of my examples above you still get financial reports for the prior periods. You see some transactions that exist before the starting condense period. These are left in to preserve account balances and linked transactions, as well as holding on to “open” transactions. But you don’t have to make any sort of adjustments after condensing, the company file is accurate.

In my second example you get a very good “purge” of information outside of the selected range of dates, with just some inventory adjustments. However, you have to do more work to get your financial documents into shape. These should be dated the last day before your opening date (in my opinion, and note that I am NOT an accountant). These will show in the audit trail, and the “entered/modified” date will be the current computer system date. 

Think about your goals, play with the options.

Before we had this updated Condense function we had to rely on third party software or outside services. You’ll find many recommendations for the utility programs by Karl Irvin (Data Transfer Utility and others), and the file repair/condense services of Matt Clark (QB or Not QB) or Shannon Tucker (AccountingUsers, Inc). Will this updated version of QuickBooks replace the need for their products and services? Well, no. Certainly this update will have some impact on these companies, but they provide many other beneficial services. You still need the Data Transfer Utility if you want to move QuickBooks data from one company file to another. You still may need Matt or Shannon to repair a seemingly unrecoverable QuickBooks company file. I also have a suspicion that in some cases we could find that these other approaches provide you with an even more compact file. I have not run a comparison between the results you get from the new QuickBooks feature and what you get from these other approaches. All I can say is that I have a lot of trust in the quality and results you get from these three companies.

Tags: , ,

Category: QuickBooks Tips/Tricks, Software Updates

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 (37)

Trackback URL | Comments RSS Feed

  1. Lyn Barr says:

    I noticed in the “File Information” screenshots that the DB File Fragments actually went up. Interesting…

  2. Ah, Lyn, attention to detail…

    So many things affect DB File Fragments, and when I’m testing these things there often can be multiple processes in play at the same time. I wouldn’t pay much attention to that in THIS particular case, it may be due to other things that were going on.

    However, when you first try it, you might watch that. It may be that you want to go spend a bit of time working on that issue after the condense is done. But, I wasn’t looking at that aspect at this time.

  3. I tried it that way (extracting one year) and then I set the starting date (remove transactions before) to 1/1/10 and the ending date (remove transactions after) to today – it works, you can start a new file and go forward with it. That said, it did not work on one test file – I have no idea why. 50% success, but worth a try.

  4. Charlie, thanks for the kind words. I haven’t done any file size comparative tests with the new condense feature yet, but I plan to soon…

    Great post.

  5. William Murphy says:

    Charlie you have written a very lenghty, informative and well illustrated article on this very ‘complex’ topic, along with some ‘excellent commentary’. A person could write a whole ‘textbook chapter’ on just this new feature….’hum, the headlights just came on’.

    Anyway, I ran 12 different tests with the new feature, (all three functions, all three ‘modes’ and several option choices). A couple of these were very large files (in excess of 2 Gig); I got exactly the results I expected for the ‘mode and options’ that I configured on all the files except 1 (one) which had some ‘sales tax’ issues after [but of the files I tested only 2 had ‘sales tax’ in them, so I am interested in how ‘others’ who have tested with ‘heavy sales tax files’ find their data upon completion.]

    What is ‘critical’ is having a complete understanding of how all the various combinations (functions, modes and options)work together in order to determine which choices will get you the file you are really wanting. I will also mention that the ‘Help’ (from the Help menu, as well as the imbeded help prompts along the way during the process, provide good documentation).

    Sorry to just be posting this, a day late (and dollar short, so to speak), but I too have been busy doing 3 large ‘file corruption repairs'; no rest ‘for the wicked’ you know!

    Murph

    William “Bill” Murphy – Oklahoma City

    • And wicked you are, Bill/Murph…

      Testing this is a really complicated process. My tests were very contrived and short as far as what I displayed here. I have some more complicated ones I’m playing with, but I can’t show screen shots of them (client data…).

      I’m going to fall back on one of my other recommendations – I’m not recommending that any of my clients use the R1 release of ANY QuickBooks product. Too many potential problems, lets let it settle out first. And R3 is already being worked on…

      • John says:

        Mr. Russell,
        I have been trying to perform a condense on my QB 2012 Enterprise (R9) database for the last two weeks…DB is 1.7 GB and has failed everytime..no explaination, it just stops processing…QB apologizes poping up window appears asking for my Email address, but I haven’t heard a word from their support team….
        I have tried moving the DB to my desktop, no luck…I have performed numorious ‘repairs’ (although they were not needed, just to satify support)….

        I noticed your comments about sales tax, could that in anyway be stopping my condense from completing???

        Need help in NJ

        Regards,

        John

        • John, R9 was supposed to fix the bug where COndense wouldn’t work on large files – but I’ve not tested that myself.

          Sometimes your data has corruption and it just can’t be condensed in the normal way.

          The contact window that pops up – you often won’t get a contact back from that. If you have a Full Service Plan you should call your support contact on this issue.

          You may want to contact the folks at http://www.qbornotqb.com, they provide a condense service that generally is superior to what you may get from this particular feature, particularly if you have some data corruption in there.

  6. Often we see transactions that are NOT cleaned up. e.g. General Journal entries, Checks, where QB states transaction not paid.
    It appears that considerable cleanup work may be required before this will condense some clients.

    • Ben, in general, you don’t get great results with the Intuit tools unless you do the Period Copy feature – which then requires more work to set up initial balances. Other than that, anything QuickBooks feels that isn’t “closed out” fully will remain, and that can be a long, long list.

  7. Tammie says:

    Hi. I was able to successfully condense the year prior to the year I want to work with. Is there a way to delete transactions or condense transactions after a certain date?

    What I am ultimately looking for is a way to get just one year of transactions so I can send it to an outside accounting firm. I only want them to be able to see/access one year.

    • Tammie, that depends on what QuickBooks product you are working with. If you look at what I show above, only the “period copy” feature gives you a cutoff on BOTH ends. If you don’t use that (or if you don’t have a QB product that supports that) then there isn’t a simple way to do what you are looking for.

      You can use third party tools (see http://www.q2q.us) or services (such as http://www.qbornotqb.com) to get better results, but there is a cost involved.

  8. Julie Wright says:

    Dear Charlie,
    I do not know what I would do without you or Rustler. I don’t ever attempt to even call QB, as they typically do not know what I’m talking about.

    I am going to try this condense process Saturday morning, and wanted to check in with you first for advice. I am using Enterprise Solutions 12.0 R9. The R10 is out, but I haven’t updated yet (I am always cautious for bugs). My file size is 1.7 GB, 12 years worth of extensive inventory (7,200 items). First, should I update to R10? I am assuming I will need to verify and back up the file first. I will condense keeping the last 3 years worth of information on the system, and pray nothing bad happens. I will, as you mentioned, prepare financial documents first. Do you have any other suggestions?
    Thank you!

  9. Debbie Bosse says:

    Have a client using QB2013 that would like to try this option to remove 6 years of data from their file (leaving 2011,2012 and 2013) file size is 355M. Do you recommend using the built in condense utility or going with the BBTU/DTU utilities? I read in the NAN forum recently that one should stay away from this utility. unfortunatley I missed the call that outlined why. I’d appreciate your advice.

    • When you say “stay away from this utility” I assume you mean the condense feature in QuickBooks itself, not Karl Irvin’s add-on products (www.q2q.us).

      The answer is “it depends”. There are issues either way, and it depends on what this installation is like, and what the budget is.

      The newer Condense/Period Copy feature in QB 2012 and 2013 as you find it in the Accountant version is much better than the older condense/archive utilities we had before However, if you are doing a true “period copy” then note that you have to do a lot of added work, to get the beginning balance set up and more. However, this is cheap, because the feature is built in.

      Karl Irvin’s utilities are excellent, but you have to buy them and learn how to use them properly. So you have some expense and a bigger learning curve.

      One question would be if you are using any add-on products. If you do, then you have to talk to those vendors to understand what might happen. If you use Karl’s utilities (or something that works in a similar way) to create a new file, then you might break the connection to data in the add-on product. Many add-ons will use the internal ID to identify records, and when you create a new file that internal ID is replaced with a new one in QB. That may cause some data loss in your add-on. I don’t think that this will happen if you use the QB condense utility.

      You may want to work with a company like http://www.qbornotqb.com to do the condense, that costs more but it saves you from having to learn all the ins and outs of the various utility products. There also are several businesses around that can do similar things using various tools.

      • Debbie Bosse says:

        Thanks Charlie – yes I did mean the internal QB condense utility. I am familar with Karl Irwins tools and have used them in the past as well as the services of qbornotqb. Client is on a tight budget. Based on that I was going to try the built in condense utiltiy to remove the data prior to 2012. i’ll give it a try. if it doesnt provide favorable results I’ll try one of the others. if it works then we’ll be finished with little time invested, client will be happy.

  10. Dana Schorr says:

    Is this feature now available in 2014 Premier or only in the Accountant version?

  11. Ann Hansen says:

    I have tried the condense feature because our file reached the 14,500 limit for items and it didn’t remove any of the items. Any idea why this part of the condense didn’t work?

    • Not surprising, Ann – but it does depend on the options you select and the edition of QuickBooks that you have. If there is ANY reference to the item after the transactions have been condensed, then the item won’t be removed. Reducing the size of the item list is tricky.

      You might take a look at some of Karl Irvin’s utilities at http://www.q2q.us. Or talk to the folks at http://www.qbornotqb.com about shrinking things.

      • Ann Hansen says:

        F2 reveals last inventory deleted 1/31/1999, although it did successfully delete 20 items. We have QB Pro 2012. Selections made on condense were:

        Remove trx before 1/1/2010
        Summarize inventory trx
        Remove the following trx: left all boxes checked
        Remove unused list entries: left all boxes checked

        I contemplated purchasing Data Transfer Utility, but we would have some other issues to resolve. Sales tax rates changed twice and I would have to reenter the whole calendar year of payroll. Thought this would have been a better solution, but am disappointed it is not working.

        • Ann, the condensing features available in Pro are often not very helpful in doing this. The “Accountant” version of the product has a much better chance of shrinking the file – you may want to work with a ProAdvisor who can help you with that. Or work with a service like the one I mentioned, they should be able to help you (for a fee, of course) and not force you to reenter data.

          You can “merge” items to make room in the list, but that is NOT a good idea because that can change your prior period financial statements. And it is tedious to do on a large scale.

  12. Bev Meteyer says:

    Hi Charlie,

    My client has Enterprise 13 (don’t know what release). I am doing a test of their data on my computer and it didn’t remove any payroll entries. It said they were outside the date range. I didn’t see any special place to specify payroll dates. The general date was all transactions before 1/1/13. this would have left only 2013 data and unfinished items.

    The last attempt at this said there was a data error, so I rebuilt the data before trying again.

    Any thoughts as to why these messages cam up and how to fix them? I’m really puzzled on the payroll issue.

    Thanks for all the time you put into these blogs – you are my first authority go-to guy.

    • Bev, if there is a data error, you need to fix that first before trying a condense. I can’t say what the error was without hands on the file – and a rebuild doesn’t necessarily fix all errors (you need to check the QBWIN.LOG file after the rebuild). You don’t mention what option you picked on condense – but note that the condense that is available in the user products will often leave many transactions behind for many reasons. The Period Copy is only available in the Accountant editions.

  13. Laura Geiger says:

    I have tried this process several times on a client file. Each time it errors out and will not allow the period close. I am using QuickBooks Premier Accountant 2013. I ran the data verification and the repair utility and still no success. Any suggestions would be welcome.

    There file is 342 MB (358,977,536 bytes)it has data clear back to the 1990’s and we need only the year 2011 for an audit.

    Thanks in advance for any suggestions you can offer!

  14. Jerry Davis says:

    Hi Charlie,

    What recommendations do you have for condensing a cash basis file for a specific time period? Say for time up to December 31, 2013. From what I understand the reports for the condensed period will be inaccurate. Any way around this issue? What about the reports starting January 1, 2014? Thanks, your posting are always helpful!

    • Jerry, I’ll be honest, I don’t look at “cash basis” very often, as I’m not an accountant and don’t have a need to. QB reports are always a bit funky with cash basis anyways, since it is just a way of changing how things are reported (there isn’t a functional change in the database, it is always accrual underneath).

      If you use the “period copy” feature you are going to have to enter adjustments for an opening balance. I can see how that would create some big issues with cash basis, most likely.

  15. Jerry Davis says:

    Thank you for responding so quickly. What the account wants to accomplish is to convert his 2013 QB Pro to QB Online. But he only wants to bring over transactions from January 1, 2013 forward to QBO. He would keep his 2013 QB Pro Software plus various backups on and off the his computer for all years of transactions. After reading your article I said to myself that condensing would do what’s necessary. But then that need for Cash Basis gave me second thoughts. Does this “Paul Harvey rest of the story” give you any other ideas?

    • Period Copy would limit the number of transactions that come over. You would have to post some adjustments to get the balance forward correct, and I would consider doing that in the QB side of things. As far as how this addresses cash vs accrual, that is something I’ll pass on, and leave that to the accountants.

      We do miss Paul, don’t we?

Leave a Reply