How to Quote a QuickBooks Data File Cleanup

The secret to QuickBooks is a good setup. However, customers that cut corners may start using the program without any help, ultimately creating a big mess. When they need to prepare financial reports for any reason, they suddenly realize it’s time to hire help. Sadly clients do not understand the amount of work a clean-up can take. Typically they think the problem can be fixed in an hour or less!  

It’s important for all involved to understand that a bad setup can take three times longer to fix than setting it up right the first time. The best exercise is to offer a Data File Review where you review the setup, check for mistakes, and prepare a report of your findings. At this point, they can decide if it’s worth the repair or if they want to start fresh.

When you first get a project like this, it is tempting to open up the file and start fixing the mistakes, but it’s like unraveling a blanket that you are knitting. It’s typically not something you can fix in just an hour. Use this report to communicate to the customer how many mistakes there are and what needs to be fixed. You can design your scope of work from this document.

As we talk about the QB Data file reviews, I need to tip my hat to the late Queen of ProAdvisors Bonnie Naganyami. Her method lives on with many consultants. It has been adopted and customized to work as a tool in cleaning up a data file in any version of QuickBooks.

After a couple of these file reviews under your belt, you create your own template and method to make these smoother so that you can repurpose your report. This report will show your prospect that you are knowledgeable and how much of a mess they made of the file. Once you present the report, they can decide whether or not to work with you.

This service can be helpful in the following ways:

1. Make the client aware of possible unknown setup issues.
2. See a sample of your work that establishes 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 provide the client and how many hours you plan to put into this analysis. As an option, you could offer a discount if they hire you for this checkup service. This will help create an incentive for the client to engage.

There are four broad categories to review during a File Checkup. You can develop your own checklist based on these main areas.

SETUP PROBLEMS

We often find that clients attempt to set up the QuickBooks file independently without any accounting or bookkeeping knowledge. Poor 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 its 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. On the expense side, we recommend breaking out between Salary & Wages and Payroll Tax Expense.
• Payroll transactions (optional). Ensure 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. Vendors Run a preliminary 1099 report and make sure the vendors selected to be issued 1099s show up. Compare to the P&L where those accounts show up. Make sure they have mapped typical accounts to casual labor, subcontractor, etc.

BALANCES

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 older than 30 days. Items that are older than 30 days typically indicate a problem. For example, a check that was never sent, a deposit never taken to the bank, and so forth.
• Balance Sheet Review. Run the Balance Sheet and compare current data with last year’s data. Look over the balance sheet to see if any of the balances are significantly different from the prior 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 the prior year.
• Unexplained Accounts. If there are any odd accounts that make no sense, 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 the setup of the initial balances in all the registers. If done correctly, 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 setup is complete.
• Uncategorized Income or Expense. Any balances in either of these accounts and/or “Ask My Accountant” need to be re-coded to the proper account.

IMPROPER WORKFLOW

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 a deposit, and make sure that the payments that are showing up are within a day or so.

HEALTH OF DATA FILE

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 the 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 is 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. 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 you are hired or not for the job, you have given them some valuable information without telling them how to do the work. Don’t give too much away!

SAMPLE CHECKLIST

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

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 should be re-organized in order to give you better management reports:
• Item List. These items need to be updated with the correct mapping to give you better management reports:
• Sales Tax (optional). These sales tax items need to be updated with the correct mapping to give you better management reports:
• Payroll Items List (optional). These payroll items need to be updated with the correct mapping to give you better management reports.
• Payroll transactions (optional). After review of your current payroll transactions, we found:
• 1099 Vendors. We found the mapping needs to be included the following accounts & Vendors 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 workflow:
• 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 and 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 tough to gauge the file’s condition 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 prospective clients. You can also provide file checkups with your clients annually. Use these tips and tricks to better serve your clients.