QuickBooks Archiving Can Drive You Crazy – Financials Don’t Match

Posted: April 21, 2013 in SBS 2011

So right way, you might be suspicious that this post isn’t about SBS. Or Windows. Or servers. Or anything Microsoft. And you would be right.

Nevertheless, I thought this would be interesting for any of you who happen to rely on QuickBooks for your accounting.  As you probably know, QuickBooks keeps all your transactions as in the “interface” format as well as the underlying accounting infrastructure.  By that I mean it keeps the payment you received, for example, along with the credit to cash (bank account) and debit to accounts payable.

Over time, this detail can increase your file size dramatically, so Intuit built in a utility to shrink file size by purging all transaction detail up to a given point in time.  It leaves instead summary general ledger transactions reflecting the summary of those deleted transaction details.  It is a process you have to trust as it runs (usually for a long time) and spits out a new QB file.  And you work from there.

So imagine my surprise to hear from someone that I helped do this, getting rid of over 10 years worth of detail, that her new files were out of kilter with end of 2012 reports.  And that at least one check was missing.  So we jumped in to figure this out.

The missing check was easy.  It was a check written in 2008 but never cashed that had been carried forward in reconciliation but in fact never was going to be cashed.  It didn’t show up in the reconciliation any longer because it was in the deleted transaction period.  So the check couldn’t be voided because it was no longer there.  No worries, we created a credit back to the bank account and a debit to the expense account that the check was charged to.  That now appeared in the reconciliation report.

The P&L and Balance Sheet were different animals altogether.  After some snooping, three things appeared different:  COGS, Inventory on Hand, and Retained Earnings.  But why?

Drilling down into the items sold that make up COGS, we discovered a few cents worth here and a few dollars there.  No pattern.  Then I looked at how they figured COGS on their items:  average purchase price.  Guess what.  These items had been purchased for years, and their unit cost had risen.  So by dropping older Purchase Order Receipts, the average cost of the item increased because we looked at fewer POs.

I compared this to taking the average height of students in grades K-12.  If suddenly you start taking them for grades 6-12, you would not be surprised that the average number increased.

Not only did this affect COGS, but it also reflected an increase in Inventory on Hand as the calculation for that takes average cost as the basis.  And retained earnings was really too large because not enough had been taken for COGS.  Amazingly, although not really, the same adjustments were made all the way around and the 2012 EOY reports were exactly the same in the original file and the condensed one.  Mystery solved.

Wow, glad I am not an accountant who would have to figure stuff like this out for someone who is an accountant…

  1. Jeffrey, Upstate NY says:

    I am quite skeptical about the use of QB at all. There are enough incidents involving people who try to stay on one version of QB that they have purchased (and which has the features and capabilities they need or because Inuit changed the features and capabilities in a later release) and suddenly a problem occurs – either because their system crashes and they cannot reactivate the version of QB they had (even if they have the original software) or QB changes the online format so it no longer works with the version owned.

    (And that does not even consider that Inuit is reportedly responsible in part for the IRS not being able to calculate people’s taxes for free based on the data already reported to them, or which people could otherwise e-file directly with the IRS.)

    I cannot say I am surprised by this behavior of QB. As for the uncashed check, one would think that Inuit would be smart enough to exclude any transactions that have not cleared. But perhaps I give Inuit too much credit.


  2. Intuit is hardly the most customer kind software company I know of, and it is often infuriating to not have a chance to speak to a human about a problem without paying an arm and a leg, especially when the skill set of most of them is wanting. And yes, they have a habit of not upgrading their older versions to work with newer versions of Windows, among other things. Still, the product delivers a lot of powerful accounting features at remarkably bargain prices. So I have to spend a few hundred bucks every couple of years to upgrade. Is that really horrific? No, I don’t think so. What perhaps you allude to is that the upgrades get forced upon us.


  3. Jeffrey, Upstate NY says:

    There are several issues:

    1) Upgrades are forced upon users.
    2) Users have to pay for the upgrades forced upon them;
    3) Upgrades change functionality or user interfaces that companies are used to using.
    4) Online storage of data becomes incompatible with earlier versions.
    5) Product keys no longer work on older versions (if the software is reinstalled) because Inuit stops supporting activation services for those versions.

    I have to question how much of a bargain QB is if companies build their financial systems around it, and then find that they have to upgrade their software, and rebuild their accounting systems because functionality has changed, or, in your case, the data they have no longer reconciles because of the way that QB now manages the (remaining) data.


  4. The point of agreements I have don’t quite extend to totally trashing the product nor the company, but let’s admit Scott Cook is a very savvy marketer; I think he started Intuit after leaving Colgate Palmolive? I am not aware of any other significant software vendor who does not charge an annual maintenance fee (by which you repurchase the product every few years anyway) who provides free updates including major upgrades to accommodate new operating systems and hardware. It just makes no economic sense. When they extend or improve features, why would any user believe they were entitled to those for free?

    To the point about my post, that inventory on hand, cost of goods and retained earnings all required adjustments after compressing the data is just the way average costing works. Accountants would argue that adjusting by computing a new average cost is actually a good thing since it more closely matches the costs associated with the reporting period. I think, since I am not an accountant. Having said that, and without having done exhaustive analysis of each item (there were thousands not all of which were effected), it may be because QB still “saw” some items in inventory from prior purchases whose purchase cost fell off. Suppose you bought 1,000 items at $5 each, then bought 500 more at $6 each. Average cost is 1,000 x $5 plus 500 x $6 divided by 1,500 ($8000/ 1,500 = $5.33). What I don’t know is whether they recalculate average cost if you had already sold 500 from the first purchase (500 x $5 plus 500 x $6 divided by 1,000 = $5.50). My gut says no, they only recalculate average cost for an item when a new purchase order is received.

    If that is the case, those are the rules they follow. During the compression, there would be fewer POs to look at for at least some items, and those would get a different average cost, still consistent within the framework of the compressed file. It only shows up, as it did in this case, where they had already prepared last year’s tax returns, did the compression, and went back to compare P&L and balance sheets printed NOW compared to when they were printed at the end of 2012.

    Could Intuit have reworked their algorithms to readjust average cost on each item, accumulate the deltas from before and after compression and apply them to the respective accounts as we did manually? I guess so. But it may be just such an unusual special case as to make the time and effort to program this not worth it.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s