Solved

Profit Margin Report with Current Sales Price included

  • 17 June 2021
  • 3 replies
  • 271 views
S
Rank
Userlevel 3
Badge

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

icon

Best answer by Neil Cantral 18 June 2021, 00:59

View original
Sarah Bond

3 replies

Neil Cantral
Rank
Userlevel 5
Badge +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.

 

1 Attachment
S
Rank
Userlevel 3
Badge

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

Sarah Bond
S
Rank

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


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved