Why not post the file here, it can appear as a zipped attachment.

If a person is under the previous years performance for the first 2
month, say $50000 on month 1, $100000 on momth 2 and they exceed the
budget by $125000, in month 3, what would you wish to happen?
Pay the bonus on $125000 as previously you were $150000 behind?

Or something else

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=569657

This may be a start, I have only put the bonus as an amount, not
multiplied it by a fixed percentage. A slight isssue is If I have
exceeded performance and budget in month 1 but then failed in month 2,
can I get bonus in Month 2 if my year to date performance is still an
improvement on the previous year, in this example I have assumed you
do

Regards

Dav


+-------------------------------------------------------------------+
|Filename: Bonus.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5167 |
+-------------------------------------------------------------------+

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=569657

Good Evening Da and thanks for the comments.

I have opened your file and made some modifications and notes using my
actual data.

In answer to your two previous posts: If a person is under the previous
year in Month 1 by $50K, under Month 2 by $100K, but over budget in
Month 3 by $125K, they would get their Month 3 capped bonus. They
could only earn more than their cap if their ytd performance was over
'05 by such a number that when multiplied by 3.14% (my rate) the
product was greater than their monthly cap. In that case they'd earn
the difference. There is a caveat, but you'll see that in the
spreadsheet - it has to do with the fact that there are monthly caps
which sum up to YTD caps.

Regarding your 2nd post: If you have exceeded Month 1, but failed
Month 2, you could still earn a bonus *if* your ytd performance
multiplied by 3.14% was greater than what you had earned in Month 1.
You would still cap however at the combined total of the Month 1 and
Month 2 caps.

At the end of the day, I want to pay the employee for hitting monthly
targets. Their ytd performance has no punitive impact on their monthly
performance. However, I give them the option to make up for prior month
'misses' if the ytd perf is such that they are still hitting ytd
targets. They can recapture some of what they would have earned in the
months when they rec'd nothing or accrue it for future months when they
might miss a monthly target.

I appreciate your help.

Here goes the post...

By the way, while I've added the real data and monthy bonus info, I
didn't overwrite your bonus data because I assume you may drop my data
into your existing formulas.

Thanks again,
Derek


+-------------------------------------------------------------------+
|Filename: Bonus_v2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5172 |
+-------------------------------------------------------------------+

--
derekblain
------------------------------------------------------------------------
derekblain's Profile: http://www.excelforum.com/member.php...o&userid=37171
View this thread: http://www.excelforum.com/showthread...hreadid=569657

I think this is what you want

Regards

Dav


+-------------------------------------------------------------------+
|Filename: Bonus_v2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5173 |
+-------------------------------------------------------------------+

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=569657

Dav - this looks fantastic! Thank you very much for your help.

I'm going to take the model to work with me and try it on several of
the individual bonus plans I'm currently working on. Each of the plans
has different 2005 and budget numbers, however they all operate under
the same logic.

I'll reply back later and let you know how it goes.

One more question for you...if you don't mind. What if the monthly
bonus amount was fixed rather than using a fixed percentage? In the
case of our previous spreadsheet, let's say each month was worth 1/12
of $45,106. All of the other logistics apply. Can the formula be
modified in such a way that this can be accommodated?

Thank you,
Derek


--
derekblain
------------------------------------------------------------------------
derekblain's Profile: http://www.excelforum.com/member.php...o&userid=37171
View this thread: http://www.excelforum.com/showthread...hreadid=569657

It can be done, but how would the fixed bonus be allocated?

Would the bonus only get paid if the 2006 amount exceeded the budget
for that month or would you get a part bonus eg if 2006 was half way
between the 2005 figure and the budget would you get a 50% bonus.

It becomes a little more complicated reclaiming bonus from the previous
months as the relationship between bonus and sales is not linear. You
would have to provide a logic to deal with this

eg if the maximum bonus was 5 every month and you paid part bonuses


Budget 2005 2006 bonus paid reclaimed bonus
10 8 7 0 0
12 8 9 1.25 0
12 9 15 5 ?

in the first line the rate is 2.5 to every 1, in the second line 1.25
to 1 and in the 3rd line 1.66 to 1 how would you calculate the
reclaimed bonus? You could say the same as now eg

Total Budget =34 Total 2005=25 Total 2006=31 Total Bonus paid
=6.25

(31-25)/(34-25)*15 -6.25

but is this what you want? If so the logic is very similar to what has
already taken place, instead of multiplying by 3.14% you have to diivde
by budget-2005 and multiply by 45106/12

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=569657

I decided it was easier to show you!

Regards

Dav


+-------------------------------------------------------------------+
|Filename: Bonus_v3.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5178 |
+-------------------------------------------------------------------+

--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=569657