Marginal percentage within a slab

Recently I was asked to make an incentive plan with following slabs. One who achieves his target within the slab will get corresponding percentage of his bonus.

target1

Therefore someone who achieves his target with say 45% will get 25% of his bonus amount. Target achievement of above 100% will also get bonus as per highest slab. This task was fairly easy with nested if and easier with vlookup function.

However later it was discussed that it would be unfair to give same percentage of bonus to someone who achieve 45% and other 25% of his target. So it was decided that the percentage will be on pro-rata basis within that slab. Further anyone who achieves his target by more than 100% will get incremental 120% of his bonus for every one percent of target above 100%.

This task also would have been easier had the marginal increase was same but it was not. As you can see in the table below that marginal increase is different for different slabs.

target5

The equation of a straight line y=mx+c and mighty vlookup function comes to our rescue here. I came up with following solution.

target4

Helper column is same as lower limit of target. And yes one has to achieve 125% of his target to get 100% bonus amount in this case. You can update the table as per your need like not giving any bonus for 0-25% target or different slabs etc. Following is the link to file for your perusal…

https://drive.google.com/file/d/0B2FLZ9YjqpJTRUhyYW5PQkFCMkk/view?usp=sharing

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s