Hello Everyone,
I'm Navi and I'm a great fan of Chandoo.org. I've learnt a lot in excel through Chandoo.org. In my free time, I always go through this forum for tips on Excel and some of the great solutions given here.
I building an incentive program for my employees based on their performance and looking for help on it. I've attached the sample data here with the Incentive Grid for you to review.
I'll try and explain the whole concept in a shortest way possible.
There are basically two parameters which are metrics.
Goal, Variance to Goal, Metric2
Goal - Each employee has an individual goal. This will range from x to 100% where x is any number from 0% and max is 100%
Variance to Goal - this is derived by 1-(Achieved/Goal). This value can range from - infinity to +infinity based on achieved number to goal.
Metric2 - this value again ranges between - infinity to +infinity (goal for this parameter is not a factor here)
There are two filters to come up to the final incentive %:
Goal is bucketed into 4 groups with goal value ranging from 65% to 99%
Variance to Goal is jotted down with a range value from 0% to more than 7% under each goal range.
Metric2 is bucketed into 5 groups with values ranging from -20% to +20% above and each group contains the final incentive % an employee is going to get.
Example :
(only "eligible" employees are given incentives)
If an employee's "goal" is ranging between 65% to 74% and "variance to goal" value ranging between 0% to 3% and "Metric2" ranging from -20% to -10%, he/she should be getting a 3% incentive (this is the value I"m trying to get as an output). Likewise, if the "goal" and "variance to goal" are as above but "metric2" is more than 20%, the employee should be getting a 7% incentive (output). Same logic is applicable for each value.
Thanks alot in advance for the help. Please let me know if you need further information on this.
Regards
Navi
I'm Navi and I'm a great fan of Chandoo.org. I've learnt a lot in excel through Chandoo.org. In my free time, I always go through this forum for tips on Excel and some of the great solutions given here.
I building an incentive program for my employees based on their performance and looking for help on it. I've attached the sample data here with the Incentive Grid for you to review.
I'll try and explain the whole concept in a shortest way possible.
There are basically two parameters which are metrics.
Goal, Variance to Goal, Metric2
Goal - Each employee has an individual goal. This will range from x to 100% where x is any number from 0% and max is 100%
Variance to Goal - this is derived by 1-(Achieved/Goal). This value can range from - infinity to +infinity based on achieved number to goal.
Metric2 - this value again ranges between - infinity to +infinity (goal for this parameter is not a factor here)
There are two filters to come up to the final incentive %:
Goal is bucketed into 4 groups with goal value ranging from 65% to 99%
Variance to Goal is jotted down with a range value from 0% to more than 7% under each goal range.
Metric2 is bucketed into 5 groups with values ranging from -20% to +20% above and each group contains the final incentive % an employee is going to get.
Example :
(only "eligible" employees are given incentives)
If an employee's "goal" is ranging between 65% to 74% and "variance to goal" value ranging between 0% to 3% and "Metric2" ranging from -20% to -10%, he/she should be getting a 3% incentive (this is the value I"m trying to get as an output). Likewise, if the "goal" and "variance to goal" are as above but "metric2" is more than 20%, the employee should be getting a 7% incentive (output). Same logic is applicable for each value.
Thanks alot in advance for the help. Please let me know if you need further information on this.
Regards
Navi
Attachments
Last edited: