I was working with a client, a group of consultants, last week whose business plan required that they pend partner reimbursements for out of pocket expenses incurred on a project until they had received payment for that project. This required that they select bills to pay not on vendor alone but by project by vendor. Here’s how I created a report for them in Excel.
The payable side of a bill transaction does not contain information about the project because a single vendor bill is designed to handle multiple types of purchase transactions. For example, a bill from a subcontractor might include multiple projects and/or expnses that might not be project related. There still is a means to, not only get the bill detail, which includes the customer/job information, but identify the bill that charge is included in and, therefore, know whether it has been paid, when it is due, etc.
We can do this by using the “Journal” report in the “Accountants & Taxes” menu, which gives all sides of a transaction. Simply filter the report for a “Bill” transaction type and select the appopriate date range to get a report you can export to Excel. Once I had the data in Excel, I was able to write a VBA macro that created the report the client needed.
The client can now simply look at this report for “Unpaid Bills” as a source for payment selection or all bills to assure that the partners have submitted all reimburseable expenses.
Robert Guild is certified QuickBooks ProAdvisor in Austin, TX who conducts CPE courses for CPAs, individual training, consulting, 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