There are times when the report you want to get from QuickBooks just doesn’t exist within QuickBooks. That doesn’t necessarily mean you can’t create the report you need. The more advanced QuickBooks Enterprise versions provide ODBC connectivity with Crystal Reports and Excel to give users access to more powerful reporting tools but even Pro and Premier users have options if they know how to use them
I am working with a small manufacturing company that had moved to QuickBooks from another application and wanted to duplicate a great management report that provided an item profitability report by customer. I recognized that this was a great tool because, due to customer discounts, item profitability would be different by customer. Unfortunately, QuickBooks only reported profitability at an item level so I had to find another way using QuickBooks Pro. I was able to accomplish this by using the transaction number assigned by QuickBooks to each accounting entry. I created a Custom Transaction Detail report for selected sales and cost of sales accounts and selected the transaction number to display in the report.
I then exported this report to Excel and used VBA programming to identity the sales and cost of sales components of each item included in a transaction. The table below is organized so you can see how I was able to identify these components from the QuickBooks custom detail report.
Once I had the data I needed in Excel, I was able to use Excel’s internal macro tools (VBA) to slice and dice the data any way I wanted. The table below shows one of numerous reports that would now be possible to create in Excel.
Robert Guild is certified QuickBooks ProAdvisor in Austin, TX who conducts CPE courses for CPAs and individual training and group classes to QuickBooks users. His company at www.QBCoach.biz, maintains a sixteen-station QuickBooks lab, providing hands-on training. You can contact him directly at rguild@QBCoach.biz or follow him on twitter at QBPro