QuickBooks CPA discusses using Excel to Customize Reports

Every QuickBooks CPA I know uses Excel to extend QuickBooks’ reporting flexibility. I sure do. I’ve never been in an accounting or finance office that doesn’t. No accounting software, even QuickBooks, handles everything a company needs. I feel the essence of being a QuickBooks CPA is to unlock the managerial potential of your accounting and bookkeeping data. Generally, most QuickBooks limitations can be overcome in Excel.

Sand Pit Example

I once setup QuickBooks for sales from a sand pit. There were five different categories of trucks to track. We had to reconcile the number of loads by truck type with the sand pit operators daily. We just ran a memorized QuickBooks sales report, exported it to Excel, sorted it by truck type and used Excel’s Count function to compare against their records. Easy.

What can you do in Excel?

Checking accuracy

In Excel you can set conditional formatting. If you’re looking at a report of purchases from Vendor X, for example, Excel can highlight amounts that fall below, say $10 and/or over $625. This helps me find bookkeeping errors.

Excel has superior data sorting capabilities. For example, I might sort that same report on purchases from Vendor X, by amount, lowest to highest. This helps me find those same types of low/high bookkeeping errors. Duplicate entries are easier to spot too.

Checking for Fraud

Excel can count how many transactions fall within a time frame. Using the same QuickBooks report on purchases from Vendor X, I might get suspicious if there are 260 transactions in January, 242 in February and 390 in March.

Combining Reporting Systems

Here’s a situation a QuickBooks CPA sees all the time. In a small restaurant, the POS system catches the Revenue and all the details about it, like the number of lobster dinners sold. But the restaurant pays for the lobsters through QuickBooks. Excel allows you to combine the data from these two systems. Now I can compare my lobster revenue to my lobster cost. Plus I can see how many lobsters swam off without being paid for! Medical offices are another example where the Revenue gets tracked through specialized billing software but expenses run through QuickBooks.

Formatting

My career often depended on being able to produce intuitively obvious reports. Who has the time to shuffle through 25 pages of numbers in a QuickBooks report? Excel has great formatting capabilities. And formatting goes beyond just making the report pretty.

Looking for Trends

I graph certain parameters over time. Excel graph capabilities are superior to QuickBooks. Plus you can combine company statistics into these charts. Graphing reveals trends, good and bad, before they might be otherwise clear.

Using Historical QuickBooks data to predict the Future

Excel can take known data from QuickBooks and extrapolate what that data should be in the future.

Internal Monitoring

When a client goes for a loan, what does the bank look at? Well, one thing is the company’s liquidity ratios, which you derive from QuickBooks. So, when you have time to optimize your company’s appeal to a bank, you could graph these ratios over time to monitor how your efforts to properly present yourself are going.

QuickBooks CPAs know there are other important sources of information for the company. A furniture store might count the people walking into the showroom. This could be compared to the number of sales that day from QuickBooks, or the average revenue dollars per person walking into the store. Both of these calculations could be used to structure sales incentives.

I’m a QuickBooks CPA with a virtual office to serve clients beyond my Wilmington NC office. If you don’t have a local QuickBooks CPA, distance is no obstacle to us in providing services. For a free phone consult, please call (910) 399-2705.

Select by Form Number

Virtual Office

Yes, we do tax returns.

It doesn't matter where you're located. Our virtual office accommodates long-distance and international clients. If you don't have a local CPA, distance isn't an issue. For a free phone consult, on most tax issues, call us at (910) 399-2705.