Skip to main content
Solved

Profit Margin Report with Current Sales Price included


Forum|alt.badge.img

I need to create a report based on the Current Sales Price and compare it to Last Cost and Avg Cost, so we can see what the Profit Margin/Profit % is.

I need to include the following fields in the report:

  • Customer       
  • Inventory ID    
  • Current Sale Price      
  • Last Cost        
  • Avg. Cost        
  • Last Transaction Qty    
  • Last Transaction Date   
  • Profit Margin   
  • Profit%

I would like to be able to filter this report based on Customer Sales Price Code/Customer and Start Date (this would be the Start Date for the Customer’s Price Code)

I’m not sure how to bring all this data together, so if someone out there can help, I would greatly appreciate it!

Sarah

Best answer by Neil Cantral

We had a similar GI that was built around approving Sales Price Worksheets - I made a few tweaks but something like the attached GI should help by providing a base to build off of.

This uses a cross join to pull every possible combination of Customers and Items then joins in ARSalesPrice  twice for the current Customer Price Class and Customer sales prices.

INItemCost and INItemStats were joined to bring in cost info. For my purposes I only care about cost from a specific warehouse so it is hardcoded in the relations for INItemStats.

Finally, ItemCustSalesStats was pulled in for the last sales data. This is my first time using the table and at first glance it looks like it is pulling the expected data.

 

View original

Neil Cantral
Jr Varsity I
Forum|alt.badge.img+3

We had a similar GI that was built around approving Sales Price Worksheets - I made a few tweaks but something like the attached GI should help by providing a base to build off of.

This uses a cross join to pull every possible combination of Customers and Items then joins in ARSalesPrice  twice for the current Customer Price Class and Customer sales prices.

INItemCost and INItemStats were joined to bring in cost info. For my purposes I only care about cost from a specific warehouse so it is hardcoded in the relations for INItemStats.

Finally, ItemCustSalesStats was pulled in for the last sales data. This is my first time using the table and at first glance it looks like it is pulling the expected data.

 


Forum|alt.badge.img
  • Freshman I
  • June 21, 2021

Hi @Neil Cantral 

Thanks for sharing your GI, I appreciate it!

I feel like I’m in over my head here...I need to find a Report Designer to bring on staff :)

Sarah


Is it possible to get the above mentioned GI that I can use to help me get started calculating profit margin? I would really appreciate it as i have just been working on defining the pricing calculations and reporting.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings