cancel
Showing results for 
Search instead for 
Did you mean: 
typicole
Level 3

How to Run Item Level Reports on Margin and Item Cost

Hello, I´m trying to run an item level report to answer the below questions:

  • What specific items do I earn the highest/lowest margins on?
    • Margins based off the cost I buy the item at vs. the price I sell the item at
  • What specific item categories do I earn the highest/lowest margins on?
    • I added a custom field to items called "cost codes"

 

I tried created a custom report to find these answers but I had no luck. I couldn´t figure out how to add a column to calculate margin to the item report. Also, when I was experimenting with adding a custom field called "cost code" to an item, it was not recognized on the report. Please look at the screenshots below to see what I mean.

 

this item below is categorized as "101" in the custom field, "cost code."

power strip.PNG

When I try creating a report with the "cost code" custom field as a column, the item I mentioned above does not have "101" listed. 

 

report with no cost code.PNG

 

Questions:

1. How to add a column to calculate margin to the item report?

2. Why is the item mentioned above not showing the value "101" as its cost code?

 

Any help you can provide would be GREATLY appreciated! :)

7 Comments 7
Rose-A
Moderator

How to Run Item Level Reports on Margin and Item Cost

Hey there, typicole. I appreciate the screenshots and the detailed information you've provided.

 

I know how important it is to run a report based on margin and item cost. However, generating the report you need is unavailable in QuickBooks Desktop.

 

As a way around, you'll want to export the report into Excel and add the necessary columns from there.

 

While this isn't available, I'd suggest submitting your feedback to make the program better by going to the Send Feedback Online on your QuickBooks Desktop.
 

 

Additionally, I recommend customizing the report to get the specific details you need. You can also memorize it to save its current customization settings. It serves as your record and helps you quickly access it for future use.

 

Don't hesitate to let me know if I can be of additional assistance. I'm always here to keep helping. Have a good one!

typicole
Level 3

How to Run Item Level Reports on Margin and Item Cost

Hi @Rose-A , thanks for responding to my post, I really appreciate it. So I understand that it's not possible to calculate margin in quickbooks but could you answer my second question as well? Why is my custom cost code field not allowing me to run filtered reports off of it? 

 

TirzahC
QuickBooks Team

How to Run Item Level Reports on Margin and Item Cost

Thanks for getting back to us, typicole.

 

As you mentioned, the custom cost code field won't show the details you've entered. Instead of using that report, you can pulled up a different one. You can used the Sales by Customer Detail report. With this , you'll see the details you've entered and run filtered reports. 

 

This is the workaround that I can think so you can get the data you need.


You can click the Memorize button if you want the same settings to be available for future use. Feel free to read through this article for more details: Create, access and modify memorized reports.

 

To learn more about getting the data you need, check this out: Customize reports in QuickBooks Desktop. This article will help you filter report just the way you want it.

 

Let me know how this goes and post a reply below. I'm always here to help you. Have a good one. 

typicole
Level 3

How to Run Item Level Reports on Margin and Item Cost

Thanks so much for your response. I followed your advice and ran the Sales by Customer Detail report. When I added the cost code custom field as a column to the report, the values entered into those fields for some of the items still weren't showing in the cost code column. 

LieraMarie_A
QuickBooks Team

How to Run Item Level Reports on Margin and Item Cost

Hello again, @typicole. Allow me to chime in and help you run an item profitability report.

 

The closest way to get your desired result is to run a Sales by Item Summary or Detail report. The summary will tell you how many of each item or service you have sold, the total sales, and the profitability of each item. The detail will show you the sales of each item with a detailed listing of each transaction. 

 

Here's how:

  1. Go to the Reports menu.
  2. Select Sales and choose Sales by Item Summary or Sales by Item Detail.
  3. Modify the reporting period.

 

However, you're unable to add columns using these reports. You can utilize the Item Listing report and add the Item cost code column. 

 

Here's how:

  1. Go to the Reports menu.
  2. Select List and choose Item Listing.
  3. Click the Customize Report button.
  4. Search for "Item code code" and select it.
  5. Click OK.

 

After that, export both reports to Excel and combine them.

 

Feel free to come back and add a comment if you need more help with running reports in QuickBooks. We're always here to help.

droybal
Level 3

How to Run Item Level Reports on Margin and Item Cost

If you have inventory you can use your inventory valuation summary report.  Clean it up to only show your items, avg costs and sales price.  Then export to Excel.  Add a column for margin formula (sales price - ave cost / sales price) and copy down page. 

 

If you want to see the margin on items you have sold,  you could use the sales by product or sales by item report. The Gross margin % is already on this report.

droybal
Level 3

How to Run Item Level Reports on Margin and Item Cost

I use the sales by item summary(for items sold during a particular time frame).  It will show you the gross margin %.  Or if you want to see margin of all inventory on hand,  I use the inventory valuation summary,  export it to Excel and ad enter a formula for margin,  copy it down the spreadsheet.  

Need to get in touch?

Contact us