Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need assistance calculating bonus plan


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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Need assistance calculating bonus plan

How about posting the spreadsheet rather than a picture?

--
HTH

Bob Phillips

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

"derekblain" wrote
in message ...

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need assistance calculating bonus plan


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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Need assistance calculating bonus plan

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" wrote
in message ...

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need assistance calculating bonus plan


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
------------------------------------------------------------------------
derekblain's Profile: http://www.excelforum.com/member.php...o&userid=37171
View this thread: http://www.excelforum.com/showthread...hreadid=568877



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Need assistance calculating bonus plan


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


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seat Plan natski New Users to Excel 1 May 5th 06 10:17 PM
Formula to figure bonus EboniDimplz Excel Worksheet Functions 3 April 19th 06 01:24 AM
formula for bonus or penalty LADY New Users to Excel 2 March 29th 06 09:39 PM
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) RICHARD Excel Worksheet Functions 1 March 15th 05 06:49 PM
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! RICHARD Excel Discussion (Misc queries) 0 March 1st 05 02:53 PM


All times are GMT +1. The time now is 12:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"