Need assistance calculating bonus plan

[derekblain]

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

 

[Bob Phillips]

How about posting the spreadsheet rather than a picture?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[derekblain]

Good Morning Bob -

I tried doing that last night but saw that users can only bitmaps an
docs - there was no option for posting xls files.

I've zipped it up and have now attached it this way. If this i
incorrect and there is another way to post, let me know.

Thanks,
Dere

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

 

[Bob Phillips]

Should C13 be

=IF(SUM($B$12:C12)>0,ROUND(C12*C7,0),0)

which you copy across.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[derekblain]

Hey Bob -

When doing what you've suggested, an earned bonus of $5881 for March
doesn't calculate as being due, but it should.

The formula should probably have some type of 'if-then-and' that says
if you've hit your current month target, you get your bonus AND any
year to date amount owed. If the year to date (which doesn't look like
it comes into play until June) is positive, you get 3.14% of that
amount. If it's negative there is no detriment, just no add'l bonus.


Make sense? And thanks...

Derek

 

[derekblain]

Good Afternoon -

I'm not sure if this helps, but I found another way to express m
attempted formula.

All to occur in cell G2

Condition 1.
If F2 is > 0 then multiply F2 by D2.

Condition 2 in the same cell.
If the product of F2 multiplied by D2 is > E2 AND the sum of F2 and F1
multiplied by D2 is > 0, then add the product of D2 multiplied by th
sum of
F1 and F2 to E2, otherwise input E2

Condition 3 in the same cell.
If the product of F2 multiplied D2 is < E2 BUT the sum of F2 and F1
multiplied by D2 is > 0, then input the product of D2 multiplied by th
sum
of F1 and F2.

Condition 4 in the same cell.
If F2 < 0, and the sum of sum of F2 and F1 multiplied by D2 is < 0
then 0.

Dere