Hello all - This is my first post to this forum. I apologize in advance
for its length. But I've had trouble with finding the right formula/s
for an incentive plan I'm trying to model.

I'm working on a model that will allow me to do the following:
1. Pay bonuses to a manager for monthly performance over 2005 at a
fixed % rate.
2. There is a monthly cap which is reached when the 2006 monthly
actual reaches the 2006 monthly budget.
3. If the manager exceeds his 2006 budgeted amount in a given month,
the difference is 'pooled' to offset any shortages from prior months.
A positive 'pool' can help the manager to regain any bonuses that were
missed in prior months.

EXAMPLE
Month 1 Budget $1M
Month 1 2005 $750K
Month 1 2006 Actual $650K
The manager would not qualify for a bonus becasue he didn't reach his
2005 actuals (the target).

Month 2 Budget $1M
Month 2 2005 $700K
Month 2 2006 Actual $1.05M
The manager would earn a fixed percentage rate (I'm using 3.14%) times
the amount over 2005 up the the budget. Therefore $300K (1M less 700K)
x 3.14%. Now, since the manager was previously $100K under his 2005
target in month 1, the over budget performance of $50K from Month 2
would pool with the shortage of budget -$100K from month one reducing
the ytd total to only -$50K. If the manager beats budget by another
$50K in month 3 he can recapture his lost incentive from month 1.

Where I'm having an issue is that my current spreadsheet (doc attached)
seems to be be paying out more than it should on months where my manager
has exceeded budget but is still under his target for the year.

I'd like to be more informative but at this point I'm thinking I'll
float the spreadsheet out there and see what questions/comments you
have for me.

Thanks in advance!
Derek


+-------------------------------------------------------------------+
|Filename: Incentive Worksheet.doc |
|Download: http://www.excelforum.com/attachment.php?postid=5153 |
+-------------------------------------------------------------------+

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