An “audit trail” for Accounts Receivable in QuickBooks is only one of the many complexities faced by government construction contractors. Running any sort of construction business can be difficult – but the amount of paperwork, the level of detail, and the additional requirements for a government contractor can cause 2 and sometimes 3 times the amount for a bookkeeper. Below is a question submitted by a reader named Shirley.
We are a Construction Company and do government contracting. Is it possible to use the Accounts Receivable Summary to track each Project and the invoices when received and when paid. It has been several years since I have used QuickBooks and have never used the Construction Version. We have QuickBooks 2011 Premier Construction.
————————————————————————————-
First, let me say that the Contractor version doesn’t work any differently than the Pro – it just has some built-in construction specific reports and other features that aren’t available in other versions. So your learning curve shouldn’t be difficult at all! That’s the good news 
As for using the Accounts Receivable Summary Report as an audit trail, personally I don’t think I would use that because it wouldn’t give me the detail that I would want – OR – that I “think” you are looking for. The A/R Summary report only shows you how much {total} money is overdue in 30, 60, and 90 day increments. This information may meet your needs if you just need to know how much money is how many days overdue by job.
Right click on the image to enlarge it
My own personal preference, would be to know which invoices were how many days past due; therefore, I would rather run the Accounts Receivable Aging Detail to determine exactly which invoices where outstanding.
Right click on the image to enlarge it
Both of these reports would provide me with information about just a specific job – each could be modified and filtered for a specific job or jobs.
Another very good report, especially if you are concerned about a specific job, is the Customer Open Balance Report {available from the actual QuickBooks job record and choosing Open Balance}.
Another alternative to obtain this information easily, without running and printing reports – would be to customize how job record information is displayed in QuickBook, that way anyone who has access to this type of information can easily see it without having to run, print, and then distribute reports. Remember, it’s all about efficiency and streamlining the workload/work flow. Customizing how information is displayed in the job record let’s you quickly see how much total money is outstanding for a specific job, when invoices were created {dated}, the date you anticipate receiving the money, how many days outstanding the money is, what the invoice was issued for and the open balance.
Right click on the image to enlarge it.
For tracking Vendor costs you could run a Job Cost by Job and Vendor Detail, click the Modify Report button and customize it like the screenshot below:
Right click on the image to enlarge it
QuickBooks can do many things, including provide you with an “audit trail” for Accounts Receivable and Accounts Payable. You just have to get in there an poke around and see what works for you.
While the information provided about may not answer the original question to the 100% satisfaction of the person asking it, based on the information provided it should at least provide you with things to look at and modify to best meet your needs.
If you found this tip to be helpful, please take a moment to leave a comment or share it on your favorite social network.
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.
Tracking employee advance or loan repayments can easily become part of your normal QuickBooks payroll function and is a must for companies that do have a policy that allows giving employees advances/loans for personal reasons.
Earlier this week, in our post QuickBooks Payroll Tip – Tracking Employee Advances or Loans, we discussed three different ways in which to record an advance or loan that was given to an employee. Today we will discuss how to track repayment of that loan through an agreed upon payroll deduction that will continue until the loan is paid in full by the employee.
Create a payroll deduction item to record the loan repayment
If your payroll item list doesn’t currently have a payroll deduction item to record loan repayments, you will need to add one.
- From the Lists menu -> choose Payroll Item List
- Click the Payroll Item button (lower left of the window) -> choose New
- Select the radio button next to Custom Setup -> and then click the Next button
- Select the radio button next to Deduction -> then click the Next button
- On the Name used in paychecks and payroll reports window -> enter the name of the deduction item (I recommend using the loan date and employee name) -> click the Next button
- On the Agency for employee-paid liability window you don’t need to enter an agency name since the money will be going to your own company -> from the Liability Account (employee-paid) drop down menu you want to select the Other Current Asset account that you created to record the loan – do not select an actual Payroll Liability account here -> click Next
- On the Tax Tracking Type window -> select None -> click Continue
- On the Taxes window -> no tax items should have a check mark next to them -> click Next
- On the Calculate based on Quantity window -> select the radio button for Neither -> click Next
- On the Gross vs. net window -> select the radio button for gross pay -> click Next
- On the Default rate and limit window -> both should be set to 0.00 -> uncheck the This is an annual limit option -> click Finish
Add the loan repayment deduction item to the employee record
- From the Employee Center -> Employees tab -> select and edit the employees record -> Payroll and Compensation Info
- Click into the first blank row of the Item column of the Additions, Deductions and Company Contributions section -> from the drop down list -> select the loan repayment item -> in the Amount column enter the agreed upon weekly amount that the employee wants deducted from his paychecks -> in the Limit column enter the total amount that the employee was loaned
Right click on the image to enlarge it
ALTERNATIVE METHOD
Sometimes an employee will opt to repay the loan with cash or a personal check by some agreed upon date in the future, instead of through a payroll deduction. In this situation, when the employee gives you his payment – either in full or partially – you will record the receipt through the Record/Make Deposits window. Select the Vendor/Other Name List entry in the Received From column -> in the From Account drop down, choose the Other Current Asset Employee Loan Receivable account -> enter other details and proceed with the deposit as usual.
Right click on the image to enlarge it.
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.
When you use the EasyStep Interview to create a new QuickBooks company file, you will be asked questions about the type of business you own as it walks you through the process of setting up your business in QuickBooks. Your answers will be used to help you get started quickly , by setting up the appropriate accounts and lists. It will take you about 30 minutes to go through the EasyStep Interview.
Creating a new company file in QuickBooks is easy and you can access the new file wizard in many ways.
|
 |
Any of these actions will launch the EasyStep Interview, which will walk you through the creation of you new company data file by asking you a series of questions about the type of business you own. It uses your answers to get you started quickly, by setting up the appropriate accounts and lists.
The following is a list of the accounts that QuickBooks creates automatically, if you selected the Construction General Contractor business type in the EasyStep Interview and indicated that your company is an S-Corporation.
- Accounts Receivable – Created during the EasyStep Interview, or the first time you create an invoice.
- Inventory Asset – When the first inventory item is created in a company data file, QuickBooks creates the Inventory Asset account.
- Construction in Progress – Automatically creates this Other Current Asset account when you select the Construction General Contractor business type in Step 2 of the EasyStep Interview. While the account is automatically created, QuickBooks does not have a built-in or automatic method of tracking Construction in Progress.
- Retainage Receivable – This account is created when you select the Construction General Contractor business type in Step 2 of the EasyStep Interview. While the account is automatically created, QuickBooks does not have a built-in or automatic method of tracking Retainage Receivable.
- Undeposited Funds – An Other Current Asset account used to you record a payment from an invoice or a sales receipt. QuickBooks uses this account to hold money you’ve collected until you deposit it in a bank account.
- Accumulated Depreciation – A Fixed Asset account used to hold depreciation information that relates to furniture, fixtures, and equipment that your company owns.
- Furniture and Equipment – A Fixed Asset used to hold information about the furniture, fixtures and equipment your company owns.
- Accounts Payable – Used to hold information about how much money you owe your vendors and subcontractors.
- *Payroll Liabilities – QuickBooks adds this account to the chart of accounts automatically when you turn on the payroll feature in a company file. QuickBooks initially maps all payroll items that create liabilities to this account.
- Sales Tax Payable – Tracks sales tax that you have charge to your customers and owe to the government; it’s created when you turn on the sales tax feature or you indicate that you charge sales tax in Step 10 of the EasyStep Interview.
- Capital Stock – This Equity account is automatically created based on the options you selected in the Step 3 of EasyStep Interview.
- Opening Bal Equity – This account is created the first time you enter the opening balance for a balance sheet account. Every time you add a new account with an opening balance, QuickBooks records the second half of the entry in the Opening Bal Equity account. This means that total equity is the net balance of the assets minus the liabilities entered into QuickBooks. Once you’ve entered all of the accounts and balances, you may use a journal entry to allocate Opening Balance Equity to the proper equity accounts. Consult your accountant for help with this.
- Retained Earnings – This account is unique because there is no register associated with it. Each time you run a balance sheet, you assign the date of the report. QuickBooks then calculates the net income from all transactions from the earliest date in the company file to the end of the fiscal year prior to the current year. QuickBooks displays the results as retained earnings. Because of this feature, you don’t need to make the traditional closing entries at the end of the year.
- Shareholder Distributions – This Equity account is automatically created based on the options you selected in the Step 3 of the EasyStep Interview.
- Uncategorized Income – This account is used the first time you enter an opening balance for a customer directly into the customer record.
- **Cost of Goods Sold (COGS) – When the inventory feature is turned on and the first inventory item is created in a company file, the Cost of Good Sold (COGS) account is created. When you choose the Construction General Contractor business type in Step 2 of the EasyStep Interview, several individual Cost of Goods Sold accounts are created – Blueprints and Reproduction, Bond Expense, Construction Materials Costs, Equipment Rental for Jobs, Other Construction Costs, Subcontractors Expense, Tools and Small Equipment, and Worker’s Compensation Insurance.
- Expenses – When you choose the Construction General Contractor business type in Step 2 of the EasyStep Interview, several expense accounts are automatically created that might be useful in your business – Auto and Truck Expenses, Bank Service Charges, Business Licenses and Permits, Depreciation Expense, Insurance Expense, Interest Expense, Meals and Entertainment, Office Supplies, Professional Fees, Rent Expense, Repairs and Maintenance, Telephone Expense, and Utilities.
- *Payroll Expenses – This account is created when you turn on payroll in a company data file. All payroll expense items are initially mapped to this account.
- Uncategorized Expenses – Created the first time you enter an opening balance for a vendor from within the actual Vendor record.
- Reconciliation Discrepancies – An expense account used when you enter an adjustment to reconcile small accounting discrepancies, it is used to track all reconciliation differences.
- Ask My Accountant – QuickBooks automatically creates this Other Expense account, which is used to hold transactions that you aren’t quite sure how to classify. When you use this account, make sure that you use the memo field of the transaction to hold details that will help your accountant correctly classify the transaction – don’t rely on your memory.
- Estimates – creates this account for you to hold information about estimates or bids that you’ve created for various customers. This is a non-posting account that does not affect your balance sheet or income statement.
- Purchase Orders – created the first time you issue a purchase order to a vendor/subcontractor. This is a non-posting account that does not affect your balance sheet or income statement.
* A single Payroll Liabilities and Payroll Expenses account can be very limiting; subaccounts should be created for each type of payroll liability and expense.
** Rather than several individual Cost of Goods Sold Accounts; create a “Parent” account and make the individual accounts “subaccounts”.
Other Chart of Account blog articles:
- Understanding Your Chart of Accounts
- Using Account Numbers in your QuickBooks Chart of Accounts
- The Chart of Accounts and it’s Many Uses in your business
- Modifying & Working with your Chart of Accounts
