# Bonus Calculation

1. ## Bonus Calculation

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  Register To Reply

2. ## Re: Bonus Calculation

bonus question (1).xlsx

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

3. ## Re: Bonus Calculation

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.  Register To Reply

4. ## Re: Bonus Calculation

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))))  Register To Reply

5. ## Re: Bonus Calculation

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.  Register To Reply