Bonus Calculation

**DavidRainey** · 06-20-2013, 04:32 PM

I am tryining to create a spreadsheet which will give me the percentage of bonus which needs to be paid out, based on sales results. I have only been given 4 targets. 0 bonus if sales are 3,431,960 . The bonus goes from 0 to 25% as sales rise to $3,502,000, 125% bonus up to sales of $4,140,000. The actual sales were $3,943,450.
So the % of bonus they need to pay out lies between 25% and 125%. I used the same formulas they used last year (which is attached). I'm not sure how this formula can calculate the linear progression (wrong terminology?) of the bonus payout %. Does anyone have another method of creating this calculation so I can prove the math? I don't pretend to be an excel whiz so please try to take it easy on me. TX

**jkj115** · 06-20-2013, 05:53 PM

bonus question (1).xlsx

you can just use a vlookup. Set up a table with the different levels. See attached

**DavidRainey** · 06-21-2013, 12:54 AM

Thanks for the suggestion. The problem is the bonuses are not going to be 25%, 140%, etc. They will fall somewhere in that range. Since the sales figure turned out to be $3,943K, the percentage of payout will fall somewhere between 25% and 125% of a stated bonus amount offered. But it won't be exactly 25% or 125%. The formulas used give the payout amount to be 87.27%, which sounds reasonable. I was hoping to find another way of calculating this, as I am not 100% sure on the formula.

**kvsrinivasamurthy** · 06-21-2013, 04:50 AM

Try this formula. Format the cell for % and for 2 decimals.

=IF($B$8<$B$5,0,IF($B$8<=$B$6,$C$5+(($B$8-$B$5)*($C$6-$C$5)/($B$6-$B$5)),$C$6+(($B$8-$B$5)*($C$7-$C$6)/($B$7-$B$5))))

**DavidRainey** · 06-21-2013, 01:42 PM

Thanks for the formula and taking the time to respond. The problem I see is that it is only looking at two scenarious, B5 and B6, but overlooks B7. I think part of that problem is how they worded this. Also, won't you need a starting point, from which point someone would begin to be entitled to a bonus? If revenues were $3,432,000, that person would still be entitled to a bonus, albeit a very small percentage. I changed my spreadsheet to reflect this, which to me makes more sense, as there is now a starting point. When I use the original formula, I get a 76.89% payout. When I do it manually I get a 72% payout (the manual part is probably wrong too). May be now it will make more sense to someone. I have reworded some of it also, they aren't really targets or goals, just if you hit this income level, you get this %, etc. The one weird thing I noticed in the formula is that if B9<B7, and also B9<B8. Both are true, so which one is acted upon? Hopefully this revised spreadsheet will make it more clear.