Whether you’ve been using QuickBooks™ for years or just a just a short time you probably noticed all the updates and versions Intuit releases. With all of that activity and attention you trust that your data is in good shape. After all, people have been relying on QuickBooks since 1992.
Well, it’s time to tell the truth about the health of your QuickBooks company file.
In a perfect world, your software and data would work smoothly, doing precisely what they are designed to do. Unfortunately, we live in a dynamic world–both natural and digital–and things degrade, no matter how cautious you think you are. It’s called entropy.
Simply defined, entropy is a gradual decline into disorder
There are four factors that affect the decline – and performance – of your file:
- File Size
- The number of DB File Fragments
- The earliest of the Versions Used on File
- Daily use and bad user habits
To locate details on the first three of these items for your own QuickBooks Desktop company file, hit the F2 key once the file is open. You’ll see the Product Information window. (In Figure 1, I’ve highlighted the items I’m referring to.)
Figure 1. Open QuickBooks and hit F2 on your keyboard to see the Product Info screen
Let’s review these factors, and the actions you can take to protect your data against corruption and loss.
There is Such a Thing as Too Big
From our years of hands-on experience working with hundreds of QuickBooks files, we have established real-world limits on the sizes of QB files before corruption and performance become serious issues:
Pro: 250 MB
Premier: 350 MB
Enterprise: 1.25 GB
Intuit, the maker of QuickBooks, seems to have a different take on file size. For instance, some support agents have told our customers that Enterprise can be 4GB or larger. Perhaps in a sterile, carefully tended environment. But that’s not the way small businesses run. We currently have a Pro customer whose file is almost 700 MB. It’s incredibly slow to open and to load into the data warehouse we use. For two years I’ve recommended that they start a new file. Now they are asking if integrated applications can sync more quickly. We design miracle reports, but we can’t help with that miracle!
If your company file is close to, or has exceeded, the maximum file size for your version, there are three options for you to take:
- File Split. If your file is otherwise healthy, we can refer you to Advanced Certified QuickBooks ProAdvisors who specialize in eliminating old data. It’s often faster and less expensive than creating a new file. This is probably not an option, though, if you use Advanced Inventory or Pricing Levels.
- New File Creation. If your file has corruptions, is older than 10 years, or a File Split will only create a temporary reduction, you will want a new file created. I cannot stress enough how critical it is to hire an experienced ProAdvisor to perform this project, as it’s an involved project. I have a colleague who wrote a 57-page document explaining most of the process. It really needs to be a coordinated effort.
- Do Nothing. If your file is relatively healthy, you can temporarily do nothing. This is temporary, however, and you risk the loss of some or all of your data, even if you do regular backups.
As a fellow business owner, I urge you to take this caveat very seriously. I have had a customer call me in a panic after receiving a scary error message from QuickBooks. The file was kaput and the only option was to start from scratch and re-enter months’ worth of transactions.
Issues Beyond File Size
For most businesses, file size isn’t the most common problem. In fact, it’s often just a symptom. The problem is actually simple: you’re using it. Opening and closing a file can lead to degradation and entropy. Software glitches, power surges, and just sharing or copying a file to a new location can cause corruption.
Let’s say you’ve been using QuickBooks for quite some time. In 10 years, having just 3 users who are opening and closing a file twice per day (because you know… it crashes sometimes) means there were over 15,000 opportunities for something to go wrong.
If your file is larger than the sizes already mentioned, your file is at greater risk because it runs more slowly. More time opening, saving, and closing means more exposure to the problems previously discussed here. If we include bad user habits, like not exiting at the end of the day, leaving transactions open and incomplete, we see even more corruptions and broken links.
One indicator for this kind of issue is the number of DB File Fragments. See Graphic 1. File Fragments are when QuickBooks has started saving packets of data in non-adjacent space on the hard drive. The more spread out the related packets are, the slower the performance of QuickBooks. There is a simple way to reduce or eliminate the file fragments – see the section at the end of this article.
As you use QuickBooks on a day-to-day basis, you likely will not notice there’s a problem. Sure, it may glitch or crash now and then, but it starts right back up and your transactions are still there. But entropy is real, folks, and without implementation of solid data policies, your data could be at risk.
Fortunately, you can take steps to protect your data.
Implement Data Policies
Investigate the topic of data policies. Plenty of accounting systems don’t allow you to keep more than two or 3 years of historical data in the active file. Before QuickBooks came along, every accounting solution gave you two years of data MAX, and you had no choice – and that included Peachtree and Great Plains. You can always keep your old file for reference purposes. Something worth considering. I promise you – you do not need 25 years of accounting data in your active file.
I just have to share something I saw yesterday – one of our customer’s files has been in use since it was a Quicken file. And it’s 1.3 GB. You can bet that the owner of the company is going to get a strongly worded invitation in the very near future to get a new file created.
Early Detection and Correction
QuickBooks does have data verification and rebuild tools built in. These tools are easy to use–just access the File | Utilities menu. The problem is that oftentimes the software will report that “QuickBooks detected no problems with your data” and then you might believe it. Some customers run a “Verification” thinking that will do something to the file – but it doesn’t, and it doesn’t always report problems when the exist.
All of that makes the job of a trusted advisor much more difficult. Some customers don’t take our warnings seriously, or they ignore them altogether.
Age is Just a Number … Or is it?
In 2006, Intuit made a significant change to the data structure and the way it is accessed, while still accommodating the way existing customers had been using the software. This means there may be data in fields where it no longer belongs because it’s a different data type. For example, there may be text in a field designed for phone numbers. If your file has been in use since 2006 or earlier, your data could be at risk. In this case we recommend a New File Creation. We found a customer last week who has been using their file since it was a Quicken file. No joke.
Why File Care is Important
There are many reasons file care is important – but the biggest is that you, the customer, can’t tell it’s happened.
In 2013, I discovered a tool called QQube that makes it easier to access the data in QuickBooks. It uses the software development kit (SDK) and the Custom Reporting function to extract data from the system. The SDK sniffs out data corruption, never lies about it, and simply won’t transfer the data out. Initially, I was frustrated by this. But after finding so many files that had otherwise undetectable issues, I realized it’s the only early warning system we’ve got.
Your QuickBooks users can help with file care every day. This means always closing all open windows before exiting the program entirely. Many users who access QuickBooks via Remote Desktop (formerly known as Terminal Services) only close the Remote Desktop window – and forget to exit out of QuickBooks. This can cause issues.
Another cause of corrupted files is weak or faulty network connections – which can be caused by a loose or broken Ethernet cable or intermittent WiFi connections. These dodgy connections interrupt the process for saving transactions.
You may have noticed that QuickBooks sends out frequent fixes and updates. With every release, there are changes under the hood. When you do not establish a strict software update policy and process, QuickBooks allows users to continue using older releases. This, too, can cause problems.
The Real Cost of QuickBooks
Selecting an accounting system to suit your operational needs–and your budget–is obviously critical. When you start out small, QuickBooks can usually handle the requirements. The problems start once significant growth begins – or when no one is tending to file care.
It’s the choice between pay now – or really pay later. In our view, if you opt to use QuickBooks, you’re choosing to pay later. What you’ll pay for is a new file creation or file reduction every few years, to the tune of $2,000 to $20,000, depending on how much historical data you truly need to keep in your production file.
QuickBooks Desktop is an attractive, affordable option. But you need to understand how to care for your valuable data. It simply is not “set it and forget it.” Here are tips you can implement to help protect your business:
- Set and follow a regular Rebuild schedule
- Read here for excellent instructions and best practices for a Rebuild
- Understand that running a “Verify” doesn’t do anything to the file – and may not detect significant issues
- Perform a “Portable Copy Round Trip” to reduce the DB File Fragments to under 10
- Don’t use the Condense feature – except, perhaps, on the Audit Trail if you’re using it
- Delay upgrading to the newest year of QuickBooks until around 6 months after its release
- Create and adhere to an update policy that:
- Disallows automatic updates
- Only allows IT to perform the update
- Updates everyone on the next login
- Don’t assume your IT provider knows how to manage and maintain QuickBooks
- Respect the minimum hardware specifications, especially for RAM, processing speed, and disk size
- If your file is larger than recommended in this article or older than 10 years, bite the bullet, engage a pro, and get a fresh new file created
We Love QuickBooks, But…
It seems like Intuit doesn’t instruct end users how to care for their files until there is a problem. And very often, that’s too late and the problem cannot be resolved. While I appreciate that we live in a dynamic world, I consider this to be unethical. A few years ago Intuit ran a QuickBooks campaign with the slogan, “Own It.” In my opinion, if you’re going to own QB, you’d better be willing to own the limitations that accompany the very reasonable pricing. It’s my hope that Intuit owns it, too, by:
- Telling customers the truth about file limitations
- Instructing customers how to care for their QuickBooks files
- Removing the condense feature
- Disallowing the use of multiple releases on the same file and network
Most business owners, and even many Certified QuickBooks ProAdvisors, are genuinely surprised to learn there’s a problem with the files. After all, it looks fine, right? But the truth is it just may not be. The older your file, the longer you use it, and the increased entropy that results may be putting you on the brink of a major league data crisis.
How to do a Rebuild
Contact us for a list of amazing ProAdvisors who specialize in New File Creation!
So You Think You Want a Dashboard?
I truly hope not. That is, I truly hope not if you think it won’t require significant effort on your part.
In case you’ve missed this concept over the past decade, a business intelligence dashboard is a data visualization tool that displays the current status of metrics and key performance indicators (KPIs) for an enterprise. Dashboards consolidate and arrange numbers, metrics and sometimes performance scorecards on a single screen.
What’s the Problem?
The problem is dashboards are surprisingly difficult to create. It’s not because we don’t have the technology. No – we can build them with our eyes closed. Dashboards are difficult to create for the following six reasons, five of which have everything to do with you.
- You don’t know what to measure in your enterprise.
- The point of a dashboard is to present up-to-date insight on meaningful business success metrics. The instant you see the display, you should know exactly what action to take to move the gauge in the desired direction. If you can’t act, your dashboard design has failed.
- Every role in your organization needs its own metrics.
- In any social group (e.g. business), individuals occupy and perform roles. Roles are made up of organized practices; a unit or set of organized reciprocal behaviors or acts.
- Since each role is different, you’ll need to measure different things – and have different dashboards.
- You are measuring the wrong thing.
- Measuring quotas and results provides you with less insight than you think. Instead, try measuring the actions you have pre-determined SHOULD produce those results.
- Your systems don’t – or can’t – capture those actions.
- Recognition and remuneration are not tied to taking the right activities – nor to keeping the data current.
- If your team members aren’t measured on taking actions and updating the data, you have nothing to measure BUT results after the fact. You won’t know what correction to put in.
- Thinking and planning are hard work, and you don’t want to do hard work.
- Even I find it challenging to sit and really focus in these days of distraction. But that’s what it takes to do good dashboard design. You can’t avoid it by hiring a consultant. They will just hound you until you sit down and answer their questions to draw out your goals.
Excellence Requires Effort!
A few years ago I was working on a project for a major US bank. The bank’s team got very excited when they heard that they could have a “dashboard” – a single view of multiple key performance metrics in graphical form. When I asked what they intended to measure – and what decisions and actions they needed to make based on these measures – the responded with “Well, we don’t know. Can’t you just give us the industry standard graphs?”
Well, uh, no.
Do you want to be “industry standard” or do you want excellence?
If you are willing to do some hard thinking, align your recognition practices, and confront your innate energy-conserving laziness, a dashboard might help your business. The tool you select to display your metrics is the least important and final decision in the process. But the endeavor, if done well, could transform your company’s future.
If you’d like to learn more, or schedule a dashboard inquiry session, we’re happy to help. The same questions that we use for custom reporting apply to dashboards.
The passage of time is mighty sneaky. I realized November 2016 was the 20th anniversary of my first career job. Desperate to work at a software company, especially after reading Microserfs by Douglas Coupland, and having just received a diploma in Computer Systems Management, I was hired as a tech support rep at Seagate Software in Vancouver. The product we supported? None other than Crystal Reports.
It’s unusual, these days, to have a single software application buoy one’s entire career. That’s a testament to the value, utility, and quality of that program. It’s also further evidence that it takes 10,000 hours to achieve mastery. In realistic terms of day-to-day practice 10,000 hours is about 10 years. And now I’m at 20 years.
So what is this thing called a career? Merriam-Webster’s simple definition is:
A job or profession that someone does for a very long time.
I’d say that’s how most of us think of the word “career”. And I almost dumped my career due to that line of thinking.
About five years ago, I started getting very bored with what I was doing in my career, this thing I’d been doing for a very long time. Don’t get me wrong, though. I was very proud of the work that I’d done to help my customers. I was, and am, grateful for the lasting friendships I’ve forged with customers and colleagues around the world, and all the unique experiences we’ve created both inside and outside the work environment. It’s just that I couldn’t see myself doing only that for another 20-30 years. Besides, there are limits to what one person can produce and to how long I can sit on my arse in front of a computer. And I’d reached those limits.
Education Keeps You Passionate
Something that has kept me from getting bored is engaging in education. I especially love learning alternative, cutting-edge ideas on how to live a better life. So this 20-year anniversary coincides with a 5-year anniversary of learning a new, yet very fundamental, approach to achieving satisfaction in many areas of life. It’s called Transactional Competence, and it’s found at Influence Ecology. Without it, I doubt I’d be celebrating the big 2-0.
A More Constructive Definition of “Career”
Here’s how Influence Ecology defines “career”: it’s the help one is known for in specific groups or ecologies – one’s identity in the marketplace. It’s an unavoidable condition of life all functioning adults must deal with. It’s also very distinct from two other unavoidable conditions, ones that are usually collapsed with career: Money, and Work.
The Value of Specialization
Discovering that I could transact to be known for some kind of specialized help allowed me to expand my thinking and acting about how I wanted to be known. Consequently, I was also able to earn more money.
What’s so special about specialization? Well, everything. When you want your home’s toilet fixed, you want someone with loads of experience who can quickly and accurately identify and solve the problem. You want a residential plumber. You don’t look for a general contractor.
Five years ago I truly believed I was as specialized as I could get. I was known as the Crystal Reports Goddess. That started out as a catchy thing to say, but then a former boyfriend registered the URL as a surprise gift. It stuck. I really was one of a handful of go-to-folks for Crystal Reports. And I really am great at designing and building amazing database reports. Sometimes what I come up with reminds me of what Charlie Parker said about playing music.
You’ve got to learn your instrument. Then, you practice, practice, practice. And then, when you finally get up there on the bandstand, forget all that and just wail.
Yes, I probably sound arrogant – but from what I’ve learned in my studies about Transactional Personalities, arrogance is both an asset AND a liability. It’s not a surprise I was frustrated by the constraints of being an employee, that I get bored easily, or that I have enjoyed the thrill of parachuting into the back-end systems of hundreds of organizations. A consultant is expected to navigate many different database systems, and quickly identify database structure, bad design, dirty data, and possible solutions – within hours of arriving. Not to mention ferreting out who needs what data, and in what format. All of this is simply puzzle-solving, and for me, that’s pure fun.
So clearly I was a specialist. And now I was being challenged to specialize even further? You can read the story of how I arrived at my new hyper-specialization in this post – but the short version is that we build custom reports and dashboards for QuickBooks – ones that QuickBooks actually can’t do. We use a third party tool to work our magic.
Making that leap to super-specialization produces often unexpected benefits. The first is that it becomes incredibly easy to articulate what you do: We do one thing. And in our case QuickBooks is well-known enough that people instantly grasp what we do. They will even say “Oh – I know someone who could use your help.” They also tend to recall what we do long after we first meet, simply because we’re the only ones doing it.
Tune Out the Naysayers
The marketplace vacillates between encouraging generalists and specialists. I definitely receive some dissent about what we’re doing. “Why limit yourself that way? Why don’t you also work with Sage or Microsoft Dynamics?” I appreciate that these folks would like our help. But we won’t – because of the other benefits our level of focus affords:
- We only have to learn one system. It’s easy to install, easy to teach, and easy to maintain.
- It’s a system that doesn’t accommodate many customizations. This means we are dealing with the same system with every customer. That helps maximize our efficiency. Even when they use it slightly differently – or even incorrectly – we are so familiar with it we can identify the differences very quickly.
- Our own systems are less costly to support. We need just three applications to do what we do.
- We can work remotely and deliver remotely. No centralized systems required.
- We don’t need to sell our solution. We just need to make the right people aware of it. When they come to us, they already know about the problem.
I think I can now bring these ideas full circle – back to career. Applying what I’ve learned with Influence Ecology has, without a doubt, enhanced my career, as well as my enjoyment of it. It would have been a tremendous waste to turn my back on 16 years of specialized knowledge, experience, and good reputation just because I was bored.
But I’m one of the fortunate ones who had focused enough to have a career foundation on which to build. I know plenty of lovely, ethical, hard-working people who are all over the map when it comes to career (and subsequently, money). I fear Millennials, if what the media says about them is true, jumping from job to job dissatisfied if not given promotions, raises, or sufficient responsibility, will suffer from misguided, incorrect thinking about career, work, and money. There is another dimension they may need to learn and practice, and that’s Transactional Competence.
I’m grateful for the meaningful work I’ve been able to contribute to the success of my customers. I’m grateful for being treated fairly and well in the many environments I’ve worked. And most of all I’m grateful for the fortuitous introduction to the curriculum at Influence Ecology. Looking forward to the next 20 years!
For some, there’s a love-hate relationship with QuickBooks.
People love QuickBooks because it’s relatively simple, and lots of people are familiar with it.
People hate QuickBooks when they need to see their financial data in a custom format. The truth is, as many soon learn, businesses can’t always function efficiently with the standard layout.
With QuickBooks you’re forced to export to Excel and then copy and paste and recalculate summaries and reformat and … well, you can see that’s an arduous and dreadful process.
Here are the side effects of running manual processes like this:
- Professional Reputation – when your reports are incorrect or look crappy
- Stress – who has the energy to futz around with Excel like that?
- Wasting your valuable time and career expertise
Maybe QuickBooks should come with a warning label.
I have a confession to make
Until 2015, I had avoided financial reporting for almost ALL of my career as a custom reports specialist. It seemed boring to me. Dead boring. (No offense to CPAs, controllers, and CFOs in the audience.) It just wasn’t my cup of tea.
When I first attempted custom reports for QuickBooks, 4 years ago, the main requests were for Sales and Commission Reports. It took another year before someone asked for a custom P&L (also known as an Income Statement – but at the time, I still didn’t know that!).
Then it dawned on me, I needed to do a crash course in financial reporting – because a good report developer needs to understand the meaning and purpose behind every element displayed on a report.
Coincidentally, and extremely fortuitously, the next assignment in a business program I was taking was to complete an online course called Financial Intelligence. (Actually – we’ve made that course available for you at a reduced rate. Find out more here.)
The lights went on for me. I could now define financial jargon and acronyms like EBITDA.
I could also appreciate the reasons a business owner might like to see expenses for Interest, Taxes, Depreciation, and Amortization displayed separately from regular operating expenses.
It gives a different perspective on Net Income. A more realistic perspective.
I quickly realized QuickBooks alone wouldn’t cut it
Luckily, the solution wasn’t out of sight.
Adding QQube® by Clearify is like adding jet fuel to QuickBooks.
When you combine QQube with expert report designers using tools like Crystal Reports, you’ve got rocket fuel – and no need to upgrade QuickBooks to a more sophisticated system. Why? Because we can program the existing data to appear just about any way you need it.
Here are just some of the custom reports solutions we’ve provided:
- Custom Cost Of Goods Sold percentages – for any account, and placed anywhere on the page
- Rolling 12 months of P&L results
- P&L with Budgets AND Forecasts (sure can’t do that in QuickBooks!)
- Inventory + Sales History – in the same view
- Construction/Job Cost Report with Gross Margin $ and % with drill-down to every line item that makes up that total – including Journal Entries
- Consolidated Financials for any number of QuickBooks company files
Custom Reports for QuickBooks – the way YOU want them
Exporting to Excel to get what you need makes me cry for you. You’re better than that.
You should at least talk to us to find out if relief is available – and determine what that relief is worth.
And by losers, I mean people who aren’t winning in the game of business. (The totally generous definition of loser.)
The sad truth is that DIY is a seemingly harmless trap that so many of us fall into.
A few months ago we created a great job cost report for Jack. We made it look so darn easy, Jack thought he could save money by learning Crystal Reports himself.
Jack’s anything but incompetent. He’s a highly accomplished, professionally licensed engineer/business owner in the Oil & Gas industry. His company earns about $10,000,000 in revenue.
After we met for breakfast a few months ago, here’s what he wrote to me…
It seems Crystal Reports is harder than it looks. The irony of this all is I am now $2000 deep into using a different programmer to write another Crystal Report for us. It looks great and the data is finally there…but in hindsight…as always…I should have used Datisfy.
Swallowing a humble pill right about now.
Name Withheld (to protect the not-so-innocent)
To create powerful, well-designed custom reports for QuickBooks®, it takes the following abilities:
- an understanding of QuickBooks® transactions
- a deep knowledge of what fields are made available by QQube® in each subject area
- 5-10 years of focused specialization on Crystal Reports
- 5-10 years of working with hundreds of customers to understand how to ask the right questions and get a report exactly how a customer needs it
Don’t be a loser.
Are you planning to invest that kind of time and effort?
No? Then the DIY route is certainly a good way to lose.
This simple calculation might make your cringe. Sum the amount of time you spend trying to get reports the way you need them, and then multiply that by the number of times you need those reports in a month or year. And then multiply that by several years.
Do what counts – not what costs.