Solved

Customer Tiered Rebates

  • 6 March 2024
  • 8 replies
  • 50 views
D
Rank

In our industry, it is common to provide monthly rebates to customers that meet certain order quantities. We could base this rebate off of monthly order quantities or monthly sales totals. For example: Tier A (0-100K), Tier B (101-250K), Tier C (251-500K), Tier D (501K+). Similar to how Acumatica processes Sales Commissions, we’d like to track sales of stock items so that we can send rebate checks to our customers that qualify for these rebates. Tier A would receive a 2% rebate, Tier B would receive a 4% rebate and so on. I can calculate this in excel, but it would be nice to have the capability built into Acumatica.

icon

Best answer by AndrewBGL 8 March 2024, 01:42

View original

8 replies

AndrewBGL
Rank
Userlevel 5
Badge

Hello,

The Sales Order Details by Customer report. You would just update the Customer ID to which customer you are looking for and then run the report. You could also save templates as needed. 
 

Andrew
AndrewBGL
Rank
Userlevel 5
Badge

Here is a screen shot of the the report

 

Andrew
AndrewBGL
Rank
Userlevel 5
Badge

Also the report will show the summations of the quantity and dollar amount. That way you don’t need to do a =sum() in excel. You can export as a PDF or .XLS as needed. 

 

Andrew
D
Rank

@AndrewBGL Thank you for your reply. I have a couple of issues with this solution.

Our customers purchase product in various UOM. If their PO is purchased in Each, Thousands, or by the case, the totals at the bottom of the report do not calculate the totals in EA only by the sales UOM. So this would still require some manual math to figure out.

Secondly, this report bases its information off of the Sales Order, not off of receipt of payment. If we use this solution, we run the risk of giving a customer a rebate that they end up canceling or returning. Or if they receive a credit for defective product/bad pallets/etc that would not be factored into it and we’d end up providing them a double discount.

Any other possible solutions?

AndrewBGL
Rank
Userlevel 5
Badge

@AndrewBGL Thank you for your reply. I have a couple of issues with this solution.

Our customers purchase product in various UOM. If their PO is purchased in Each, Thousands, or by the case, the totals at the bottom of the report do not calculate the totals in EA only by the sales UOM. So this would still require some manual math to figure out.

Secondly, this report bases its information off of the Sales Order, not off of receipt of payment. If we use this solution, we run the risk of giving a customer a rebate that they end up canceling or returning. Or if they receive a credit for defective product/bad pallets/etc that would not be factored into it and we’d end up providing them a double discount.

Any other possible solutions?

Hello,

You can use the Additional Sort and Filters to help focus on the data you are looking for. 

Andrew
AndrewBGL
Rank
Userlevel 5
Badge

Here is an image of the filters you need. You can set the SO status to invoiced to ensure only completed orders are in the report. 

You can also define the UOM and set it to pack to account for the cases of products you sell. 

Andrew
D
Rank

@AndrewBGL thank you for trying to help. Your suggestions do not solve my issue and I will further explain why.

SalesOrder.Status = Invoiced returns no results. I changed the value to “Completed” and received results. But this filter only returns Sales Orders that were both 1) created between the filter dates, and 2) that have been invoiced. What if I set a SO filter for Jan 1, 2024 to Jan 31, 2024, but I created a SO in Dec 2023 and invoiced for it in Jan 2024? In this case, that Jan 2024 invoiced item would not appear on this report. Furthermore, nearly all of our products are “Purchased to Order” and it is common in our industry to either overship or undership quantities on the PO. That means, I am almost never selling my customer the exact quantity on their sales order. This specific report gives me the line total for the amount on the Sales Order, but it does not return the values that were actually invoiced. I would only provide a rebate to a client based on what we actually invoiced.

SalesOrderLine.UOM = PACK returns no results. I changed the value to “EA” and it only returned results that were sold by EA. If I changed the Value to “CTN250” it only returned the results that were sold in CTN250. I was hoping there was a way that Acumatica would convert CTN250 (a case pack of 250) to EA (each). We have multiple different case packs depending on the item purchased.

For the above mentioned reasons, the suggested solution is not viable for our business.

AndrewBGL
Rank
Userlevel 5
Badge

Hello @dyanSIPAC ,

For your scenario you are right. There will not be a default report that will work. You can use the report as a framework to export to Excel. 

Andrew

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