Trusted Members
December 20, 2019
Can you please describe what you are trying to achieve?
Is column C actual sales? and row 2 the target?
do you want the percentages to work out on only the part between the target? for example 1% of the amount between 60k & 70k and then 5% of the amount between 70k and 80k?
what is the 10x10 grid for at the bottom of the page?
New Member
October 27, 2020
What i am trying to do is to automate the allocation of the commission based on Sales (C3:C17).
Column C is the actual sales and row 2 is the target
The commission to be awarded only the actual sales is between the target - for example, 1% of the amount between 60k & 70k and then 5% of the amount between 70k and 80k
Example Business Case Scenario
a) 1% Commission for achieving the 60K target (60K, 120K, 180K, ...)
b) 5% Commission for achieving the 70K target (70K, 140K, 210K, ...)
c) 9% Commission for achieving the 120K target (120K, 240K, 360K, ...)
If a sales order is 66.8K, the dealer gets a commission of 1%
If a sales order is 133.6K, the dealer gets a commission of 26% (where 1% from achieving the 60K Target and 25% for achieving the 120K target)
In the example file attached, I manually allocated the commission (using the 10x10 grid) based on the actual sales per order and a simple formula to calculate the total commission to be payout.
The manual effort is time-consuming and not error-proof.
1 Guest(s)