Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seat Plan | New Users to Excel | |||
Formula to figure bonus | Excel Worksheet Functions | |||
formula for bonus or penalty | New Users to Excel | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) |