Calculating a bonus check

Ok All...I am trying to put my bonus calculations on a spreadsheet.

Here is an example of what the payout would be if:

baseline goal 8.44%
goal attainment 9.5%
over goal 1.06%

Payout calculation:
baseline reached = $2888
1 share x $1500 (.01 x $15)= $1500
.06 share x $3000 (.06 x $30)= $180

Total Payout = $4568

Here is the commission rates paid:
rate paid at >0<=1% above baseline ($15)
rate paid at >1%<=2% above baseline ($30)
rate paid at >2% above baseline ($45)

<90% baseline attainment = $0 bonus
90% baseline attainment = 50% of target or $1444
95% baseline attainment = 75% of target or $2166
100% baseline attainment = target of $2888
baseline plus 1% = $4388 (plus $15 per .01 share)
baseline plus 2% = $7388 (plus $30 per .01 share)
baseline plus 3% = $11888 (plus $45 per .01 share)

I want to be able to plug in my baseline goal and goal attainment and have the spreadsheet calculate my bonus.

Any help would be greatly appreciated.

Originally Posted by davesexcel

Hi GatorFanDan,
Lets see what you have so far, obviously you have something laid out by now and you are just looking for formulas, if you could zip an example of the workbook and attach it to your next post then we can see how you get your numbers

Hi Dave,

I've been using mostly "IF" statements to try and calculate the bonus. The challenge I am having is that if I reach...lets say...1.3% above my baseline goal, then the I get $1500 for covering the first tier which is the "1" of the 1.3%. The ".3" is calculated at the next tier, which is shown in the chart from my original post.

Here is what my spreadsheet looks like so far:

Baseline Marketshare 8.44 (input)
Q1,2008 Performance 9.5 (input)
Share over baseline 1.06 =B5-B4

Payout
<90% baseline attainment FALSE =IF(B5<B4*0.9,"NO PAYOUT")
90% of baseline attainment 1444 =IF(B5>=B4*0.9,1444,IF(B5<B4*0.95,1444))
95% of baseline attainment 2166 =IF(B5>=B4*0.95,2166,IF(B5<B4*1,2166))
Baseline attainment 2888 =IF(B5>=B4,2888)
Baseline plus =IF(B6=0,0,IF(B6>=0.01,B6*15,IF(B6=1,B6*1500)))
Baseline plus 1 FALSE =IF(B6>1<=2,B6-1) 0 =B14*1500
Baseline plus 2 FALSE =IF(B6>2<=3,B6-2) 0 =B15*3000
Baseline plus 3 FALSE =IF(B6>3,B6-3) 0 =B16*4500

Again, thanks for any help you can provide.

Dan

Any ideas? I'm gonna put this back on the top of the thread list.

Any ideas? I'm gonna put this back on the top of the thread list.

Any help from anyone would be greatly appreciated.

Thanks in advance,

Dan