+ Reply to Thread
Results 1 to 5 of 5

Bonus Calculation

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    Sonoma, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    09-25-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    148

    Re: Bonus Calculation

    bonus question (1).xlsx

    you can just use a vlookup. Set up a table with the different levels. See attached
    pls click the star if you liked my answer!

  3. #3
    Registered User
    Join Date
    06-20-2013
    Location
    Sonoma, CA
    MS-Off Ver
    Excel 2010
    Posts
    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.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,497

    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))))

  5. #5
    Registered User
    Join Date
    06-20-2013
    Location
    Sonoma, CA
    MS-Off Ver
    Excel 2010
    Posts
    3

    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.
    Attached Files Attached Files
    Last edited by DavidRainey; 06-21-2013 at 01:53 PM. Reason: revised wording on spreadsheet

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1