If you use QuickBooks for your accounting and you create your Estimates in Excel instead of within QuickBooks you loose out on job costing capabilities and so much more!
I often see and hear comments such as this as I browse the web or talk with potential customers for our software:
Right now we are using QuickBooks Contractor. It is ok for our accounting software but we are looking for something that works as an overall system for our office – CRM, estimating, job costing, proposals, work tickets and most importantly easy for my guys to use. Right now we are using Excel for Estimates and invoices.
When I see and hear comments like this I get so confused – because QuickBooks does allow you to create Estimates, Proposals, and do Job Costing - so I thought I’d attempt to write a short article to address this.
Creating Estimates in Excel vs. QuickBooks – Pros and Cons:
Excel is REALLY easy to use – anyone can quickly whip up an Estimate, Proposal or a Work Order., you can create some great looking documents that you are proud to hand to your prospective customer. But that is about as far as you can go.
QuickBooks, on the other hand, if set up properly will allow you to create an Estimate, Proposal, Work Order, or an Invoice and perform job costing just by entering vendor bills, writing checks, entering credit card charges, and employee payroll. You then can access all kinds of Job Costing reports and an Actual vs. Estimate Report – which allows you to see the difference between what you thought you’d make on the job vs. what you actually made.
But….QuickBooks requires more work on the front end and then proper training for your employees.
I think I just heard everyone groan! There is no way that I can write a “short” article on the benefits of using QuickBooks to prepare these documents for your business, but I can provide you with some suggestions to get started.
- Is the QuickBooks Estimate function turned on? If not go to the Edit menu -> Preferences -> Jobs & Estimates -> Company Preferences tab -> set “Do You Create Estimates” AND “Do You Do Progress Invoicing” to Yes
- Modify/Customize the built in forms to meet the needs of your company using the Layout Designer, by downloading a pre-built template or by using the Create Form Design option. All of these options are available from the Lists menu -> Templates -> and then clicking the Template button at the bottom of the window.
- Create a FULL list of the services, cost codes and/or products your company uses for billing
- Have a meeting – get EVERYONE involved – take that master list of products and/or services/cost codes and come up with descriptions that EVERYONE can agree on {letting them know that they can “tweak” it more to their liking later}
- Go to the Items List and set up items that correspond to that master list of of the things your company does, creating Group Items or Inventory Assemblies if appropriate
- Create an Estimate, a Proposal, or a Work Order using the entries in your Item List – print it out and see what everyone things of it. If they are happy teach your Estimators how to use the Estimating function
- When entering Vendor bills, writing checks, or entering Credit Card Charges use the Items tab and assign the cost to the job
- Turn on time tracking, enter employee hours by Job, Service Item, and Payroll Item
- Create a Progress Invoice from an Estimate for the current amount that you are billing the customer
- Look at the Estimate vs Actual Report for a specific job and look at the report results
These suggestions are just the tip of the iceburg for taking QuickBooks from plain-Jane accounting to job-costing accounting. Use the search box on our site and enter specific keywords for additional tips.
If you can’t get your Estimators to leave Excel, there is a 3rd party application Transaction Pro Importer – that will import your Excel based Estimate into QuickBooks for you and you can implement some of the other suggestions in this article.
I hope you’ve found this article to be helpful, if so please take a moment to leave a comment or ask a question – or share it on your favorite social networking site if you think others would find it to be useful.
A QuickBooks tip for entering Job-to-data costs and billing data at the item level when converting or archiving.
Use this procedure to enter Job-to-date (“JTD”) costs and JTD billing so the Job Profitability Detail Report is accurate at the Item level after converting your existing Quickbooks file to a new database file, or after archiving old QuickBooks transactions. In both cases, you are bringing over beginning balances as of the conversion date into your new data file, and this procedure will prove useful for reporting on jobs that are in progress as of the conversion date.
From your old data file:
Run your Job Profitability Detail Reports for each job in progress to obtain the JTD cost and revenue figures as of the conversion date. In the date fields, leave the first field blank, enter the conversion date in the second date field. You will use the figures on this report for your data entry in the new data file.
In the new data file:
- Create a fake Vendor called “Opening Balance”. You will make this inactive after completing this procedure.
- Create an Other Expense account in the Chart of Accounts called “9999-Conversions-JTD Costs”. You will make this inactive after completing this procedure.
- Create an Other Income account in the Chart of Accounts called “9998 · Conversion – JTD billing”. You will make this inactive after completing this procedure.
- Create a Service Item called “Opening balance JTD Costs” and link it to “9999 · Conversion – JTD Costs”. You will make this inactive after completing this procedure.
- Print your Item List. (Reports>List>Item Listing) Modify the report to display columns for Account and COGS Account; remove price, cost, tax code, etc.
- Look at your Item list and determine which Service Items appear on your Job Profitability Detail Reports. These are the items to edit in step 9. These are active items.
- In the Item List window, make all other Service Items temporarily inactive. Making them inactive will keep them from appearing in the Add/Edit Multiple Items screen allowing you to quickly copy down the account edits you will perform in step 9.
- Create a backup before performing step 9!
- Using the Add/Edit Multiple Items screen (available in QB 2010 and later) ,edit “all active service items” and point the COGS/Expense account to account 9999 and point the Account/Income account to 9998. Change the top item, and quickly copy down the account edits to the other items. Click Save. Do not update existing transactions. You will have to answer No repeatedly by pressing N until it scrolls through all your changes.
- When you are done with the following procedures, you will edit these same Items again, and change the COGS/Expense and Account/Income accounts back to what they were. Do not update existing transactions. You will have to answer No repeatedly by pressing N until it scrolls through all your changes.
To enter the JTD Costs for each item on each job:
Enter a zero dollar Bill to the “Opening Balance” vendor for the JTD cost. On the item tab enter each Item and the costs on the job profitability detail report and the appropriate Customer/Job. Keep the Billable box checked. On the last line, enter a negative number to the “Opening balance JTD Costs” Item with no customer:job.
This results in no AP balance, leaves no effect on GL, but leaves the correct JTD cost by Item in your Job Cost reports.
Create a new zero-dollar Bill for each active job as of the conversion date.
To enter the JTD Billing for each job:
Enter an Invoice for the Customer:Job for the total Billing-to-date on the job. The Add Time/Costs box will pop up, select the Item tab and bring over all Items to the invoice. You now only have to edit the dollar amounts to agree with your JTD Billing on your Job Profitability Detail report.
Enter a Journal Entry for the total amount of the JTD Billing on the job. The debit is to the “9998 · Conversion – JTD billing” account, leave the Name field blank. The credit is to Accounts Receivable. Select the Customer/Job name in the Name field.
In Receive Payments, enter the Customer/Job, and you will find the above Journal Entry is available as a credit against the Invoice you created above. Apply the Journal Entry to the Invoice.
The net effect on GL is zero, the invoice is paid, and the correct Job-To-Date Billing as of the conversion date will be reflected in your Job Cost reports.
Create a new Invoice, Journal Entry and Receive Payment entry for each active job as of the conversion date.
Run your Job Profitability Detail Reports on the new data file and compare with the same report on the old data file. They should agree. If not, you’ll need to double check your data entry.
I hope you find this QuickBooks tip to be helpful. If so, please leave a comment or share it on your favorite social media platform by using the buttons below.
A QuickBooks tip for creating a job cost report that displays hours worked and payroll/labor burden costs.
Creating a job cost report displaying the hours worked by employee, on a specific job or on all jobs, for a specific week or at the end of a job which includes payroll/labor burden costs can provide a contractor with vital job costing information so you can see if you correctly bid the number of man hours and payroll costs required on a job.
NOTE: It is highly recommended that you start with a single pay period AND a single paycheck to ensure that your report is correct. Once the report is correct, memorize it and you can then either finish your payroll or change the dates to better suite your needs.
- From the Reports menu -> choose Custom Transaction Detail Report (the Modify Report:Custom Transaction Detail Report window may or may not automatically appear; if it does not, click the Modify Report button)
- On the Display tab, navigate to the Columns section and using the scroll bar on the right of the window select (click) only the following options – Date, Num, Name, Source Name, Item, Payroll Item, Qty, Sales Price, Amount and Balance. All other items in this list that were preselected should be unchecked.
- Click on the Filters tab
- in the Choose Filter block, click on Transaction Type -> from the Transaction Type drop down menu select Paychecks
- still in the Choose Filter block, click on Account -> from the Account drop down menu -> select Multiple Accounts -> and click on each of your Payroll Expense or Payroll Cost of Goods Sold Payroll Costs accounts.
- Click the OK button.
Print your Report – but do not close it yet:
Right click on the image to enlarge it
Compare the report to the employees paycheck – making sure that ALL of the amounts from the Company Summary section of the Paycheck Detail are included in your report.
Right click on the image to enlarge it.
If all of your costs are included, return to the report, click the Modify Report button -> click on the Header/Footer tab -> change the Report Title to Job Cost Report with Hours & Payroll/Labor Burden Costs -> click OK. Next click the Memorize button and save it.
You can now pull up the memorized report at any time and make further modifications for a specific date range or even a specific job.
To run the memorized report for a specific job:
- From the Reports menu -> choose Memorized Reports -> select your report
- Click the Modify Report button -> click the Filters tab -> from the Filters box -> click on Name -> from the Name dropdown menu select just the specific job
- From the Dates drop down menu -> select All
- Click the Refresh button
This will produce a report just for this specific job and include all payroll/labor burden costs to date. This is a valuable report to run at the end of a job.
We hope you found this QuickBooks tip for creating a job cost report which included hours and payroll/labor burden costs helpful. If so, please take a moment of your time to leave a comment.
Author’s Note: The idea for this post originally came from Ruth Perryman – however, it has been modified to include additional instructions and details.
A QuickBooks tip for creating a job cost report that displays hours worked.
Creating a job cost report displaying the hours worked by employee, on a specific job or on all jobs, for a specific week or at the end of a job can provide a contractor with vital job costing information so you can see if you correctly bid the number of man hours required on a job.
Luckily, QuickBooks will provide you with this information through the Time by Job Summary or Time by Job Detail Reports, this article will teach you how to modify the reports for your business needs, however, in order for these reports to be useful you must enter employee hours using the QuickBooks Enter Time function.
The Time by Job Summary Report
- From the Reports menu
- Choose Jobs, Time & Mileage
- Time by Job Summary
- From the Dates menu, scroll up to select ALL
This initial report will provide you with summarized information about the total number of hours worked for each QuickBooks Item/Cost Code/or section totaled by job.
Right click on the image to enlarge it
While this can provide you with useful information, perhaps you would like to filter the report to include the total time spent on a single, completed job, by cost code.
- Click the Modify Report button at the upper right – above the Dates option
- From the Modify Report: Time by Job Summary window, click on the Filters tab
- From the Choose Filter box, click on Customer:Job
- From the Customer:Job drop down menu (where it currently says All customers/jobs), select Multiple customers/jobs
- From the Select Customer:Job window, scroll through the list of Customers and Jobs until you see the job that you want and click on it to select it.
Right click on the image to enlarge it
- Click the OK button, twice
- You now have a time summary report for just this single job.
The Time by Job Detail Report
- From the Reports menu
- Choose Jobs, Time & Mileage
- Time by Job Detail Report
This initial report will provide you with detailed information about the total number of hours worked by each employee under each QuickBooks Item/Cost Code/or section; subtotaled by each cost code and totaled by job.
Right click on the image to enlarge it
While this can provide you with useful information, perhaps you would like to filter the report to include the total time spent on a single, completed job, by employee and cost code.
- Click the Modify Report button at the upper right – above the Dates option
- On the Dates tab from the Dates drop down menu, scroll up to select All, you can also add or remove columns to display in the report for Payroll Item, Class, Notes and WC Code.
- Click on the Filters tab
- From the Choose Filter box, click on Customer:Job
- From the Customer:Job drop down menu (where it currently says All customers/jobs), select Multiple customers/jobs
- From the Select Customer:Job window, scroll through the list of Customers and Jobs until you see the job that you want and click on it to select it.
Right click on the image to enlarge it
- Click the OK button, twice
- You now have a time by job detail report for just this single job.
Right click on the image to enlarge it
These reports will help you to see how accurately you bid your man hours on a job, however, they will NOT include payroll dollars. You could export the modified time by job detail report to Excel, add a column for Rate (the rate being the hourly wage PLUS the hourly labor burden), add some simple formulas to the spreadsheet and also get the total cost.
NOTE: Always check the Time by Job Detail Report for a No Item Assigned section, hours that appear in this section have been assigned to a job, but not assigned to a QuickBooks item or cost code and this will make your job costing reports inaccurate.
Right click on the image to enlarge it
You can correct the job costing reports by pulling the original hard copy timesheets and double click on each entry (which will take you to the time entry field) and you can assign the correct cost code. You will also need to go to the paycheck detail and add the cost code there as well.
We highly recommend that you run the Time by Job Detail Report each week after entering employee hours, but BEFORE you issue payroll to make sure that everything has been correctly costed to your jobs.
We hope you’ve found this post helpful – if so please leave a comment.
QuickBooks Payroll, when properly set up, is capable of tracking and including the cost of your General Liability Insurance; as well as many of the other things that costly construction software does automatically – with a little more effort on your part and without the big price tag.
Tracking General Liability Insurance, when it is based on gross payroll, and getting those costs into Job Costing Reports is vital for many businesses, especially the construction industry.
NOTE: The best time to implement this procedure is when your General Liability Insurance Policy period starts.
The following instructions will allow you to track your General Liability Insurance costs when it is based on gross payroll and get those costs into your job costing reports without making complex journal entries. It will also help you be aware of how much your premium payment will be so that you aren’t in for an unwelcome surprise when the bill comes in or your policy is audited.
QuickBooks Setup for accruing the cost of General Liability Insurance
Example:
In our example general liability insurance is calculated at $6.36 per $1,000.00 in wages for field workers – realize that each type of work that you perform could very well have a different experience rate (just like Worker’s Comp).
Step 1:
Come up with a rate per $100 in wages so this can be calculated for each employee with each paycheck, if paychecks are usually less than $1,000.00 per employee per week.
- $1,000.00 divided by 10 = $100.00
- $6.36 divided by 10 = $0.636
If your policy has different experience rates for different work or employee classifications you’ll want to determine this cost for each different rate.
Step 2:
Create an Other Current Liability Account on your Chart of Accounts to track your Accrued General Liability Insurance. From the Lists menu –> choose Chart of Accounts –> click the Account button at the lower left –> click New –> select the radio button next to Other Account Types and choose Other Current Liability from the drop down menu.
Right click on the image to elarge it.
Click the Continue button and add the details for the account.
Right click on the image to elarge it.
Click Save & Close.
Step 3:
Create the Cost of Good Sold and/or Overhead accounts to track the expense to the company. A Cost of Goods Sold account would be used for field workers and an Expense Account for Overhead and Office workers. From the Lists menu –> choose Chart of Accounts –> click the Account button at the lower left –> click New –> select the radio button next to Other Account Types and choose Cost of Goods Sold from the drop down menu – OR – click the radio button next to Expense.
Right click on the image to elarge it.
Click Save & Close.
Step 4:
Create Company Contribution Payroll Items to track the costs while running payroll. From the Lists menu –> choose Payroll Item List –> click the Payroll Item button at the lower left –> choose New –> select Custom Setup –> click Next –> click Company Contribution –> click Next –> type in the name for this item and select the Track Expenses by Job option –> click Next –> choose your General Liability Insurance Carrier –> from the Liability Account drop down select the account you created in Step 2 –> from the Expense account drop down select the account you created in Step 3.
Right click on the image to elarge it.
Click Next –> Tax tracking type should be set to None –> click Next –> Taxes window should have no tax types checked –> click Next –> Calculate based on quantity window select the radio button to Calculate this item based on quantity –> click Next –> Default rate and limit window, enter the amount that you calculated in Step 1 and make sure that the This is an annual limit option is NOT checked. Click Finish.
Step 5:
Add the company contribution item(s) to Employee Defaults so all new employees who are hired will automatically have this item automatically included in their employee records.
From the Edit menu –> choose Preferences –> select Payroll & Employees –> click on the Company Preferences tab –> click the Employee Defaults button –> click into the Item Name column of the Additions, Deductions and Company Contribution section and select the item(s) you created in Step 4.
Step 6:
Add the company contribution item(s) to existing Employee Records in order to calculate your accrued liability when processing payroll.
From the Employee Center, edit each employee record going to the Payroll & Compensation tab –> click into the Item Name column of the Additions, Deductions and Company Contribution section and select the item(s) you created in Step 4.
Calculating General Liability Insurance Costs When Running Payroll
When running payroll you’ll want to open (view) the detail of each employees paycheck –> determine gross payroll ($280.00 + $1,120.00 = $1,400.00 – per the sample paycheck below) –> take total gross and divide it by 100 (1,400.00 divided by 100 = 14) –> enter 14 in the Quantity column next to the company contribution item and click Enter. QuickBooks will then calculate the General Liability Insurance for this employee and display that amount in the Company Summary section.
Right click on the image to elarge it.
By implementing and following this procedure your General Liability Insurance will be included in your Payroll Summary Reports, Profit & Loss, and Profit & Loss by Job Reports. Additionally, your accrued liability will be displayed on Balance Sheet Reports and can be viewed at any time simply by viewing your Chart of Accounts List.
One final note; when it’s time to pay your General Liability Insurance policy premium you will cut the check using the payroll Pay Liabilities function – DO NOT USE the Write Checks feature.
