Check Your Company File Health

Posted on Posted in Tips & Tricks

Its That Time of Year to Pay Attention!

What you should prepare for now, before it costs you more later.

We are just now arriving at the mid-point of the last quarter of the year for most businesses. By this time you should have already analyzed your projected year end financial results and looked at your probable tax liability for 2018. Plus implementing plans to improve your financial results and reduce your tax liability. No ? You have been so busy with the day to day operations that you haven't had any time to consider this very important "Owners Responsibility". This really is an important habit you should really consider performed multiple times per year, but certainly before the end of the year has completed.

If you unfamiliar with this process, or simply do not have the time to do it yourself ... get help! This may be a service provided by your accountant or CPA, but do they understand your business and its documented profit targets to gauge and analyze your financial strength ?

Primarily Accountants and CPAs focus on two things: Tax Liability and Compliance with Tax Liability including Payroll Taxes. They most likely have no idea where you are making the normal profit levels for your business, or where you are losing money. That simply is not their typical function for small businesses.

We work with small businesses worldwide, and have seen so many small business owners ignore this process until its simply too late, and state, "I don't know what happened, we seemed to be very busy, but I still can't pay all the bills when due." How can that be ?

Many small business owners translate having a work backlog as a profit indicator. Even though there can not be profit without work coming in your door, having lots of work does not mean you are going to be profitable. Using good analysis, along with good strategies are how to ensure a long term profitable business. Using benchmarking and dashboards show you exactly where your business strengths and weaknesses are, and the information to correct the weaknesses.

The Time to Look at Your QuickBooks Company File is Now !

If you are using QuickBooks Desktop, you should follow the steps listed below immediately before any of these issues cost you thousands of extra dollars and time later. If you are a Pro or Premiere version user (not Enterprise), you should immediate review the section below about "Name List Limits". If you are approaching the limit, this is the time to address it... not after the first of the year.

QuickBooks keeps all your data and most settings in a Company File which has an extension of ".QBW". This file needs to be maintained to be healthy. If your Company File becomes corrupted depending of the severity, it could become unusable, or require some very expensive recovery.

This area most of my clients were not aware of to perform, and often I receive panic calls that their QuickBooks will not open, or they cannot add any more records, so please take the time to read this section ... it could save you thousands of dollars !

To let you know upfront, most of the problems can be avoided by regular maintenance and review of key limitations. Most of these can be performed by you, and not require our services. However, we can assist you if you do not feel confident to do these on your own.

Once you log into your Company File, Press [F2] or [Ctrl] + 1 to open up the QuickBooks Product Information screen:

Figure 1 - QuickBooks Product Information screen provides very valuable information about your Company File.

There are several areas on this screen you should pay attention to. If you don't you may be surprised with issues that can cause expensive services.

The first area to keep in mind is there are some different limitations between QuickBooks Pro/Premier compared to QuickBooks Enterprise. If the Top Product title includes the word "Enterprise", the product you are using would be Enterprise and not Pro/Premier. In the above case, the version in QuickBooks Premier (Accountant Edition) 2015.

There are suggested limitations for each version as far as database size, database fragmentation, and names.

Database Size

The size of the database is directly related to the number of transactions you have in QuickBooks. Some businesses grow their database faster than others. This is not only how many Customers or Jobs you do, but also how many transactions are within those jobs. If you order parts or materials for a job and they are billed to you on one bill, it counts as a single transaction. If you have multiple times these are ordered, each with its own bill, it multiplies the number of transactions.

As the database size grows, it may slow down the speed of operations. As an example, a report may take up to 20 - 30 seconds to open. In addition, as the database grows past general guidelines there is a higher risk for corruption.

General Database Size Guidelines:
  • Pro/Premier 200mb
  • Enterprise 300mb

In the above example Figure 1, the database size is listed at 298332 K ... that roughly translates to 298mb, almost 100mb larger than the general guidelines for Pro/Premier.

Database Fragmentation

This is also an important guideline that reveals how healthy your database is. This is much like fragmentation of your hard drive. Information is not stored efficiently and access to your data is being challenged. In all desktop versions the general guideline is to maintain this at 20 or below.

The example in Figure 1 (Above) is listed at 653 ! This can easily cause issues and corruption is very possible.

Pro/Premiere Users Pay Attentions !

Figure 2 - QuickBooks Product Information screen with file size and fragmentation okay

In the above Figure 2 the database size is at 196436 K, and the DB File Fragments is only at 4, so one would consider the file healthy at this point. However, pay attention to the box titled, List Information, on the right side. The Customer List is 13,776 and the Total Names are 14,200.

For Pro/Premier, the combined Total Names is limited at 14,500, and Customers, Vendors, Employees, and Other Names themselves are limited to 14,500. So in Figure 2, this company file can only add a total of 300 combined of new Customers, Vendors, Employees, or Other Names until the panic message appears from Intuit to, "You have reached the maximum list limitation, upgrade to Enterprise". Enterprise basically has no practical list limits.

Now before you decide to upgrade to Enterprise there are several other options to consider. These are too lengthy to discuss in this article, but you can always contact us through our online scheduling portal at aeiiScheduling.com and select the Free 1 Hour New Client Session and then select the best time for you on our schedule to review the options and suggest the best one for your business.

I will tell you that all the other options verses upgrading to Enterprise will save you considerable dollars. Currently, based on the the number of users you have Enterprise will cost between $150.00 to $250.00 per month. You never own Enterprise, you rent it.

If from our determination, you will reach the limit sometime in 2019, the time to address this issue is now, and have it resolved by 2019. However, if you wait to contact us until December, it is likely we will not be able to assist you until 2019 which will make additional data entry necessary for you. Look now ... schedule now !

What Else To Do ?

Database size is a very tough one to do much about other than the condensing processes, or doing a company divide. Both of these should be handled by an experienced QuickBooks service provider such as AEII, QuickBooks R Us.

In most cases we see clients with database sizes up to 400mb with Pro/Premier have few issues as long as they follow the maintenance steps listed in the next section.

Special Note:

If you use a management system to bring in Vendor Bills and Vendor Credits, you will notice that the Bill in QuickBooks includes two entries that really aren't needed, but increases your database size and Name Lists unnecessarily.

Figure 3 - Typical QuickBooks Vendor Bill from Management Systems using Items

In Figure 3 the Customer/Job is listed on the Bill as well as the checkbox for "Billable" checked. Even though I have provided this to the major management system companies as ill advised, they have not fixed it, or at least make it an option during setup to turn off.

The "Billable" checkbox if activated causes QuickBooks to add the Bill Items to a database table tracking expenses for reimbursement. This is not used by 95% of businesses, because it tracks your reimbursed items by Job at cost, not retail. This is used by a profession that is billing a client for reimbursed expenses such a hotel, meals, transportation.

Go to Reports > Jobs, Time & Mileage > Unbilled Costs by Job ... You may find that QuickBooks has millions of dollars listed as "Unbilled" depending how long you have used a management system with QuickBooks. These entries are increasing your database size unnecessarily.

Also the Customer/Job entry is designed for job costing individual jobs, but in most businesses, you can't accurately job cost individual jobs without very detailed entry in your labor costs/payroll. Most businesses that have management systems utilize the management system for individual job costing and QuickBooks for month end total profitability. Hence the Customer/Job entry is not needed in QuickBooks, and also adds to the database size greatly.

Maintenance You Can Do

On a regular basis, monthly in most cases, you should not only look at the Database Fragmentation, but also check the integrity of your data, and rebuild it if needed.

Go to File > Utilities > Verify Data:

Figure 4 - QuickBooks Utility Menu for Verify and Rebuild Data

This process for verification will scan your file for data integrity issues. If there is a problem a Warning Message appears suggesting to "Rebuild" your data. The Rebuild Data is just below the Verify Data option in Utilities.

Before you Rebuild there are important steps to follow:

  • The QuickBooks File should be temporarily moved to your local computer.
  • It IS NOT advised to rebuild your data on a Network Drive or mapped drive.
  • The process will make an Automatic Backup before Rebuilding ... put it on your desktop
  • If the process rebuilds and you get a "Rebuild has successfully completed" message, you should log out of QuickBooks, reopen your local company file, and verify again, and Rebuild again.
  • Sometimes it may need multiple rebuilds.
  • Check your Database Fragmentation ... it should be 20 or below
  • Don't forget to put your QuickBooks Company File (.QBW) back on your network.

If your Database Fragmentation is still too high, or you continue to receive Rebuilding Errors, you should contact us. There are a few additional procedures and special tools that can be performed that may fix the issues without tougher and more expensive options.

Keep in mind, we are here to help in any way we can to assist you and keep your data safe and healthy. Keep your data from becoming sick just requires some basic maintenance in most cases.

QuickBooks Training

Closing your QuickBooks monthly needs to be a high priority for all small business owners. You should have completed final monthly financial reports by no later that the 5th of every following month. I know most that are reading this, are no where close to this goal, but it can be done, and it is critical for the sustainability of your business.

There are several resources you should consider to reach this goal ... the first being our company AEII, QuickBooks R Us. We are here to assist any small business and provide the training and support needed to achieve their goals.

That being said, we are excited about 2019 because we have many LIVE, VLIVE ™, and Online QuickBooks and Financial Management Training Programs being scheduled. To learn about these programs go to our website at QuickBooksRus.net.

To check out available 2019 training dates check out our Online Class Calendar beginning December at: QuickBooksLiveTraining.com.

Thank You and I look forward in sharing QuickBooks Tips and Tricks with you soon ...