QuickBooks Tip: Tying the Profit and Loss to the Profit and Loss by Job
By Joe Woodard, AccountingWEB QuickBooks columnist
The Profit & Loss by Job report shows all of the income and expenses you have assigned to Jobs on Checks, Bills, Invoices, Journal Entries, etc. However, the totals on the Profit & Loss by Job report may not agree to the totals on the Profit & Loss. There are two primary reasons:
- The income statement activity is not assigned to a Customer or Job name. Transactions coded to the income statement that do not include a job name do not appear on the Profit & Loss by Job report.
- The income statement activity is assigned to a name record other than a Customer or Job. The Profit & Loss by Job report shows columns for the Customers and/or Jobs only – not for any other type of name like vendors, other names or employees.
The Primary Issue: If you do not assign all income statement activity to a Customer or Job, QuickBooks does not display the income or expense on the Profit & Loss by Job report. This causes the Profit & Loss by Job to show different totals than the Profit & Loss. It is critical for these two reports to agree.
The Strategy behind the Workaround: The Profit & Loss by Job report doesn’t tie to the Profit & Loss unless every post to income and expense refers to a Customer or Job. As a result, you must total the Profit & Loss by some other criteria – one that shows the columns for the Customers/Jobs but also include transactions not posted to a Customer or Job.
Option 1: Use a Modified Expenses Not Assigned to Jobs Report
By default the Expenses Not Assigned to Jobs report includes purchase forms only (e.g. Bills, Check, Credit Card Charges, etc.). There are other settings that limit the amount of information you will see on the report as well. You need to modify this report so that it will show you more transactions in your file that are not assigned to jobs. Perform the following steps to modify the report:
- Remove the Transaction Type filter
- Filter the report by account for “All Income/Expense Accounts.” This account filter will include Cost of Goods Sold accounts as well. As an alternative you can filter the report for Income and Cost of Goods type accounts – if you are tying Gross Profit by Job to company-wide Gross Profit (rather than Net Income).
- Remove the Detail Level filter so that the report will show all detail levels – as opposed to “All Except Summary.”
Double-click each transaction on the report and assign a Customer or Job name. If the transaction is a discount taken on a bill (recorded on the Pay Bills window) you cannot assign a customer or job name. See Step 5 Option B in the section below for more information.
Option 2: Use a Modified Profit & Loss Report
You may need or prefer to use this option for three reasons:
- You and/or the client have an edition of QuickBooks that does not include the Expenses Not Assigned to Jobs report.
- The modified Expenses Not Assigned to Jobs report described in Option 1 above does not fully show the discrepancy between the Profit & Loss and the Profit & Loss by Job.
- You prefer to work from a summary report that shows the total balances so you can enter a summary journal entry to make the Profit & Loss tie to the Profit & Loss by Job (see Step 5 Option B below for more information).
Perform the following steps to create a Profit & Loss report that shows all Income Statement balances that are not assigned to jobs:
- Create a Profit & Loss Standard report.
- Filter the report by Customer:Job.
- QuickBooks displays a “No name” column in the far right column of the report, just to the left of the total. Double-click each amount in the “No name” column and assign the transaction to a Customer:Job name.
- If the totals on this report still do not agree to the Profit & Loss, total the report by “Payee.” You will notice that the report totals now agree to the Profit & Loss. However, there are still balances without a job name. To locate these balances, filter the report by Name for “All Vendors.”
- Double-click on balances that show on this report and assign the appropriate Customer or Job name. If the expense is overhead, use a Customer called “overhead.”
When you apply a discount using the Pay Bills window, QuickBooks does not allow you to assign a Customer:Job name. To assign these discounts to the applicable Jobs you have two options:
Option 1: Use Bill Credits instead of Vendor Discounts to reduce the amount you owe the Vendor. Unlike Vendor Discounts, Bill Credits allow you to assign a Customer:Job name. Also, Bill Credits allow you to post to the both the Account(s) and Item(s) you used on the Bill. In some situations it is best to post the discount from the Vendor to the same Items you used when recording the Bill, if the Bill contains Item records.
Option 2: Create a Journal Entry to adjust the account you use to track Vendor Discounts as shown below. Notice the credit and the debit are to the same Account, but the debit uses the vendor name and the credit uses the Customer:Job name. You would reverse the debits and credits to assign Costs and Expenses to jobs. The credit will then show on the Profit & Loss by Job report. There is not net impact on the Profit & Loss.
- After all of the balances on the report you created in Step 4 are assigned to jobs, filter the report by name for “All Other Names,” and repeat Step 5 above for any amounts that show on the report.
- After all of the balances on the report you created in Step 6 are assigned to jobs, filter the report by name for “All Employees” and repeat Step 5 above for any amounts that show on the report.
- After all of the balances on the report you created in Step 7 are assigned to jobs, remove the name filter and total the report by Customer:Job. Make sure the totals of the report agree to the company-wide Profit & Loss.
A Note about the Job Profitability Report
Even if you perform all of the steps above and even if the Profit & loss by Job agrees to the Profit & Loss, there still may be a discrepancy between the balances on the Profit & Loss by Job and the Job Profitability report. The Job Profitability report posts some transactions differently from the P&L by Job and in some cases excludes transactions even if they are assigned to a customer/job name.
Consider the following when tying the Job Profitability Summary Report to the Profit & Loss:
- Inventory adjustments do not show on the Job Profitability Summary Report even if there is a customer/job name assigned.
- The Job Profitability Report considers not only the type of account to which a transaction posts but also the type of transaction. For example:
- Bills, Bill Credits, Checks, Credit Card Charges and Credit Card Credits that debit income will increase costs on the Job Profitability Report instead of decreasing revenue. The result is an overstatement in both Costs and Revenue. Overall profitability is not impacted. To quickly locate these transactions double-click the total costs on the Job Profitability Report. Then, filter by account for “All Ordinary Income Accounts.”
- Invoices, Sales Receipts, Credit Memos and Deposits that credit Cost of Goods Sold will increase revenue on the Job Profitability Report instead of decreasing costs. The result is an overstatement in both Costs and Revenue. Overall profitability is not impacted. Exception: When sales forms debit Costs of Goods Sold through the use of an Inventory Item these debits will show in the Costs column of the Job Profitability Summary report. To quickly locate these transactions, double-click total Revenue on the report and filter by account for All Cost-of-Sales Accounts. (Tip: Include expense accounts if you think some job costs are recorded as expenses.)
About the author
Joe Woodard is an Advanced Certified QuickBooks ProAdvisor and Intuit Solution Provider who has taught over 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, Louisiana and the second in Atlanta, Georgia – 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.