QuickBooks File Check Up

Kathy GrosskurthQuickBooks File Checkup

People normally do not think of going to the doctor until they are sick. The same goes for your Accounting Software. Often QuickBooks Proadvisors will get a call from a prospect when reports aren’t looking right and they need help in correcting their setup in their data file. They usually want you to take a look with the expectation that it can be fixed in an hour or less! Instead of taking their word for it and guessing what is wrong, you can do a “File Check up” and provide the client a written report when you are through. This should not be confused with doing the actual clean-up work. This service can also help you figure out the scope of the project. Basically you take a more diagnostic look at the data file, and you get paid for it!

This service can be helpful in the following ways:

  1. Making the client aware of possible setup issues they might not have known about.
  2. They are able to see a sample of your work, proving your expertise.
  3. It can help determine the scope of a support engagement.
  4. A deliverable is created for the client, even if they do not hire you.

Typically a ProAdvisor will offer this service for a flat fee of between $300-$1,000. This is based on the size of the report you will actually offer your client and how many hours you are planning to put into this analysis. As an option, you could offer a discount if they hire you for this check-up service. This will help create incentive for the client to engage.

There are four broad categories to review during a File Check-Up. You can develop your own check list based on these main areas.

Often we find that clients attempt to set up the QuickBooks file on their own without any accounting or bookkeeping knowledge. Bad setup equals inaccurate reports!

Here are just a few of the areas to check into regarding set-up.

  • Chart of Accounts. Pull up the client’s current Chart of Accounts. Each industry has their own style of accounts. Review to see if it is organized properly, separating Cost of Good sold from operating overhead. Look for duplicate accounts, accounts not organized properly, missing accounts, accounts that could be merged or consolidated.
  • Item List. Open the Items list, customize the view across the top so that Account, Cost of Goods Sold & Asset Account columns side by side. This view makes it easier to check for consistency. Make sure the mapping is correct for inventory items and service items. Also make sure they are using the correct Item type for each item.
  • Sales Tax. (optional) While you are in the item list, customize the view across the top so that Asset Account & Tax Agency are side by side. Accounts should all be mapped to Sales Tax Payable and the Tax Agency should all be the same name. If they have multi state then the two columns should be consistent.
  • Payroll Items List. (optional) Open the payroll Items list. View the list so that Expense and Liability columns side by side. We recommend setting up Liability accounts for each type, like Federal Tax Payable, State Tax Payable, FUTA and SUTA. Also on the expense side, we recommend to breaking out between Salary & Wages and Payroll Tax Expense
  • Payroll transactions (optional) Make sure the Employees are being paid and have a YTD check stub available. Make sure they didn’t add payroll in the middle of the year and skip entering the details of their employees from January 1st until current.
  • 1099 Vendors Run a preliminary 1099 report and make sure the vendors selected to be issued 1099s are showing up. Compare to the P&L where those accounts show up. Make sure they have mapped typical accounts to casual labor, subcontractor etc.

We find that when a client has attempted setup on their own the balances are often incorrect. Here are some of the areas to review to help uncover an area that may need more attention.

  • Bank & Credit Card Reconciliations. Review the bank reconciliation tool by reviewing all the outstanding checks and deposits that are older than 30 days. Items that are older than 30 days usually indicate a problem. For example:Perhaps the check was never sent, the deposit never taken to the bank and so forth.
  • Balance Sheet Review. Run the Balance Sheet and compare current data with last years data. Look over the balance sheet to see if any of the balances are much different compared to last year or if there are any negative balances. If the balances are growing over time, it usually means liabilities payments are not being recorded properly.
  • Profit & Loss Review. Look over the Profit & Loss to see if any expenses are significantly larger than prior year.
    Unexplained Accounts. If there are any odd accounts which make no sense, then you may want to drill down on those totals to look at the transaction listing to see what is going on behind those numbers.
  • Undeposited Funds. A very large Undeposited Account balance may mean that payments are being recorded and not being deposited. This account should be zero or equal to recent payments that have been recorded as payments to accounts receivable but not added to a deposit slip and taken to the bank.
  • Opening Balance Equity. This account is used only for set-up of the initial balances in all the registers. If done properly, then the Opening Balance Equity account should have a ZERO (0) balance and then be inactivated in the Chart of Accounts because the balance is transferred to Retained Earnings when set-up is complete.
  • Uncategorized Income or Expense. Any balances in either of these accounts and or “Ask My Accountant” need to be recoded to the proper account.

Using improper workflow will render the financial reports useless. These areas will help determine where the problems lie and then you can annotate what needs to be done to fix them:

  • Negative Inventory. Run an inventory summary looking for negative inventory amounts or values. Include these in your report.
  • Accounts Receivable. Run an open invoice report and see if there are payments that are not applied to invoices. Make sure these are customers with open invoices listed and not journal entries.
  • Accounts Payable. Run an open bills report and see if there are payments that are not applied to bills. Make sure these are vendors with open bills listed and not journal entries.
  • Sales Tax. Find the vendor for sales tax and make sure the transactions to make payment are called “Sales Tax Payment”.
  • Payroll Liabilities (optional)- Make sure the liabilities are current and not flagged as being unpaid. Find the vendor for payroll tax and make sure the transactions to make payment are called “Payroll Tax Payment”.
  • Undeposited Funds. Go to Banking, make deposit and make sure that the payments that are showing up are within a day or so.

With the client’s file open, press the F2 key. In doing so, you will be able to tell a wealth of information on the health of client’s data file via the window which opens.

  • File Size. The first thing to look for is File Size. In QuickBooks Pro and Premier, the file size should be 100,000k or less and Enterprise can handle file sizes of 100,000k or higher. If a client Pro or Premier is getting close to these maximum targets, it may be time for them to upgrade to QuickBooks Enterprise.
  • DB Fragments. DB fragments should be no more than 10. If higher than 10, make a note where necessary.
    List Size. In QuickBooks Pro and Premier, size limits for lists are as follows: Names should be no more than 14,500 and accounts should be no more than 10,000. If a client using Pro or Premier are getting close to these maximums, then it’s time for them to upgrade to Enterprise.
  • Number of Versions Used. Take a look at how many years the client file has been kept in QuickBooks. If it’s been several years, it means the file has been upgraded over the years, and it may be a clue, along with DB fragments, list size, and file size, that the file may need further work such as verify / rebuild, movement to Enterprise (if not already an Enterprise file), or set up with new balances in either QuickBooks Pro/Premier/Enterprise. Balance Sheet in Balance? Run a balance sheet and verify the balance sheet is balanced: TOTAL Assets = TOTAL Liability & Equity.
  • Speed. How long is it taking to run a report? A minute or longer? Data files that are too big for the software program will often get super slow. If they are on Pro/Premier, they will get more speed from Enterprise. If they are already on Enterprise, they will need to get a safe data shrink from www.qbornotqb.com. Under no circumstances should you use the built in condense feature:. Although the tool is available in the software, we do not recommend using it as it has proven to cause more problems rendering the file unstable.
  • Data Verify. Be sure to run a file, utility, verify to make sure the file is ok. There is no need for you to rebuild the file, just check to see if they say there is a data integrity problem and report your findings.
    From these lists you can create your own outline or checklist to perform the deliverable report for your client. Remember whether or not you are hired for the job you have given them some valuable information without telling them how to do the work. Don’t give too much away!

Here is a sample checklist you can use to provide your clients with an analysis of the clean up needed. Include the items that are relevant.

Dear Client:
We have performed your data integrity analysis and found the following areas to review and need to clean up:

  • Chart of Accounts. These accounts need to be re-organized in order to give you better management reports:
  • Item List. These items need to be updated with the correct mapping in order to give you better management reports:
  • Sales Tax. (optional) These sales tax items need to be updated with the correct mapping in order to give you better management reports:
  • Payroll Items List. (optional) These payroll items need to be updated with the correct mapping in order to give you better management reports.
  • Payroll transactions (optional)- In review of your current payroll transactions, we found:
  • 1099 Vendors – We found the mapping needs to be include the following accounts & Vendors in order to issue 1099s:
  • Bank Reconciliations. These bank accounts have old outstanding items that need review:
  • Credit Card Reconciliations. These credit card accounts have old outstanding items that need review:
  • Balance Sheet Review. These balance sheet accounts have unusual balances that need review:
  • Profit & Loss Review. These profit and loss accounts have unusual balances that need review:
  • Unexplained Accounts. These accounts are unusual and need review for possible reclassification:
  • Undeposited Funds. These payments are old and need to be deposited using the proper work flow:
  • Opening Balance Equity. This account is supposed to be -0-, your account balance is $ . Anything in this account needs to be reclassified.
  • Uncategorized Income or Expense. These uncategorized entries need to be reclassified to the proper income or expense account.
  • Negative Inventory. These inventory items have negative quantities, these need to be corrected:
  • Accounts Receivable. These open accounts, need to be cleaned up:
  • Accounts Payable. These open accounts, need to be cleaned up:
  • Sales Tax. These sales tax payments, need to be cleaned up:
  • Payroll Tax. These payroll tax payments, need to be cleaned up:
  • File Size. Your file size is ________the file size should be 100,000k or less and Enterprise can handle file sizes of 100,000k or higher.
  • DB Fragments. DB fragments should be no more than 10. Your DB fragments are_____.
  • List Size. Your total lists are_________
  • Number of Versions Used. You have used your QuickBooks since _______.
  • Balance Sheet in Balance? Yes or no…
  • Speed. We found your data file to be FAST, NORMAL, SLOW….
  • Data Verify. Your data file verification indicated:

Whenever we get called by clients, and have to go behind someone else’s work, it’s really hard to gage the condition of the file and provide a quote for clean up work. With all of the issues that can arise from setting up a QuickBooks file wrong, we strongly advise you to offer this service to prospects. You can also provide file checkups with your clients annually. Use these tips and tricks to better service your clients. If you are interested subcontracting this type of work to another CBG partner, you can email Kathy at kathy@bookkeepingcleanandsimple.com