Htydjtk

I have a set of rules that define how the incentives for the sales agents get computed. They are stored in a table with the columns: Sales_agent, Product, Incentive where:

• Sales_agent -> name/user_id of the person that makes the sale


• Product -> product name/type


• Incentive -> a percent that will be applied to the sales value to determine the absolute value of the sales bonus for the cumulated sales (for the previous month) of that particular product, and that particular sales agent

The incentives have been established by the client, not only taking into consideration the type of product the agent sells, but also the actual person (employee), as they have been individually negotiated.

The aforementioned table, that stores the incentive percentage for all agents, for all products contains about 3500 rows. The client wants to simplify and would like not to consider the person anymore, but only the product. The new incentives rules table will look like this: Product, Incentive (the contents are the same as described above for the old table).

The thing is that they will also supply a table containing all the calculated sales incentives for last year and they want:

• to have approximately the same total paid sum (total sum of incentives paid for all agents) as with the actual set of rules, when the new set is applied on last year data


• a minimal variation of individual incentives, compared to last year data when the new set of rules is applied


• considering that there will be some people that will be more affected by the change (they might be paid much lower incentives), they would prefer to have a few of these cases instead of having more cases where the difference is lower

I tried my best to explain as clearly as possible the requests but I might still have missed something, if something is not clear, please ask for clarifications.

My objective, and the part I need some help with, is to create an algorithm that can help me create a new set of rules that respect the conditions specified above.

I actually thought of a method that creates the new set of rules by removing duplicates in the Product, Incentive data set. It would then proceed to change the incentives values inside, computes the values of the incentives from last year based on the new rules and then compares with the last year output. It loops on applying the same modifications until the desired result is reached. It might actually work but it could take a long while as it's a rather brute force approach and I'd need something more elegant.