By Joe Woodard, AccountingWEB QuickBooks columnist
There are various tips, tricks and workarounds for handling professional billing using QuickBooks. Following are processes that work best for The Woodard Consulting Group and its clients.
Tracking client retainers
Perform the following steps to track retainers in QuickBooks:
1. Create an Other Current Liability account called "Client Retainers."
2. Create an Other Charge Item called "Retainer" and enter the "Client Retainers" liability account into the Income field.
3. Create an invoice template called "Retainer" that includes only the item column (screen only), the description column and the amount column. Make the printed title of the form "Retainer Request" rather than "Invoice."
4. When you need to receive a retainer from a client, send them a Retainer Request (i.e. Invoice) that uses the Retainer item and lists the amount of the retainer. Use the Memo field to note the retainer activity. For example, type, "Retainer Received - Data Troubleshooting Engagement."
Tip: As with any invoice, you can use QuickBooks Merchant Services combined with QuickBooks Billing Services to send this form by email and receive payment online. You can also use QuickBooks Merchant services to receive this retainer through an e-check payment.
Note: For retainer tracking to work correctly, you must use a job record for each engagement and you must apply retainer at the engagement level.
1. When the client provides the retainer, use the Receive Payments window to apply the receipt to the Retainer Request (i.e. Invoice).
2. When work is complete (or on some incremental basis) create an invoice for the client.
3. Create a Credit Memo using the Retainer item and apply the Credit Memo to the invoice you created in the previous step.
4. Print the invoice showing the total of the invoice, the retainer applied and any balance due.
Creating a Client Retainer Summary Report
Perform the following steps to create a client retainer summary report:
1. Select the Reports menu and then select Custom Summary Report.
2. On the Display Tab, set the Date range to "All."
3. On the Display Tab total the rows by "Customer."
4. On the Display Tab, click Advanced, go to the Display Rows section and select the "Non Zero" radio button. Then, click OK. to close the Advanced Options window.
5. On the Filters tab, filter by account for the "Client Deposits" account.
6. On the Header/Footer tab title the report, "Client Retainer Summary."
7. Memorize the report for future use.
Creating a Client Retainer Detail Report
Perform the following steps to create a client retainer detail report:
1. Select the Reports menu and then select Custom Transaction Detail Report.
2. On the Display Tab, set the date range to "All."
3. On the Display Tab and in the Total By field select "Customer."
4. On the Display Tab, add and remove columns until only the following columns show on the report: Date, Memo, Amount and Balance
5. On the Filters Tab, filter by account for the "Client Deposits" account.
6. On the Header/Footer Tab title the report "Client Retainer Detail" and memorize the report for future use.
Tip: When you need to send retainer detail reports to a specific client, filter by customer/job name and email the report to the client.
Options for Clearing Zero Balance Accounts from the Report: The retainer detail report above will show all retainer activity, including retainers that are fully exhausted (i.e. those that have a zero balance). If you prefer, you can reconcile the Client Deposits account using the Bank Reconciliation window. Doing so will not only provide additional accountability for retainer management, it will also give you an additional filter based on cleared status. If you filter the report by cleared status for "Uncleared," all exhausted retainers will drop from the report. There is an alternative option if you do not want to reconcile the Client Retainer account. You can create two Customer Types called "Open" and "Closed." Assign each engagement with the "Open" Customer Type when you create the job and then change to the "Closed" Customer Type when retainers are exhausted (or when the engagement is closed). Then, you can filter the Client Retainer Detail report by Customer Type for "Open." Note: The job status and job type fields do not have the same impact on QuickBooks reports. You cannot use these fields for this purpose.
Setting Billing Rates by Timekeeper, Service and Client
Summary of Billing Rate Functionality: QuickBooks Premier allows you to enter billing rates for each vendor or employee record. For the billing rate you can assign a specific "price" to each item (i.e. service). Then, when you record time for these vendors/employees and pass the time through to an invoice, QuickBooks uses the billing rate rather than the default service item price.
Defining the Problem that Needs a Trick or Workaround: The billing rate works great as long the client is not a factor in calculating the rate. For example, if Sally bills $50 for bookkeeping and $125 for QuickBooks consulting services and she does so for all clients throughout the company there is no issue. However, if Sally bills $50 for bookkeeping for some clients and $45 per hour for other clients you have the following options for QuickBooks to bill the correct amounts automatically:
Option 1: Use Billing Rates in combination with Price Levels. To automatically adjust Sally's rate from $50 to $45 for a specific customer or job, add a Price Level to the customer or job record for a 10% discount. This is effective under some conditions, but this option has the following major limitation: You cannot use Item-Based Price Levels. Only percentage-based price levels will have the correct impact. For example, if set the rate for the Bookkeeping Service Item to $45, QuickBooks will use the Price Level of $45 and exclude the Billing Rate altogether. This would have the correct impact for Sally, QuickBooks would also impose the global $45 price level for all billable professionals. You then have billing rates by customer and service, but not by professional - if multiple professionals within your organization provide that service.
Option 2: Create a Unique Service Item for Each Customer-Specific Rate. In the example above you can create a sub-item of Bookkeeping called "Bookkeeping-[Client Name]-." Then, you can use Item-Based Billing Rate Levels to adjust for specific professionals who provide bookkeeping services. This works fine for billing purposes, but depending on the number of active engagements each year the item list can become very long and cumbersome - especially when entering timesheets. Tip: If you choose this option it is best to use a numerical prefix for each service item and client combination. The concept is similar to departmental tracking on the Chart of Accounts in traditional accounting systems but in this case one truncation of the service item is for the type of service and the other is for the applicable client. If Bookkeeping has the code "01" and the Client has the code "4789," the item name would be "01-4789 - Bookkeeping."
Option 3: Use Virtual Time. There are numerous products on the market that enhance the billing capabilities of QuickBooks. However, if you want to solve for this specific issue without incurring the cost and hassle of separate billing solution, we steer our clients to Virtual Time by Virtual Software. You enter Timesheets into Virtual Time just as you would do in QuickBooks (only with a web portal for timekeepers to optionally enter their own time). Then, you can set billing rates by professional, item and customer. Virtual Time calculates the correct invoice amounts and then pushes the calculations into a QuickBooks Invoice. Virtual Time will also send the time detail to QuickBooks so you can use the time to create Paychecks and to create time reports.
Tracking Employee Receivables (e.g. for Expense Reports)
You have the following options when tracking Employee Advances (e.g. for expense reports).
Option 1: Create an Other Current Asset account called "Employee Advances" and then create sub-accounts for each employee. This is a very simple option that provides turnkey tracking of accounts balances for each employee and requires very little training. However, this option has the following drawbacks: 1) If you use Paychecks to advance money to employees or to receive money back from employees, you will need one Payroll Item for each Employee Advances sub-account. 2) If you have numerous employees an un-collapsed Balance Sheet could be overwhelming with perhaps multiple pages of lines showing the balances in the account. The report would be especially cumbersome when totaled by month or when spanning multiple years.
Option 2: Create an Other Current Asset Account called "Employee Advances" and then use a Custom Transaction Summary Report to get a breakdown by Employee.
Perform the following steps for Option 2:
1. Use Paychecks, Bills or Checks to advance monies to each employee. Make sure to use the Employee record when writing checks or bills for consistency.
2. Create a Custom Transaction Summary Report (date range "All") totaled by Employee and filtered for the Employee Receivable account. Set the Advanced Display options to show only "Non-Zero" rows.
Note: Typically it is best to limit employee transactions to payroll. However, using the employee record on a non-payroll transaction will have no impact on payroll reports or payroll forms and this option allows you to use a single asset account and a single payroll item to track all employee receivable activity.
Adding an Auto-Populating "Professional" Column to Invoices
If you create a class for each professional, you can assign each timesheet entry to the professional's class and then include the "Class" column on invoice templates. Simply rename the "Class" column as "Professional" and the process is complete.
Note: If the company uses classes for some other purpose like locations, you can often make the timekeeper's name the sub-class. If the professional is the sub-class, only the lowest level sub-class will show on the printed invoice.
Additional Benefit: You can also use this class designation to produce a Profit & Loss by Professional with production less human resource costs - on the cash or accrual basis. You can also burden overhead costs to each professional (based on some pre-determined allocation method) to report Net Income by professional. To create this overhead burden you would have to enter periodic journal entries to move the postings from the Overhead or To be Allocated class and into the class for each professional. The journal entry would have no General Ledger impact. You would debit and credit the same accounts but use different classes on each line.
Managing write downs
Using the class to track professionals as described in the section above has another benefit - the ability to track write downs by professional. This trick will not solve for the multi-step process of actually writing off invoices or reducing the balance of an invoice. You would still do that using a Credit Memo (for post-billing write downs) or using discounts (for write-downs during the billing cycle). However, you do get reporting at the quantity level (i.e. hours) and on the Profit & Loss by Class report.
Perform the following steps to track write-downs:
1. Create a class for each professional as described in the section above.
2. Create a sub-class for each professional called "Less Write Downs."
3. Bill the invoice normally - without writing down the number of hours and save the invoice.
4. Return to the invoice and write down the hours to the number you wish to bill. Then, re-save the invoice and print/email for the customer.
5. Repeat Steps 1 through 4 until the billing is complete.
6. Filter an Audit Trail report by transactions type "Invoice" and by Entered/Modified Date for "Today." Then add a Quantity Column. Use the changes in Quantity to measure the write downs for each invoice you created during the billing process in Steps 1 through 4 above.
7. Enter a Credit Memo as described below to record the write down activity by General Ledger account and class. This process will increase Income (to bring the amount back to the amount of income prior to the write-down) and will debit the "Write Off" contra-income account. This process will also allocate write downs to the professional who performed the work and to the client. You will then show write down activity by professional on the P&L by Class and by Job on the P&L by Job.
Perform the following steps to allocate the write down to the correct account, professional, client and service item:
1. Create a Credit Memo template called "Write Down" that shows the Class as a column.
2. Using the "Write Down" Credit Memo form, enter the Service Item for the service you wrote down, the class (sub-class of the professional) and the number of hours you wrote down. Make sure all of the information, including the per hour rate and total, agrees to the Audit Trail report you created in Step 7 above.
3. On the subsequent line, enter the same information, but use the class for the professional (not the sub-class) and enter the quantities as a negative number. The two lines will zero each other. Save the Credit Memo.
The Impact of the Credit Memo on Reports: The Credit Memo will have no impact on the General Ledger because you will debit and credit the same income account. However, the increase in income will post to the Professional's Class record and the reduction in income will post to the Professional's sub-Class record (i.e. write off record). The hourly reporting will have the same impact. You will show that you billed the total number of hours to the Service Item, less the hours you wrote off.
Billing Non-Employee time (e.g. contractor time) - best practices
QuickBooks allows you to record and bill time for employees, vendors and other names. If you have sub-contracted professionals there is only potential problem we need to address with a tip: the default billable status. Employees have a default billable status of "Billable." This works great for the professional service company since billable professionals are many times employees. The default billable status for vendors is "non-billable." As a result, when you enter a time record for a vendor's time you will have to click the "Billable" button on each and every record. If the data entry person is not meticulous, the company could incur loss of revenues.
Tip 1: Create a Vendor Type called "Professional" and then create a time detail report filtered for this Vendor Type. Also filter the report by billable status "Non-Billable." If a sub-contracted professional has non billable time it is usually a data entry error.
Tip 2: Use an Other Name record instead of a Vendor record. Other Name records default to billable for timesheet entries. This solves the problem very well except for Vendor-specific functions like Billing Rate Levels and 1099 Tracking. As a result, few businesses will benefit from this option.
Tip 3: I will again refer you to Virtual Time. That products allows you to control default billable status at the name record level. See "Option 3" on the previous page for more information or go to http://www.virtualsoftware.net.
Posting timesheets to classes and jobs without processing payroll in QuickBooks
If your client uses QuickBooks to process payroll and does so by importing detailed timesheets into Paychecks the client will get very detailed payroll costs by job and class as long as the job and class information is on the timesheets and as long as the payroll preference shown below is set to track job costing, class and item tracking for paycheck expenses by earnings item.
Issue that Requires a Workaround: This works great as long as the client processes payroll in QuickBooks. However, consider the following limitations:
- If the client needs to accrue payroll costs weekly or monthly, the client will have to calculate the accruals by job and class (as applicable) and then enter a very detailed journal entry (with a reversing entry) to record these period-end costs.
- If the client uses a payroll service (e.g. ADP or Paychex) the timesheet detail will never post to the General Ledger - because the client will not import the timesheet detail into paychecks. If the payroll service the client uses provides a download into QuickBooks and if this download includes job and class level detail this may work for the client's job costing and class tracking needs. If this level of detail is not available as a downloadable import file it would be very time consuming (perhaps even cost prohibitive) to use a journal entry to capture this level of detail.
- If the client has both administrative workers and field workers, the client probably needs to record some of the company's payroll tax expense to Cost of Goods Sold and some to Operating Expenses. QuickBooks makes you choose one or the other globally for all employees in the QuickBooks file. If you outsource the payroll processing to a payroll service or process the payroll in a separate QuickBooks file you can use the steps in this workaround to record payroll tax costs to either Cost of Goods Sold or to Operating Expenses depending by employee - or even split a single employee's costs between costs and expenses.
Perform the Following Steps for this Workaround: This workaround allows you to post timesheet detail to jobs and classes (and to service items) for any date range you like - even date ranges that do not correspond to pay periods (e.g. to accrue payroll costs). You can also burden payroll costs with insurance and other costs not captured on the employee's paycheck to get a truer picture of labor costs by job and class.
Perform the following steps for this workaround:
- Create a vendor or other name record for each employee
- Create a COGS type account called Payroll Costs (or some similar wording). If you capitalize work in process you can use your existing WIP asset account instead.
- Create or modify service items so that the item shows both an expense/cost and an income field. In the Expense/Cost field enter the Payroll Costs account or the WIP account.
- In the default cost field, enter the average man hour. If you prefer you can burden the average man hour with indirect costs like worker's compensation insurance. (This is the trickiest part. It may take a few pay periods to tweak the average man hour to the point where your total costs based on the average man hour are relatively the same as the payroll expenses reported by the payroll service plus any expenses like Worker's Compensation you burden into the average. The amount doesn't have to be exactly the same as the payroll expenses recorded by the payroll service. You simply have to be within an immaterial discrepancy.)
- Using the vendor or other name record for your employees and the service items you created or modified in Step 3 above, create a timesheet for each employee. Designate the job and class for each line of the timesheet.
- After the payroll is run (through the payroll service) enter a Journal Entry to record the payroll to the General Ledger. Enter no job or class information during this step. At this point the Trial Balance will reflect the payroll activity for the period. The workaround does not have any impact on the Trial Balance or you will overstate or understate costs.
- Open the Write Checks window. In the bank account field create/use a bank account called "Time to Jobs" or some similar wording. This bank account will never carry a balance.
- Enter the employee's name (vendor or other name record) in the payee field and press TAB. QuickBooks will ask if you want to use timesheet detail when creating the check. Select yes and enter the applicable date range for the timesheet entries you want to use when creating the check.
- QuickBooks populates the Items tab with all of the detail from the paycheck and multiplies the number of hours by the average man hour you established in Step 4 above to arrive at a total. Each total posts to the jobs and classes you used on the timesheet.
- Since you don't want to record a decrease in cash for the "Time to Jobs" bank account and because you don't want to increase costs on the Trial Balance, zero the check using the Expenses tab. Select the "Payroll Costs" account and enter a negative amount to zero the check. The Items tab will make costs go up (debit) and the negative entry on the expenses tab will make costs go down by the same amount (credit). The check amount will zero as well. (Note: If you prefer, you can use a separate account in the Expenses tab. Many people prefer to use a contra expense account - a sub-account of payroll expenses - called "Less Wages in COGS" or some similar wording. This is a better option because the P&L Standard report will show the wages above gross profit just a the P&L by Job and P&L by Class will do. This gives you a consistent financial statement presentation and more accurate gross wages/margins.
About the author:
Joe Woodard is an Advanced Certified QuickBooks ProAdvisor and Intuit Solution Provider who has taught more than 20,000 QuickBooks consultants across the country. Joe works with Intuit, state CPA societies, and Atlanta-area CPA firms to present advanced QuickBooks instruction to accounting professionals and software consultants. Joe has earned a unique relationship with Intuit as a trainer, consultant, and author. Joe has built two successful accounting software consulting practices: the first in New Orleans, and the second in Atlanta - Creative Financial Software (CFS). In addition to consulting with small businesses, CFS provides advisory services to CPA firms and other QuickBooks ProAdvisors/Intuit Solution Providers across the country - helping them to better service their clients who use Intuit products. Joe recently hosted the first annual Scaling New Heights QuickBooks conference in Atlanta.