Customized Reports in QuickBooks

Dear Readers:

It’s important to learn how to create customized reports in QB. QB can filter data so you get information important to you.

Here’s a good example of how to create a very unique report. Open a sample company file in QB and follow along to learn how to get the hang of this.

Brian writes:

My business has inventory. We’d like to pay a percentage of the profit per invoice to each employee who generated that invoice. How do I customize a report in QuickBooks to track profit by invoice so we can do this?

My reply:

I will assume that the Sales Rep box appears on your invoices, and that each employee has been assigned as a rep. If employees haven’t been assigned this way, do so before proceeding.

From any screen, press Control-F. This takes you to the Find screen. Click the Advanced tab.

In the Filters box, select the first filter: Account. From the Account drop-down box, select “All Income/Expense Accounts.”

Still in the Filters box, select Item. In the Item drop-down box, select Multiple Items. Select each Item that is used for the invoices in question.

Note that the only way for this report to work, you must use Inventory Items on the invoices that you wish to track via this report. Other types of items cannot be used to create a Gross Profit by Rep report.

Why is this? The only way QB can know when a specific item was for a specific sale is when Inventory Items are used on Invoices (or Sales Receipts). QB takes the Sales information from the item, and the average cost of the item, and posts both on the P&L as of the date of the invoice.

When other types of items are used in the business, QB only knows what you sold the item for, not what you paid for it. The costing information was posted to the P&L when the items were paid for through the Enter Bills, Write Checks, or Enter Credit Card Charges screens. QB has no way of knowing which sale the item was for when they’re posted in this way. This is why other types of items cannot be used for this report, or for a true COGS for that matter.

Ok? So back to the report…

In the Current Choices box, select Posting Status. Select the circle that says Posting. Click Find, then click Report.

Click the Modify Report button and remove the following columns: Left margin, Memo, Account, Class, Clr, Split, and Balance. Scroll through the list and add a column for Rep.

In the Total By box, select Rep. In the Sort By box, select Number.

Click the Header/Footer tab, and in the Report Title box, enter Gross Profit by Rep. Click OK. Adjust the date range as needed.

Click the Memorize button to save the report so you don’t have to do this over and over. You can even put the report in the Icon bar by navigating to the View menu and selecting, “Add ‘Gross Profit by Rep’ to Icon bar.”

Use the Modify Report button to make adjustments if this isn’t exactly what you need.

Isn’t QuickBooks great? With a little bit of work, we can create customized reports to show very unique data!

Take care and thanks for writing.

Jennifer Thieme's signature

Need More Help? Contact Jennifer

Leave a Reply