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.
Freebie Friday! The work week is just about over (for some of us, that is).
This week’s Freebie is an AIA (G-702/G-703) Billing Training exercise.
Have you ever been asked to complete a G-702 and G-703 billing form and don’t know where to even start? If so, than this training exercise is just what the doctor ordered!
Most anyone will tell you that completing the AIA G-702 and G-703 forms by hand is frustrating and difficult. To fill out one correctly, you pretty much have to:
- forget about any basic accounting principals you’ve ever learned
- ignore change orders that have been submitted or approved, but not processed
- ignore money that is still outstanding from previously submitted applications for payment
You need to realize that the payment applications that you submit are an important piece of your overall cash flow management. When your payment applications get screwed up — bad things happen; none of which put you in “good standing” with the General Contractor or Project Owner, and can ultimately damage your company’s overall cash position.
The point is – you have NO choice except to learn how to fill out the AIA forms correctly!
This training file (zip file) is to be used in conjunction with the “What Is AIA Billing” page on our website, so you’ll want to go and print that page before you request the training exercise. Print What Is AIA Billing now, opens in a new window for your convenience.
IMPORTANT NOTE: To avoid confusion the instructions contained in this articles are instructions for MANUALLY completing an AIA Billing form and HAVE NOTHING TO DO WITH THE USE OF OUR AIA BILLING SOFTWARE.
In order to obtain our free AIA Billing form, we’ll ask you to complete a short request form and then you will have instant access to download the zip file that contains the training exercise.
Request your training exercise here.
Have a great weekend everyone 
P.S. If you’d like to automate your AIA Billing task AND you use QuickBooks; click the green “Free 30 day trial” button on the left menu. Take our Construction Application for Payment Solution (CAPS) program for a test drive — it sure the heck beats filling these out by hand!
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.
