Troubleshooting

File Integrity

Data Integrity Overview

Data Integrity Overview

We often receive inquiries from accountants and tax preparers as they receive their clients QuickBooks files. Most are looking for some suggestions on how to approach looking at the file to judge the accuracy and overall health of the file. By far the most common error is not checking the data integrity of the file.

What is data integrity?

QuickBooks is a complex system of inter-related transactions. For example, a purchase order is not a posting transaction. It is, however, then linked to a bill in the Accounts Payable Register to update the general ledger. This bill is then paid though pay bills and will appear in the check register. The bank reconciliation function is then performed and the bill payment is marked as cleared. It is quite apparent how an unstable operating environment, fluctuations in the power supply, and incorrect computer procedures (especially not closing QuickBooks before closing Windows or turning off the computer) can contribute to problems with the data.

More information on this topic

File Integrity

How Do You Know You Have A Problem?

How do you know you have a problem?

Data integrity problems can manifest in many different ways. Most are the knowledge that something just “doesn’t look right.” Here are a few examples:

  • From the chart of accounts, the diamond to the left of the account is used to make an account a sub account, and the result is that the entire chart of accounts is a sub account of a “mystery” account;
  • The customer list is open to reveal the outstanding balance due; the open balance quick report is then created for this same customer to reveal a different balance;
  • Something strange or unexpected happens within the software.

More information on this topic

File Integrity

How to Avoid or Correct Problem

How To Correct or Avoid the Problem?

In an effort to aid in troubleshooting data inconsistencies, checking data integrity is a logical first step. This should be done if it is possible that there is a problem. Also, as a common practice, it is a good idea to check the data integrity at least once a month (maybe when completing month end procedures) or any time that anything unusual happens in the operating environment such as the computer locks up; QuickBooks encounters a fatal error, etc.

To verify the data, close all open windows within the software.  Then “Verify Data.” 

QBRA-2004: File > Utilities > Verify Data

Once the verification is complete, a message will appear. If it states “QuickBooks detected no problems with your data,” everything is probably fine and the problem usually has its roots somewhere else. Usually there is something in the report format or data entry that does not produce the expected results.

If it states that the data has lost integrity and you need to rebuild the file, don’t panic! Simply choose File > Utilities > Rebuild data. The software will require a back up and then will go through the data to rebuild it.  This will solve the problem 95% of the time.  After the rebuild is complete, check the data integrity again.

TIP: New with version 2003 and higher is the option to automatically check the data integrity when backing up the data file from within QuickBooks.

QBRA-2004: File > Back Up

More information on this topic

File Integrity

What happens when the rebuild does not work?

What happens when the rebuild does not work? 

Verify the data integrity again then press Ctrl + 1 to see the product information, then Ctrl + 2 to see the tech help.  Click on the open file tab and scroll down until you see a file called qbwin.log.  Open this file and scroll to the bottom where End Verify Log should be.  If a transaction is damaged it should be listed here.  Scroll up until Begin Verify Log is visible.  In the example below there was no problem with the data integrity, hence no damaged transactions. 

QBRA-2003: Ctrl + 1 > Ctrl + 2 > Open File > QBWIN.LOG > Open File > Scroll to bottom

If the file had lost data integrity, there would be a list of the damaged transactions that need to be addressed.  Typically the best way to handle it is to print the list.  Note that it may be necessary to save the file and then open it with the notepad and delete the information above the Verify Log to eliminate a huge print job. 

 

If there is a reasonable number of transactions complete the following steps: Find the individual transactions using the find feature.  Make sure everything about that transaction is known.  Obviously the date name, accounts or items, amounts, etc. but also any memos, links to other transactions, has it been reconciled, etc. will be important.  Then delete it.  Do this for all the damaged transactions.  If the list is unbelievably long (the worst one I have ever seen had 3,400+ damaged transactions) the file may be “dead.” 

 

Rebuild the data and verify the data again to confirm that no problems have been detected.  Re-enter the damaged transactions as they were including any links.  Verify the data again to confirm that no problems have been detected.

 

More information on this topic

File Integrity

When the file is "Dead"

When the file is “Dead”

 

When the list of damaged transactions is too long, or it seems impossible to restore data integrity, the file may be dead.  At this point, the determination needs to be made as to when a new file will be started.  It is possible to keep limping along with a damaged file, but keep in mind that something is wrong so using it is on borrowed time.  If it is almost the end of the year, it may be possible to finish the year and start a new file with the New Year. 

 

The transfer of the lists in the older versions, or the remove transaction option in the newer versions makes starting with a clean file (i.e. no transactional data) relatively easy.  The beginning balances will need to be entered and reconciled.  This is also a perfect time to make any changes that were impossible due to transactions (i.e. clean up the lists to remove unused or unneeded entries), setting up the file a little differently, etc. 

 

The transactional data is where it becomes complicated.  If it is close to the beginning of the year, re-entering the current year data may be the best way to go.  If not, there is an add-on that can help automate the process of transferring the entries from the old damaged file to the new one.  It does not work for payroll transactions (they will be transferred as checks and need to be re-entered or YTD adjustments entered) and the links between transactions will not transfer but it will save time on most of the data entry of the individual transactions. 

More information on this topic

File Integrity

Ask the Expert - This File Is a Mess!

Ask the Expert - This File Is a Mess!

Q - You know how it goes this time of year. Small businesses had thought they could do everything themselves, and now they walk into your office saying "I know I am kind of late, but can you do my taxes so I don"t have to file an extension?" You don"t make any promises, but say you will see what you can do. You open the file and . . .

A - Although I am not a firm believer in "start over at the first sign of trouble." I do agree that there are times when it is best to let the past fall where it does and just move forward. The analogy I often use is, let"s stop the bleeding and start the healing.

My approach to a file with significant challenges is as follows:

First determine, can it be saved?

If there are problems with inventory items that should have been non-inventory, for example, it is probably not worth saving. In certain instances this is true for payroll too. For most other problems, it will be a judgment call.

Second, what is the approach?

Usually you will not go back through all the transactions and try to correct each one. Usually it makes sense to have a cut off. Up to this date, things have problems, but the ending balances are correct coming into the next period and from that point forward everything is relatively clean. This early in the year (i.e. only 3 months) I would probably try to get the balances reconciled as of 12/31, even if it means making some manual support schedules and entries to force the balances. You need to get to a clean starting point to move forward.

Third, how do you actually do that?

Make sure the balances as of the last tax return agree with QuickBooks. If they don"t, assume the tax return is correct (hopefully it is an entity that has a Balance Sheet as part of the income tax return) and move forward. Double check the lists for obvious problems. Then work through the current balance sheet so that you feel confident you know why each balance is there. Review the P & L for obvious coding errors, but let it, for the most part, fall where is does. The QuickBooks Diagnostic Tool provides an automated, interactive way to go through the file so that issues are not overlooked.

Finally, what if you look at the file and determine that it is not worth the time to fix it?

To export and import the lists is pretty straight forward using the options under File > Utilities. In fact, with the newer versions of QuickBooks under the File > Archive & Condense data option they have made it even easier, you can just get rid of all the transactions but preserve your lists, service subscriptions, and preferences. The transactions get a little tricky. There are a couple of add-on products out there to fill this need. There are some limitations, such as paychecks come in as checks and you will need to "relink" payments and invoices, etc. but it still save some time and hassle of re-entering the transactions.

More information on this topic

File Integrity

Data Integrity with Large Files

Data Integrity with Large Files

Q - We have a client that is using QuickBooks PRO 2003; the file size is 134MB. Should we be concerned about the data integrity? They are a manufacturing business and use Accounts Receivable and Accounts Payable extensively; they also like to have 3 to 4 years of history available. We have condensed data except for the last 4 years. If this is a concern, what are the options?

A - As a general rule, Intuit recommends that the file size not exceed 80 MB (it was 100 MB in most literature prior to the release of the Enterprise Solution). The speed, memory, etc of the computer being used will significantly impact the file size that is appropriate.  Even if the file size is not above that limit now, it is helpful to calculate how quickly the file size is growing to know when the issue may need to be addressed.

The with any data file, regularly confirming the data has integrity (File > Utilities > Data Integrity) is an important procedure. With large files it is critical. Data integrity issues are often not readily apparent to the end user without use of the feature within QuickBooks.

Based on the size of the file, the extensive use of QuickBooks features, and the desire to have so many years readily accessible in the same data file, this may be a candidate to upgrade to the Enterprise Solution. This product has the same look and feel as the other QuickBooks software so there is no learning curve, but it is designed with larger data files in mind.

More information on this topic

File Integrity

Condense Data

Condense Data

 

This option does not need to be done every year.  The "condense data" option will reduce the QuickBooks data file size by deleting cleared, completed, and reconciled transactions up to a specific date and replacing them with one journal entry for the month. Any transaction that has not been completed (i.e. an invoice which has not been paid in full, a bill payment which has a partial amount still due on a bill, etc.) will not be condensed.  It is recommended that several years not be condensed to make comparative detail reports easier.  For many QuickBooks files with limited transactions it may not be necessary to use this feature for many years.

The first step is to make sure that all the data has been reviewed to ensure that there are not any small amounts still remaining that need to be corrected. An example would be a customer that short-paid an invoice by a penny. A credit memo was entered to clear the amount due, but the two transactions were not linked together. By not completing this final step, all of the deposit from the receive payment for that invoice will not be condensed, for that reason the receive payments for all of the transaction on that deposit will not be condensed, and therefore, none of the effected invoices will be condensed. It is easy to see how this little penny could be a big problem.

This option was changed with version 2002 and higher.  In the past, the condense option was File > Utilities > Condense data.  Now with 2002 and higher there are two choices, the condense option as was available in the older versions and a new choice, remove all transactions.  The later choice preserves the lists, preferences, and subscriptions in the file but eliminates all the transactions.  On the older versions, the most efficient way to create a new file without any transactions was to export the lists, import them into the new file, set the preferences again, and work with Intuit to transfer the subscriptions to the new file.

 

QBRA-2003: File > Archive & Condense Data

 

 

TIP: In the 2001 and prior versions, it is recommended that from Windows, copy the file as another name prior to condensing. This will make it easier to look at any transaction that has been condensed.  With 2002 and higher an archive copy is created automatically.

 

TIP: If you see that you need to make some corrections, restore the backup, make the corrections, and then follow the condense procedures again. It is recommended that you do not try to condense data in stages or you will have difficulty in restoring the backup to see all the detail should you ever need it.

 

TIP: The data file must be in single user mode to use these options.

 

TIP: New with 2002 and higher inventory transactions may be condensed.

 

TRICK: QuickBooks only deleted estimates for jobs that are marked as closed.  QuickBooks only deleted time data if it is marked as billed or not billable or if its job status is closed.  If payroll is based on time, timesheets will only be removed it the employee has been paid.

 

TRICK: Because the condense option eliminates customer, vendor, item, etc history, the general ledger by month will remain unchanged, but detail reports will no longer be available.

 

More information on this topic

Year-End Procedures

File Integrity

Condense Data Error

Ask the Expert – Condense Date Error

                                     

Q - We archived our QuickBooks data but put in the incorrect date so I would like to bring this data back into the main database, but am not having any luck.  Any help you could give would be greatly appreciated.  Submitted by Paul

A – Unfortunately there is not an “undo” option for condensing.  This is true for most everything in QuickBooks, once you acknowledge that you are sure you want to do it that is it.  The only exception to that is the “undo” bank reconciliation feature in the newer versions. 

With the condense feature, you are required to make a back up of the data.  My first thought is to restore the back up.  That will get the file back to where it was before you did the “condense.” 

If the back up file was not retained, or is damaged, with the newer versions of QuickBooks the condense process also creates an “archive” copy of the data.  This is simply a copy of the data file as it was prior to the condense.  The file that was created will specifically state Archive Copy, the date it was created, and the original QuickBooks data file name.

QBEA-5: File > Open (navigate to the folder where the original data file was saved)

Restoring from a back up is preferred, but the archive copy can provide another alternative as a last resort.  Keep in mind, however, the QuickBooks help documentation states specifically “Important: You should never use the archive copy for active data entry.”

Once the file is available as it was before the condense, it is possible to either re-enter the data you have in the incorrect file, or using one of the data transfer utilities that are available you could automate the process of moving the new transactions into the old file.

More information on this topic

File Integrity

Error Messages

Error Messages Overview and Links

Error Messages Overview and Links

QuickBooks is relatively stable, and error messages are relatively rare.  However, there are several types of messages that are more common.  For the specific QuickBooks error message codes the most current and complete source is the Knowledge Base that can be found under support at the QuickBooks website http://www.quickbooks.com/support/.

More information on this topic

Error Messages

Maintenance Releases

Maintenance Release Overview & Resources

Intuit seems to be on a cycle of a new version of their QuickBooks® Accounting Software every year.  While this author feels that it is always a good idea to upgrade to the most recent version, for some clients this may not make sense.  The reason for not upgrading may include: economic constraints, especially when the multi-user version is purchased; interface with an add-on product that has not yet been updated to the most recent version; some change in look and feel that requires a learning curve; etc.  Usually there is at least one improvement that makes the upgrade "worth it."  In any event, between the new versions, and even after a new version becomes available there are maintenance releases from time to time.  These are free updates that are typically downloaded from the Internet and provide feature enhancements, improve usability and/or fix known problems. 

As an example, with the most recent version 2002, the bank reconciliation screens were changed, including the reports.  The new reports only show the detail of the cleared items.  There was not a listing of the uncleared items on the report.  It was possible to work around the problem by creating a report for the uncleared items, but it was not as user friendly as the past.  Since that time, there have been two maintenance releases.  The first changed the report to include the uncleared checks and deposits.  If the maintenance release is not downloaded and installed, the change on the reports would not be fixed.

To determine what version and release are being used, press Ctrl > 1.  The very top line will say the product, the version, and the release.  To determine if the most recent release is being used, there are two alternatives:

Ø      Visit http://www.quickbooks.com/support/updates.html and compare the information found there with what was seen on the screen.  This is also where you would go to manually download the update if there were problems when trying to download it from within the QuickBooks software.

Ø      Or, it is possible to try to download the update and if nothing new is available, a message will appear stating that fact.  Depending on the version used, the keystrokes to find the download or update option may be slightly different: File > Update QuickBooks > Update.

With version 2006, there has already been multiple maintenance releases.  When working in a networked environment, be sure to install the maintenance release on the server first, then update the work stations.

More information on this topic

Computer Issues

Overview of Version 2004

Error Messages

2006 Update Book

Version Errors When Trying to Open or Restore a File

Version Errors When Trying to Open or Restore a File

 

With the latest versions of QuickBooks there have been significant new releases.  A release is a free “patch” that improves usability or fixes a “bug” within the software.  Quite often the releases are large files that resulted in significant changes to the data structure.  Once the release is down loaded and the software updated, the first time the data file is opened it will be converted to the new release. 

 

The releases, as they become available, are automatically downloaded to an “inet” folder when the computer is logged on to the Internet, if that option has been selected.

 

If not, simply click on the Update button and the software will automatically go to the Internet (after it has been set up the first time) and log onto Intuit’s web site to download any new releases for the version of the software you are using that may be available.  It is recommended that this process be done with some regularity if the choice is made to do it manually.  If the preference has not been set to perform the function automatically, once a month during the month-end procedures is often a logical time to download the latest release. 

 

QBRA-2002: File > Update QuickBooks > Options

 

Keep in mind that if files are being transferred between different computers, it is important that all of them are using the same version and release of the software.  The best alternative in this situation (and an important issue if not all the computers that are networked together have internet access) is to change the default in the options section of the Update feature to permit sharing of the downloaded files.

 

To check which release is currently being used, with the software open QuickBooks then press Ctrl > 1 together.  The first line will note the product and release currently installed.  The box in the lower left corner shows the version and release, along with the update date for the company file that is open.

 

Ctrl > 1

 

TIP: A version error message when opening a file may mean that the file was last used in a different version, or in a different release.  It is possible to convert the file to the newest version and release as detailed above.  However, once that process is complete, it is impossible to go back to an earlier version or release, so all users of the file must be updated as well.

 

TRICK: For versions prior to Version 2000, when downloading the update file for use by everyone on the network, open a data file that is on the network drive, then do the download.  By using this procedure, the first time each different computer accesses the data, the machine will be updated.  If a file is on the local hard drive (i.e. sample company) each computer will need to download the update individually.

 

TRICK: If there is a problem when trying to use the update function from within QuickBooks, it is possible to manually download the update via the internet.  To use this alternative visit: http://www.quickbooks.com/support/updates.html.

 

More information on this topic

Error Messages

Reconciliation Issues

Undeposited Funds on Balance Sheet

Undeposited Funds on Balance Sheet

Undeposited Funds is a special account created by QuickBooks as a clearing account for payments that have been received but not yet deposited into the bank account.  The easiest way to picture this account is as the top desk drawer.  As the money comes in each day, it is entered into the computer, and placed in the top desk drawer.  This happens all day long.  At the end of the day, the drawer is opened and money is scooped up and taken to the bank.  At that point, the make deposit function is completed in QuickBooks to pull the undeposited funds onto a deposit slip.  The total of this deposit slip should agree with the bank statement at the end of the period. 

The problem occurs when the money is entered one day and the deposit is made on a different day.  During the interim, the amount will be in undeposited funds.  To correct the situation, the deposit date should be changed to agree with the received payment, resulting in a deposit in transit on the bank reconciliation. 

Some accountants choose to edit the name to "Cash on Hand" to clarify what this account actually is.  The primary problem with this approach is that when the statements are issued, the account will appear in the Other Current Asset section, not with the bank accounts, and there is no way to change the account type. 

That is the rational for the recommended approach of having the deposit date and receive payment dates match.

 

TIP: In the newer versions of the software it is possible to make a general journal entry to the undeposited funds account.  Just be sure to reverse the entry as of the first of the next period.

 

 

More information on this topic

Receiving Payments and Discounts

Reconciliation Issues

Old or Unusual Amounts on Reconciliation

Old or Unusual Amounts on the Reconciliation

Although it is possible to reconcile the bank account each month to a difference of zero that does not necessarily mean that the bank account balance is correct.  For most small businesses most check are issued and cashed shortly there after.  For bank deposit there may be a day or two lag, but that should be it.  When the outstanding deposits and outstanding checks are reviewed when the bank account is reconciled, most should be very current.  If there are transactions that are old, or unusually large, this should signal the need to investigate the situation more closely.

For old transactions, confirm that the entry has not been inadvertently duplicated.  If it does not appear that that is the problem, further investigate what has happened.  If the deposit is missing from the bank statement for example, this may mean contacting the customer to see if the check has cleared their bank account.  If it is an old check, which might mean contacting the vendor to see if they still show an outstanding amount due.  Once the situation has been determined, the next step is to reflect the activity correctly in QuickBooks. 

In most situations that DOES NOT mean voiding the transaction.  In QuickBooks if a transaction is voided, it is removed as of the original transaction date of the entry. 

If the check, for example, is three years old, the vendor does not show any outstanding amount, and it just needs to be removed from the bank reconciliation since it appears that it has been cashed but incorrectly reconciled at some point in the past, a deposit with the current date should be entered.  When the next bank reconciliation is done, the check and deposit can both be marked as cleared (net amount of zero) to remove them from the bank reconciliation screen in the future.

 

More information on this topic

Reconciliation

Reconciliation Issues

Beginning Bank Balance Difference

Ask the Expert - Beginning Bank Balance Difference

Q - I have been doing my bank reconciliation procedures each month without any problems, but this month the beginning balance does not match the ending balance. What happened and how do I fix it?

A - Discovering the problem is the tough part. Once you have discovered (or remembered) what has been changed, it is typically not too difficult to fix.

As you stated, the beginning balance should be the same as the beginning bank statement balance. This amount cannot simply be overridden in QuickBooks. The software automatically calculates this balance each time by going through the account's entire register adding and subtracting all the transactions with a checkmark in the cleared column. If the amount shown in the beginning balance field is not correct, there is only one of three things that could have created the variance.

1. A transaction has been deleted or unmarked even though it had already cleared the bank. A possible source of the difference is that a transaction that has already cleared on the bank statement was either accidentally unchecked in the register or deleted. The former is probably the easiest error to find because the transaction will appear again in the reconciliation. By comparing the outstanding checks and deposits with the previous report, the error should be quite apparent. A deleted transaction, on the other hand, is much more difficult to find. The best way to find the error is to print a check register each month in addition to the full reconciliation report. Both reports should be filed with your bank statement. With the check register report, it is possible to compare the balance on the report with the register balance. By working backwards, the month that contains the deleted transaction resulting in the change in the opening balance can be determined. Once the month is found, by going through the register transaction by transaction, it is possible to figure out which one is missing and then re-enter it. Once the transaction is in the register, by clicking in the cleared column the checkmark will again appear. Record the transaction and the opening balance in the current reconciliation should be correct as well. If the check registers were not printed, or were not retained, the next best thing is to look at the audit report if it is turned on and see a deleted transaction with an older transaction date.

2. A transaction has been changed even though it had already cleared the bank. To determine the transaction with the error, follow the same procedures as detailed above for finding a deleted transaction.

3. A transaction has been marked as cleared even though it has not yet cleared the bank. Use the find feature to look for the dollar amount then investigate each one to see if it is marked as cleared but should not be. If this does not quickly discover the problem, try scrolling through the register from the bottom up and looking for a recent transaction that has a checkmark. The other way to find this error is to look at the previous reconciliation report at the uncleared transactions and confirm that they have not been marked as cleared. If the previous report was not printed in detail, the transaction may have cleared on the current bank statement but it is not listed in the reconciliation window. To correct the problem, go into the register and click in the cleared column to remove the checkmark and then choose record to save the change.

Although this sounds very straight forward, finding what has changed can be quite challenging. To aid in discovering the difference, the QuickBooks Premier Version 2002 and higher offers a discrepancy report which details all the changes made since the last reconciliation.

 

More information on this topic

Reconciliation

Reconciliation Issues

Subsidiary Reports Don't Agree with the Balance Sheet

Subsidiary Reports Don't Agree with the Balance Sheet

It is important to remember that the subsidiary reports are strictly that: reports.  They will be accurate based on how they have been set up using the report choice, custom button, and filter button.  Below is a checklist to assist in discovering why the results are not what you expect:

Date

 Although it seems quite obvious, the problem of any report, at one time or another has been due to a failure to check the report date or date range at the top of the report.

Unpaid Bills or Open Invoices do not agree with the balance sheet or balance detail reports 

The most common reason for this is that the "current (faster)" option has been chosen. This is the report default. To confirm that this is the setting, click on the customize button at the top of the report.  What this option does is takes the current report, for example, August 24, 2000, and when the date is changed to July 31, 2000, it simply modifies the current report to only include those transactions that were also outstanding at July 31, 2000.  Usually, the report that is expected is everything that was outstanding at that point.  To obtain a report that will agree with the Balance Sheet Report, select "As of Report Date" or choose the A/R Aging report instead.

Inventory Valuation Summary Report does not agree with the Balance Sheet

This issue is usually the result of one of two situations:

  1. 1.      An entry that has been made to inventory directly, rather than through a transaction by the individual items.  For example, a journal entry was created without the ability to enter the transaction information by item, or a bill was entered with inventory as the account on the item tab as opposed to the individual items on the item tab, etc.  To correct, simply find the transaction, delete it, and re-enter it through the appropriate form or inventory adjustment method.
  2. 2.      An inventory item has been marked as inactive when a value is still present.  If the item is on hand, do not make the item inactive.  If the item needs to be inactive because it is no longer present in the business (theft, discarded, etc) or is not able to be sold (damaged, obsolete, etc) an inventory adjustment should be done to remove the quantity and value from inventory and reclassify the amount to the appropriate Profit & Loss account.

Payroll Liabilities by Item does not match the Balance Sheet

More often than not, this occurs because proper procedures were not followed.  The liabilities need to be paid through the Employees > Pay Payroll Liabilities option so that the individual items know they have been paid.  This is important from a reporting standpoint, but even more important for creating accurate returns. A check coded directly to the liability account does not serve the same purpose.

Sales Tax Liability Report does not agree to the Balance Sheet

The Sales Tax Payable account on the Balance Sheet is based on the accrual method of accounting.  This means that the sales tax is recorded as being due and payable as of the invoice date.  Most sales tax payments are required to follow this convention.  If a difference is evident, and the business is required to remit sales tax based on the invoice date, double check the sales tax preference and confirm that it is marked for as of invoice date.  If the business is permitted to remit sales tax based on when the customer actually pays the invoice, the report and the Balance Sheet will never agree by virtue of one being cash basis (reported as payments are received) and the other being accrual basis (general ledger is updated as invoiced).

 

More information on this topic

Reconciliation Issues

Balance Sheet

A/R and A/P Reconciliation on Cash Basis Statements

A/R and A/P Reconciliation on Cash Basis Statements

The first and easiest approach is to decide that the software is technically correct, and leave it as is.  A review of the open invoices and unpaid bills with their account coding will usually confirm that this is correct. 

The most efficient way to do this is to perform the following procedures:

  1. 1.      Create a Balance Sheet and change the date as needed
  2. 2.      Confirm the report basis is cash
  3. 3.      Create an open invoice and/or unpaid bills report as of the report date and confirm there are not any transactions that have not been "linked."
  4. 4.      If Accounts Receivable or Accounts Payable still has a balance, double click on it.  This will create a report for the activity for the period
  5. 5.      Click on modify report and then filters
  6. 6.      Change the date from the beginning of the file to the Balance Sheet date (i.e. include all historical transactions but not future transactions) and the paid status to open.  This will provide a list of the transactions and related amounts that are coded to a Balance Sheet account and have not been reversed automatically.  In addition, payments are considered to be "closed" transactions so if there are payments that have not been applied to an invoice, they may not appear on the report and would need to be subtracted to arrive at the cash basis balance.  Double check the unpaid bills or open invoice report as of the report date to determine if this issue exists or not.
  7. 7.      It may prove helpful to modify the report to include the item column (check it on the display tab).  Usually the transactions are sales tax, inventory, customer deposit liabilities, etc. 
  8. 8.      If the situation is not clear, it is possible to double click on the transaction to get to the form and then choose Reports > Transaction Journal to see the actual journal entry created by the form.  This will display any additional accounts and amounts that are part of the entry for inventory items.  Note: Any transactions that have been partially paid will be prorated to each line on the invoice so the amount on the report may not match the amount of the journal entry exactly.  Note: It may be easier to analyze what is happening if the modify report button is used to add the debit and credit columns and eliminate the amount column on version 2002 and prior.
  9.  

QBRA-2002: Open the form > Reports > Transaction Journal

Depending on the reporting basis of the financial statements and any related rules, it may be preferred to make a journal entry to reclassify the balance to a prepaid or accrued account, respectively.  That is the second approach.

The second approach would be to make a journal entry to reclassify the balance.  This approach works because journal entries are assumed to be on a cash basis as long as the A/R or A/P amount is not on the first line of the entry. The journal entry can only have one Accounts Receivable or Accounts Payable account and will need a customer or vendor for each Accounts Receivable or Accounts Payable transaction, respectively.  Creating a Misc A/R customer or Misc A/P vendor will eliminate extraneous transactions in the history of any actual customers or vendors.  The first day of the subsequent period, the journal entry should be reversed to eliminate the activity in Accounts Payable or Accounts Receivable.  Keep in mind that the entry should typically be to a prepaid or accrued account, or to the actual Balance Sheet account that has been affected by the transaction based on the research performed in the first approach.  An entry to income or cost of goods sold type accounts is incorrect.  That is not the origination of the issue and doing so will distort both the Balance Sheet and Profit & Loss reports.

Final Notes

As you have learned, QuickBooks does permit switching between the cash and accrual methods of accounting for Accounts Receivable type accounts and Accounts Payable type accounts.  If there are any other Balance Sheet accounts that should be eliminated, a journal entry will be required.

 

More information on this topic

Reconciliation Issues

Opening Balance Equity Not Zero

Opening Balance Equity Not Zero

 

Opening Balance Equity is a special QuickBooks account in the equity section of the Balance Sheet that the software automatically creates to balance certain types of transactions.  The balance in the account can be analyzed by double clicking on the account name from the chart of accounts list to review the register.  The entries contained are usually the result of one of the following:

 

1. The “off set” account when beginning balances are entered into the chart of accounts.  This is the primary reason for the account.  During the set up process for a new file, beginning balances for Balance Sheet accounts can be entered on the new or edit account screen up until the point that there are any transactions associated to the account.  The “other side” of the entry is made behind the scenes to Opening Balance Equity.  Other beginning balance items such as outstanding checks, deposit in transit,  or outstanding invoices or bills, would all be coded to Opening Balance Equity as part of the start up procedures.  Then the Opening Balance Equity account is used to balance the journal entry for year-to-date profit and loss accounts if the decision has been made to start mid-year.  The remaining balance at that point should equal Retained Earnings from the prior accounting records, so a journal entry is made to close the Opening Balance Equity account into Retained Earnings.  Therefore the Opening Balance Equity account should only have a balance as the “start up” procedures are followed, after that time, it should always be zero.  The reason that this special account is used rather than simply posting beginning balance journal entries directly to Retained Earnings is because Retained Earnings does not have a register so researching a variance is more difficult, and several of the beginning balances are entered via forms to permit accurate reports in the future.

 

2. The most common reason for a balance in the Opening Balance Equity account is the result of a bank reconciliation that was not balanced to zero prior to completion.  The process of entering the ending balance, marking the items that have cleared the bank, and reconciling the difference to zero is the correct procedure.  Occasionally, the difference is small, and is determined to be immaterial, or the user cannot find the difference and decides to complete the reconciliation anyway.  If the process is ended with this outstanding difference, the software will provide the option of adjusting the difference.  This option will adjust cash with the “other side” of the entry being Opening Balance Equity.  A better procedure is to adjust the difference in cash through a check or deposit coded to a profit and loss account.

 

3. Directly coding a transaction to this account is the third way that entries appear in this account.  To correct the error, research the transaction and then edit it to be coded to the correct account.

 

Once the account is reconciled back to zero, it is recommended that the account be marked as inactive.  Keep in mind, however, that if the software needs the account (such as item 2 above) it will use it, even if it has been marked as inactive.

More information on this topic

Reconciliation Issues

Retained Earnings Balance Doesn't Agree with the Prior Year

Retained Earnings Balance Doesn’t Agree with the Prior Year

 

Retained Earnings is yet another special QuickBooks account.  There is not a register for this account, nor can a quick report be created to review the detail in this account.  Each time a Balance Sheet is created, all prior years Profit and Loss accounts are automatically “rolled” into this account. 

 

The easiest way to describe this account is to compare it to the opening balance when completing the bank reconciliation. Going to the account register and adding or subtracting all the transactions with a checkmark in the cleared column calculates the opening balance for the reconciliation.  Calculating all of the prior profit and loss amounts plus any transactions coded directly to Retained Earnings creates the Retained Earnings balance.  That is the account balance that will appear on the Balance Sheet.  Therefore, just like the opening balance on the bank reconciliation, if the amount does not agree with the balance previously calculated, a transaction has been added, modified, or deleted. 

 

Discovering the transaction, and then correcting it as appropriate, will correct the balance. 

 

If the change is that a single transaction has been entered, using the find function will locate it. 

 

If it was that a transaction has been changed, it may be possible to review the audit trail report to see a prior transaction date with a current entered/modified date to narrow down the problem (if the audit trail preference was on the previous and current transaction will appear). 

 

If the transaction was deleted, and the audit trail preference has been turned on, it is possible to scroll through the audit trail report to see the deleted transaction.

 

New with version 2002 and higher there is an additional option, for the time that the closing date has been entered and version 2002 or higher has been used, there is a new report available in QuickBooks Premier called the Closing Date Exception Report.

 

Otherwise, finding the change can be difficult.  One suggestion is, as an additional line of security, a complete general ledger and related reports should be printed. If a change is then made, the research and analysis process will be greatly reduced.

 

For the older versions, to provide an additional level of protection for the prior year data, it is recommended that the “closed” periods be password protected and that no one but the admin password have access to change a closed period.  For version 2002 the closing date step is critical to ensure that the Closing Date Exception Report will be available.

More information on this topic

Reconciliation Issues

Year End Issues

Removing Transactional History

Removing Transactional History

For the newer versions of QuickBooks (2002 and higher), there is an option under File > Archive & Condense Data. This will permit removing all transactions in the file while preserving the lists and service subscriptions. This alternative will leave the basic framework and since all transactions are gone, it is possible to delete entries on the various lists. If using payroll in the current year or extensive changes need to be made to the lists (i.e. that is what was not set up properly) the second alternative may be better.

QBRA-2004: File > Archive & Condense

For the older versions of QuickBooks (2001 and prior), the only alternative is to start a new file.  The lists can be exported from the old file and then imported into the new file.

Note: If the reason for the new set up is because of errors on the list (for example, inventory parts that should have been non-inventory) these errors can be corrected in Excel prior to importing the lists into the new file. There is some information that may not transfer (such as some payroll information, customer credit card numbers, etc.) so check the lists carefully before you begin entering transactional information.

With either alternative, the beginning balances will need to be re-entered. There are several add on products available to make this process easier.  The assumption here is that this is that the business is a calendar year end, so this is the perfect time for starting again.

If the business is not a calendar year end and/or there have been transactions entered for the new period into the old file, the QuickBooks Transaction Copier Excel Add On may aid in automating the process of moving that information.

 

More information on this topic

When to Create a new File

Year End Issues

Ask the Expert - Mid Year Conversion for 1099s

Ask the Expert - Mid Year Conversion for 1099s

Q - We converted to QuickBooks at the start of our fiscal year (June 1) and I have a problem with the 1099s. When I go to the 1099 summary report, it does not show anything. What am I missing? Also, how do I adjust the amounts on the form at the end of the year for what was paid prior to switching to QuickBooks?

A - There are two issues you need to consider. The first is true for anyone using QuickBooks to generate Form 1099s. The second is specific to those users who convert to QuickBooks mid-year.

To handle the problem with information not showing on the report, it sounds like the 1099 preference and related vendor set up has not been completed. For a complete description of how with works, go to our October QuickBooks Tips and Tricks - 1099 Prep Work article on the subject.

Here is one way to handle the set up of the expenses paid to independent contractors without entering all the checks again.

 

  1. Create a new bank account and call it "1099 clearing."
  2. Enter the amount paid to the vendor as a check coded to the appropriate 1099 account.  This information should be calculated from the prior bookkeeping system (i.e. a report if software was used for the time period, or manually if appropriate).
  3. Enter a deposit without the vendor name to the same account. The net effect is to increase the payments recognized by the system for 1099 purposes to the specific vendor, but not affect the general ledger in total.
  4. Once the data entry has been completed the net balance in the new bank account should be zero so the account can be made inactive.

 

 

More information on this topic

Year End Issues

Form 1099

Change Year End

Changing Year End

For most businesses, when the QuickBooks file is set up, the first month in the fiscal and tax years are entered and this entire issue can be ignored.  However, there are two common reasons why the year end may need to be changed in a QuickBooks file.

By far the most common reason is that the first month in the fiscal year was set up incorrectly from the beginning.  This is usually the result of new businesses entering the first month they were in business as opposed to the first month of the fiscal and tax year.  Typically the error is found when running reports for the first month of the new year yet the year to date amounts include months from the previous year.

Less common, is the situation that the business has changed year end for income tax purposes.  Occasionally the year end change is mandated by the government, but more frequently the change is the result in a change in legal form.  For example a sole proprietor converts mid year to a corporation but does not start a new QuickBooks file for the new entity.  To learn how to "split" the file if this is the situation, consider our free eReport on Starting Over.  Or, if leaving the file with both legal entities intact is acceptable to the tax accountant, just be very careful when running reports around year end change.

In either case, Company > Company Information is where the first month in the fiscal and tax year can be changed.  The change will affect all of the historical reports created in the future.  It is also possible on this screen to change the income tax form.  Be aware, however, if the income tax form is changed, the tax lines assigned to each account will be removed and will need to be re-assigned using the correct tax form lines for each account manually.  Be sure to exercise extreme caution and watch the date range on reports around the time of the change.

QBRA-2004: Company > Company Information

 

More information on this topic

Company Information

Year End Issues

Exception Reports - Problem Accounts

Exception Reports – Problem Accounts

One report that can be created to discover exceptions would be for accounts that are known to have been a problem in the past.  A transaction detail report (Reports > Accountant & Taxes > Transaction Detail by Account) can be filtered for the potential problem accounts. 

For the report, it is possible to choose a single account, selected accounts, or accounts based on their type.  Some examples include: Repairs and Maintenance to discover expenditures that should be capitalized, Auto Expense to discover parking tickets that are not deductible for tax purposes, or Legal to discover an impending litigation, just to name a few.

 

QBRA-2004: Reports > Accountant & Taxes > Transaction Detail Report > Modify Report > Filters Tab > Account

 

 

 

More information on this topic

Analytical Review

Year End Issues

Exception Reports - Variance Analysis

Exception Reports – Variance Analysis

There are several different ways to compare current activity to historical results.  The benefit is an easy way to see where, specifically, the business is producing better results and where the business is not.  QuickBooks makes the comparison process relatively easy by providing an alternative to compare the current period to the previous period (for example, last month to the month before) or the current period to the same period the previous year (for example, December 2004 as compared to December 2003).  It is possible to see the difference as a dollar amount change, or as a percentage.

QBRA-2004: Reports > Company & Financial > Profit & Loss Standard > Modify Report

 

More information on this topic

Analytical Review

Year End Issues

Closing Date Exception Report

Closing Date Exception Report

The Closing Date Exception Report is useful in finding changes made to a previous period.  It is dependent on the closing date being entered.  It was first available with QuickBooks Premier Version 2002.  The changes that are recorded on this report are changes made in the current version to transactions prior to the closing date.  Changes made in previous versions of QuickBooks will not be tracked.

QBRA-2004: Reports > Accountant & Taxes > Closing Date Exception Report

TRICK: Prior to upgrading to a new version of QuickBooks, it is important to review and/or print the closing date exception report in case there is any problem reconciling retained earnings.

TIP: This report also shows when the closing date was set or changed which can be helpful information for internal control.

 

More information on this topic

Analytical Review

Year End Issues

Condense File

QuickBooks Tips and Tricks - Condensing the Data File
Ask the Expert
Q. At this time of year, some accountants like to encourage clients to condense the QuickBooks file. What are the pros and cons of this procedure?
A. It is the opinion of this author that this option does not need to be done every year. The "condense data" option will reduce the QuickBooks data file size by deleting cleared, completed, and reconciled transactions up to a specific date and replacing them with one journal entry for the month. This means that all of the customer:job, vendor, audit trail, and class information is removed with only the general ledger account and the summary amount remaining. Any transaction that has not been completed (i.e. an invoice which has not been paid in full, a bill payment which has a partial amount still due on a bill, etc.) will not be condensed. It is recommended that several years not be condensed to make comparative detail reports easier. For many QuickBooks files with limited transactions it may not be necessary to use this feature for many years. To calculate how quickly a QuickBooks file will grow without condensing, see the archive newsletter article Outgrowing QuickBooks.
The first step is to make sure that all the data has been reviewed to ensure that there are not any small amounts still remaining that need to be corrected. An example would be a customer that short-paid an invoice by a penny. A credit memo was entered to clear the amount due, but the two transactions were not linked together. By not completing this final step, all of the deposit from the receive payment for that invoice will not be condensed, for that reason the receive payments for all of the transaction on that deposit will not be condensed, and therefore, none of the effected invoices will be condensed. It is easy to see how this little penny could be a big problem.
This option was changed with version 2002. In the past, the condense option was File > Utilities > Condense data. Now with 2002 there are two choices, the condense option as was available in the older versions and a new choice, remove all transactions. The later choice preserves the lists, preferences, and subscriptions in the file but eliminates all the transactions. On the older versions, the most efficient way to create a new file without any transactions was to export the lists, import them into the new file, set the preferences again, and work with Intuit to transfer the subscriptions to the new file.

More information on this topic
Year End Issues
Tips and Tricks

Ask the Expert - Cash Basis Accounts Receivable After Condense

Ask the Expert - Cash Basis Accounts Receivable After Condense

 

Q - On my cash basis trial balance I am getting a balance in accounts receivable. It is made up of transactions condensed by my client. Is making a journal entry sufficient to fix this problem, or is there a better way to correct it? Submitted by Dolores

A – Our Cash Basis Information provides a background on the issues. This question takes the issue one step further by dealing with transactions created using the condense feature

The condense feature is only necessary when the file becomes too large. For cash basis clients this process is not recommended because the cleared, completed, and reconciled transactions become condensed into one journal entry for the month on an accrual basis with the customer and item detail removed. For Cash basis clients who choose to use the Accounts Receivable and Accounts Payable features creating a new file when the file becomes large is preferred. When the condense feature is used instead, the result is what has been discovered here. The only alternative is to create a journal entry; the challenge will be to understand exactly what the "off set" for the entry should be.

The best suggestion will be to compare the Balance Sheet at the end of the previous period (or possibly an older period if comparative reports are needed) so the journal entry can be made to "balance" to the previous report. Keep in mind that if accrual reports are also prepared, the journal entry will need to be voided or deleted to return the Accounts Receivable and Accounts Payable balances back to what they should be for that type of report.

TRICK: If the file needs to be started again, there are many tools to help automate the process including balance transfer tools and transaction copiers. There is a detailed Set Up Case Study to provide step by step instructions.

 

More information on this topic

Year End Issues

Cash vs. Accrual