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

[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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[derekblain]

Good Morning Bob -

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

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

Thanks,
Derek

[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" <[email protected]> wrote
in message news:[email protected]...
>
> Good Morning Bob -
>
> I tried doing that last night but saw that users can only bitmaps and
> docs - there was no option for posting xls files.
>
> I've zipped it up and have now attached it this way. If this is
> incorrect and there is another way to post, let me know.
>
> Thanks,
> Derek
>
>
> +-------------------------------------------------------------------+
> |Filename: Incentive Worksheet.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5156 |
> +-------------------------------------------------------------------+
>
> --
> derekblain
> ------------------------------------------------------------------------
> derekblain's Profile:
http://www.excelforum.com/member.php...o&userid=37171
> View this thread: http://www.excelforum.com/showthread...hreadid=568877
>

[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 my 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 the 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 the 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.

Derek