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.
Contractors switching to QuickBooks from another accounting program are often concerned about how to enter Job to Date Costs and Billing Data when converting, so they don’t loose this vital information.
Below is a procedure that I found several years ago, unfortunately I don’t remember where I found it or who wrote it (otherwise I would be happy to give them credit for it), but I do want to share it with you; as it is one way to handle this task.
Use this procedure to enter Job-to-date (“JTD”) costs and JTD billing so the Job Profitability Summary is accurate after converting your existing accounting software to QuickBooks, when you convert your existing QuickBooks file to a new database file, or after archiving old QuickBooks transactions.
Run your Job Profitability Summary Report to obtain the JTD 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. If your cut-off date is 12/31/08, for example, use the next day (1/1/09) as the date for all the transactions below.
- 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 • Conversion – 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 Billing” and link it to “9998 • Conversion – JTD billing”. You will make this inactive after completing this procedure.
To enter the JTD Costs for each job:
Enter a Bill to the “Opening Balance” vendor for the JTD cost. On the expenses tab select the appropriate Customer/Job, and select “9999 • Conversion – JTD Costs” for the account.
Enter an AP Bill Credit to the “Opening Balance” vendor for the same amount, but do not select the Customer/Job. On the expenses tab, select 9999 • Conversion – JTD Costs.
In Pay Bills, select the bill, click Set Credits, and apply the credit against the bill.
This effectively removes any AP balance, leaves no effect on GL, but leaves the cost in Job Cost.
Create a new Bill and AP Bill Credit 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. Use the new service Item called “Opening Balance JTD Billing”.
Enter a Journal Entry for the total amount, 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 see the above Journal Entry is available as a credit against the Invoice you created above.
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 and Journal Entry for each active job as of the conversion date.
Run your Job Profitability Summary Report 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.
The Balance Sheet by Class Report is new in QuickBooks 2011 and it gives users the option of selecting “Classes” (fund, location, profit center, or other category) as their column/class grouping.
Over the last several days, I’ve been discussing and sharing some information about how we all will need to change our data entry procedures in order to utilize the Balance Sheet by Class Report available in QuickBooks 2011; little did I realize when I started delving into the specifics of this report, that making it work would be so complex.
Previous QuickBooks 2011 – New Balance Sheet by Class Report articles include:
- Part 1, we touched briefly on the fact that transactions will have to be entered in a very specific manner and there are many data entry transactions that are not supported by the Balance Sheet by Class Report
- Part 2, we discussed how accounting professionals and end users would need to change their procedures when creating journal entries so that they were balanced
- Part 3, we discussed how users and accounting professionals would no longer be able to assign multiple classes to a single paycheck.
- Part 4, we discussed how you would need to classify Payroll Liability Payments in order for them to be appropriately recognized on the final report.
- Part 5, we discussed how you need to classify Sales Tax Liability Payments using a Journal Entry AFTER you actually make the payment.
- Part 6, we discussed the effect of handling customer prepayments when using the Receive Payments window.
- Part 7 – we talk about invoices with multiple classes and how offering customer discounts in the Receive Payments window would cause discrepancies between the Profit & Loss by Class and the Balance Sheet by Class reports.
In this article, we’ll talk about how entering a single Vendor bill with multiple classes and taking advantage of vendor discounts in the Pay Bills window causes discrepancies between the Profit & Loss by Class and the Balance Sheet by Class reports and how to correct this.
Many business owners make it a policy to take advantage of any early payment discounts offered by their vendors; after all in this tough economy every penny counts! However, paying and taking an early payment discount for a vendor bill that has multiple classes causes a difference in net income between the Balance Sheet by Class and the Profit & Loss by Class reports.
Entering the bill itself doesn’t cause an issue on either report and is recorded correctly on both.
Right click on the image to enlarge it.
- Right click on the image to enlarge it.
Paying the bill and actually taking the discount in the Pay Bills window causes the problem because the discount can only be assigned to a single class.
Right click on the image to enlarge it.
The solution to this discrepancy is to enter a bill for each class, even if the vendor sends you a single bill.
Right click on the image to enlarge it.
That way when you pay each bill individually and take the discount, you can assign the discount to the appropriate class.
Right click on the image to enlarge it.
You can then pay the invoices using a single bill payment check IF you select each individual bill and apply the discount with the appropriate class assigned to it.
Right click on the image to enlarge it.
When handled in this fashion, the Profit & Loss by Class accurately reflects your expense and the amount of the discount taken for each class.
Right click on the image to enlarge it.
While Net Income is accurately reflected and classed on both the Profit & Loss by Class and the Balance Sheet by Class reports.
Right click on the image to enlarge it.
Accounts Payable personnel who work for business owner’s who make it a practice to take advantage of vendor early payment discounts will need to put in place new procedures and protocols that include the requirement of entering vendor bills that are internally assigned to different classes individually. This will cause additional data entry time.
Additional procedures will include a method of tracking class that is assigned to each bill so that the same class can be assigned to the discount.
One way to track this is to enter the Class in the Memo field of each bill and then starting with a standard Unpaid Bills Detail report (Reports menu -> Vendors & Payables -> Unpaid Bills Detail) OR a Vendor Balance Detail (Reports menu -> Vendors & Payables -> Vendor Balance Detail). Once either of these reports are displayed, click the Modify Report button and on the Display tab in the Columns section choose (check) Memo, Terms, and Class so that you have record of this information.
NOTE: You must enter the Class information in the Memo field for each bill, because even though both of these reports offer to display Class information, the class information that is entered in the Bills windows does not flow through into these reports. Personally, I feel this is a bug in Intuit’s code and will report it as such.
Right click on the image to enlarge it.
Once you are satisfied with the information being displayed in these reports, Memorize them so that you do not have to customize them each time you use them.
As with the other special requirements required to ensure the accuracy of the Balance Sheet by Class report, make sure that you inform your tax preparer so that any year end journal entries they make do not negatively effect the accuracy of this report.
 |
Request our FREE 142-page “What’s New in QuickBooks 2011? eBook, by completing a simple request form.
This eBook will provide you with with all the information I’ve posted here in our blog, plus MORE! Once you’ve completed our simple request form, you’ll have instant access to this 142-page .pdf eBook, designed to be duplex printed and put in a binder for future reference. |
Progress Invoicing is a feature of QuickBooks® Pro, Premier and Enterprise editions, however, it is geared toward line item billing; more commonly used by contractors who work on large commercial and/or government construction projects involving a large scope of work or schedule of values and detailed line-item billing.
Any contractor can run into a customer billing issue when attempting to utilize detailed job costing in conjunction with QuickBooks Progress Invoicing.
A contractor will have to enter detailed information about labor, materials, subcontracts and other costs in the Estimate or Schedule of Values form in order to get an accurate Estimate vs. Actual report from QuickBooks.
An issue often will arise when the contractor bills his customer using a percentage of a Lump Sum Draw, rather than a percentage of completion for each line item.
For example, you submit a detailed Estimate to a customer for the installation of some wrought iron fencing around his property. The Estimate details amounts for Site Work, the fencing, installation of the fencing, and some excavation, providing a grand total for the project.
Right click to enlarge image.
However, your contract terms are 20% of the total contract price upon acceptance of the Estimate, 40% when the project is 40% complete, and the remaining 40% upon completion of the project.
Most times a contractor in this type of situation will prefer to have his Progress Invoice display a single line item for the fence installation as a single lump sum scheduled/estimated value with current amount being billed along with previous amounts or percentages that were billed.
Right click on the image to enlarge it.
Because of their contract terms, the contractor does not want or need to show all of the line items on the progress invoice but does want to track the detail for job costing – this causes a conflict between what the contractor wants to see vs. what he wants his customer to see.
One solution to this problem would be to create a Custom Invoice Template in QuickBooks that would eliminate all of the columns except for the Description column and each time you created an invoice you would end up manually typing in the description (20% Draw, 40% Draw, etc.). The drawback to this solution is that you just eliminated the BEST feature of the Progress Invoicing function – no duplicate or redundant data entry; and the invoice ends up looking unprofessional.
The best solution, in my opinion, is to create a Group Item that includes the details of your job costing needs (up to 20 individual items can be added to a single group). A Group Item allows you to have a single description you can indicate that you DO NOT want to print the items within the group by simply by leaving the Print items in group option unchecked.
Right click image to enlarge
Some people might feel that they would need to set up a new group item for every group of items that you use. If you always use the same combination of items on every job (with a few extra items here and there) such as Labor, Materials, Subcontracts, and Other Costs, you would create a “standard or master billing group” containing these items. When you encounter that job that has extra items – perhaps equipment rental – you can actually add an item for equipment rental to the group directly on the Estimate form. Once your group item has been pulled into the form, click into the group item (make sure that you are not above or below it), from the QuickBooks Edit menu -> choose Insert Line to insert a blank line, then from the item column access your item list and select the new item to be inserted. You can also use the Edit menu -> Delete line to remove items that will not be used for this specific job.
Progress Invoicing (also called progress billing, percentage of completion billing, or partial billing) involves billing from an estimate (or Schedule of Values) over the course of time and could be considered a type of installment billing. Progress billing is commonly associated with the construction industry; however, it is common in other industries as well.
QuickBooks® Pro, Premier, and Enterprise editions all offer several options for invoicing and a Progress Invoicing function is also available.
Some projects or jobs will require only a single invoice, others will require two invoices, others may require three invoices (one at the start of the job, one when you are 50% done, and the other when you are finished), other projects might require that you submit monthly progress invoices over the course of months or even years.
Typically, if you take on a job that you will have finished at the end of 30 days, you might only want to create a single invoice, unless retention or retainage is held for some reason, in which case you will have to create two invoices.
When your scope of work on the project will span one or more months, then you will want to consider progress billing. When you generate progress billings, you will bill ONLY for the work that you have completed and the materials that you have used or installed – occasionally you will also be allowed to bill for materials that you have at the jobsite but have not yet used.
If you are a subcontractor, sometimes the General Contractor will have a form that they insist you use (instead of your own invoice form), other times they may insist that you submit your progress billing on an industry standard billing form – such as the AIA G-702 & G-703 forms developed by the American Institute of Architects – or similar plain paper forms containing the same information.
Make sure that you submit your billings on the forms that they want or they will probably reject your invoice; mail it back to you and tell you to resubmit your billing CORRECTLY. Usually having to resubmit a bill means that you have to wait until next month to submit the corrected bill.
Always try to get your invoice paperwork right the first time and include any proper releases that are required. Sometimes the General Contractor will hold up everyone’s invoices —-even if only one contractor has submitted their billing incorrectly.
