Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Challenging the GOAL SEEK function within a bonus scheme
I am currently confronted with a bonus participation scheme problem as
follows: Assumptions: Column B4:B13 Names of 10 employees Employee1:Employee10 Column C4:C13 Annual salary of each afore-mentioned employee Cell C1 = Average Bonus-Amount of 2.5% of the total annual salaries (SUM(C4:C13)/100*2.5) Column D4:D13 Individual Performance Assessment rating from 0.0 (minimum) to 4.0 (maximum) Column F4:F13 Individual Bonus-Amount as a percentage rate Column G4:G13 Individual Bonus-Amount Cell G15 = Cell C1 (i.e. SUM(G4:G13) = C1) Depending on the individual performance assessment, a bonus factor must be applied in order to calculate an exponentially increasing individual bonus amount, e.g. Assessment - Bonus-Amount as a percentage of the annual salary B19 = 0.0 - C19 = 0.00% B20 = 1.0 - C20 = 0.63% B21 = 2.0 - C21 = 1.56% B22 = 3.0 - C22 = 2.81% B23 = 4.0 - C23 = 5.00% Restrictions: The total average bonus amount must be 2.5% of the total annual salaries, see above Cell G15. How can I make use of the GOAL SEEK function in order to a) vary the percentage rates above (C19:C23) AND b) restrict the total amount of bonus to 2.5% (C1=G15)? Many thanks for any ideas in this respect! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Challenging the GOAL SEEK function within a bonus scheme
In F4 (and down):
=VLOOKUP(D4,$C$19:$E$23,3) In G4 etc: =F4*C4 In G17: =C1-G15 In B19: 0.01 In E19 and down: =D19*(1+$B$19) Goalseek G17 to be zero by changing cell B19 -- Kind regards, Niek Otten Microsoft MVP - Excel "ran58" wrote in message ... I am currently confronted with a bonus participation scheme problem as follows: Assumptions: Column B4:B13 Names of 10 employees Employee1:Employee10 Column C4:C13 Annual salary of each afore-mentioned employee Cell C1 = Average Bonus-Amount of 2.5% of the total annual salaries (SUM(C4:C13)/100*2.5) Column D4:D13 Individual Performance Assessment rating from 0.0 (minimum) to 4.0 (maximum) Column F4:F13 Individual Bonus-Amount as a percentage rate Column G4:G13 Individual Bonus-Amount Cell G15 = Cell C1 (i.e. SUM(G4:G13) = C1) Depending on the individual performance assessment, a bonus factor must be applied in order to calculate an exponentially increasing individual bonus amount, e.g. Assessment - Bonus-Amount as a percentage of the annual salary B19 = 0.0 - C19 = 0.00% B20 = 1.0 - C20 = 0.63% B21 = 2.0 - C21 = 1.56% B22 = 3.0 - C22 = 2.81% B23 = 4.0 - C23 = 5.00% Restrictions: The total average bonus amount must be 2.5% of the total annual salaries, see above Cell G15. How can I make use of the GOAL SEEK function in order to a) vary the percentage rates above (C19:C23) AND b) restrict the total amount of bonus to 2.5% (C1=G15)? Many thanks for any ideas in this respect! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Challenging the GOAL SEEK function within a bonus scheme
You can of course also leave the percentages the same and change the
threshold values, with the same technique. In that case, apply the multiplication by (1+$B$19) to C19 and down and adjust your VLOOKUP formula -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... In F4 (and down): =VLOOKUP(D4,$C$19:$E$23,3) In G4 etc: =F4*C4 In G17: =C1-G15 In B19: 0.01 In E19 and down: =D19*(1+$B$19) Goalseek G17 to be zero by changing cell B19 -- Kind regards, Niek Otten Microsoft MVP - Excel "ran58" wrote in message ... I am currently confronted with a bonus participation scheme problem as follows: Assumptions: Column B4:B13 Names of 10 employees Employee1:Employee10 Column C4:C13 Annual salary of each afore-mentioned employee Cell C1 = Average Bonus-Amount of 2.5% of the total annual salaries (SUM(C4:C13)/100*2.5) Column D4:D13 Individual Performance Assessment rating from 0.0 (minimum) to 4.0 (maximum) Column F4:F13 Individual Bonus-Amount as a percentage rate Column G4:G13 Individual Bonus-Amount Cell G15 = Cell C1 (i.e. SUM(G4:G13) = C1) Depending on the individual performance assessment, a bonus factor must be applied in order to calculate an exponentially increasing individual bonus amount, e.g. Assessment - Bonus-Amount as a percentage of the annual salary B19 = 0.0 - C19 = 0.00% B20 = 1.0 - C20 = 0.63% B21 = 2.0 - C21 = 1.56% B22 = 3.0 - C22 = 2.81% B23 = 4.0 - C23 = 5.00% Restrictions: The total average bonus amount must be 2.5% of the total annual salaries, see above Cell G15. How can I make use of the GOAL SEEK function in order to a) vary the percentage rates above (C19:C23) AND b) restrict the total amount of bonus to 2.5% (C1=G15)? Many thanks for any ideas in this respect! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Challenging the GOAL SEEK function within a bonus scheme
On 27 Jul., 14:03, "Niek Otten" wrote:
You can of course also leave the percentages the same and change the threshold values, with the same technique. In that case, apply the multiplication by (1+$B$19) to C19 and down and adjust your VLOOKUP formula -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... In F4 (and down): =VLOOKUP(D4,$C$19:$E$23,3) In G4 etc: =F4*C4 In G17: =C1-G15 In B19: 0.01 In E19 and down: =D19*(1+$B$19) Goalseek G17 to be zero by changing cell B19 -- Kind regards, Niek Otten Microsoft MVP - Excel "ran58" wrote in message .... I am currently confronted with a bonus participation scheme problem as follows: Assumptions: Column B4:B13 Names of 10 employees Employee1:Employee10 Column C4:C13 Annual salary of each afore-mentioned employee Cell C1 = Average Bonus-Amount of 2.5% of the total annual salaries (SUM(C4:C13)/100*2.5) Column D4:D13 Individual Performance Assessment rating from 0.0 (minimum) to 4.0 (maximum) Column F4:F13 Individual Bonus-Amount as a percentage rate Column G4:G13 Individual Bonus-Amount Cell G15 = Cell C1 (i.e. SUM(G4:G13) = C1) Depending on the individual performance assessment, a bonus factor must be applied in order to calculate an exponentially increasing individual bonus amount, e.g. Assessment - Bonus-Amount as a percentage of the annual salary B19 = 0.0 - C19 = 0.00% B20 = 1.0 - C20 = 0.63% B21 = 2.0 - C21 = 1.56% B22 = 3.0 - C22 = 2.81% B23 = 4.0 - C23 = 5.00% Restrictions: The total average bonus amount must be 2.5% of the total annual salaries, see above Cell G15. How can I make use of the GOAL SEEK function in order to a) vary the percentage rates above (C19:C23) AND b) restrict the total amount of bonus to 2.5% (C1=G15)? Many thanks for any ideas in this respect! Thank you so much, Niek Otten, for your guidance. This works fine.... There is however one additional factor, i.e. a further restriction, to consider: The maximum bonus percentage rate may not exceed 5.00%. If I e.g. change value C20 to 0.00% (equal to C19 = 0.00%), then the remaining GOAL SEEK calculated values in E21 (and down) change e.g. from 1.56% to 1.95% 2.81% to 3.52% 5.00% to 6.26% However, the maximum bonus percentage rate may not exceed 5.00%. How can this restriction be considered? Once again, many thanks for your advice! René |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Challenging the GOAL SEEK function within a bonus scheme
On 28 Jul., 12:45, ran58 wrote:
On 27 Jul., 14:03, "Niek Otten" wrote: You can of course also leave the percentages the same and change the threshold values, with the same technique. In that case, apply the multiplication by (1+$B$19) to C19 and down and adjust your VLOOKUP formula -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... In F4 (and down): =VLOOKUP(D4,$C$19:$E$23,3) In G4 etc: =F4*C4 In G17: =C1-G15 In B19: 0.01 In E19 and down: =D19*(1+$B$19) Goalseek G17 to be zero by changing cell B19 -- Kind regards, Niek Otten Microsoft MVP - Excel "ran58" wrote in message .... I am currently confronted with a bonus participation scheme problem as follows: Assumptions: Column B4:B13 Names of 10 employees Employee1:Employee10 Column C4:C13 Annual salary of each afore-mentioned employee Cell C1 = Average Bonus-Amount of 2.5% of the total annual salaries (SUM(C4:C13)/100*2.5) Column D4:D13 Individual Performance Assessment rating from 0.0 (minimum) to 4.0 (maximum) Column F4:F13 Individual Bonus-Amount as a percentage rate Column G4:G13 Individual Bonus-Amount Cell G15 = Cell C1 (i.e. SUM(G4:G13) = C1) Depending on the individual performance assessment, a bonus factor must be applied in order to calculate an exponentially increasing individual bonus amount, e.g. Assessment - Bonus-Amount as a percentage of the annual salary B19 = 0.0 - C19 = 0.00% B20 = 1.0 - C20 = 0.63% B21 = 2.0 - C21 = 1.56% B22 = 3.0 - C22 = 2.81% B23 = 4.0 - C23 = 5.00% Restrictions: The total average bonus amount must be 2.5% of the total annual salaries, see above Cell G15. How can I make use of the GOAL SEEK function in order to a) vary the percentage rates above (C19:C23) AND b) restrict the total amount of bonus to 2.5% (C1=G15)? Many thanks for any ideas in this respect! Thank you so much, Niek Otten, for your guidance. This works fine.... There is however one additional factor, i.e. a further restriction, to consider: The maximum bonus percentage rate may not exceed 5.00%. If I e.g. change value C20 to 0.00% (equal to C19 = 0.00%), then the remaining GOAL SEEK calculated values in E21 (and down) change e.g. from 1.56% to 1.95% 2.81% to 3.52% 5.00% to 6.26% However, the maximum bonus percentage rate may not exceed 5.00%. How can this restriction be considered? Once again, many thanks for your advice! René Further to my additional restricition mentioned, limitation to 5.00% can easily be accomplished by using an if-statement =IF(D19*(1+$B$19)<=5%;D19*(1+$B$19);5%) However, then the progressive curve turns into a degressive one, since the top value decreases more than the other remaining values. Thus, the question remains: Is there another way to proportionally affect all values with the 5%-limitation? Many thanks for your considerations! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Challenging the GOAL SEEK function within a bonus scheme
Now it becomes difficult! If you allow the ratios between the percentages to
change, an infinite number of combinations of percentages may give the same result. You'll have to apply some restrictions, I think. Therefore you may need Solver instead of Goal Seek. I can't help you with that! Google may give you several instruction articles. -- Kind regards, Niek Otten Microsoft MVP - Excel "ran58" wrote in message ... On 28 Jul., 12:45, ran58 wrote: On 27 Jul., 14:03, "Niek Otten" wrote: You can of course also leave the percentages the same and change the threshold values, with the same technique. In that case, apply the multiplication by (1+$B$19) to C19 and down and adjust your VLOOKUP formula -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... In F4 (and down): =VLOOKUP(D4,$C$19:$E$23,3) In G4 etc: =F4*C4 In G17: =C1-G15 In B19: 0.01 In E19 and down: =D19*(1+$B$19) Goalseek G17 to be zero by changing cell B19 -- Kind regards, Niek Otten Microsoft MVP - Excel "ran58" wrote in message ... I am currently confronted with a bonus participation scheme problem as follows: Assumptions: Column B4:B13 Names of 10 employees Employee1:Employee10 Column C4:C13 Annual salary of each afore-mentioned employee Cell C1 = Average Bonus-Amount of 2.5% of the total annual salaries (SUM(C4:C13)/100*2.5) Column D4:D13 Individual Performance Assessment rating from 0.0 (minimum) to 4.0 (maximum) Column F4:F13 Individual Bonus-Amount as a percentage rate Column G4:G13 Individual Bonus-Amount Cell G15 = Cell C1 (i.e. SUM(G4:G13) = C1) Depending on the individual performance assessment, a bonus factor must be applied in order to calculate an exponentially increasing individual bonus amount, e.g. Assessment - Bonus-Amount as a percentage of the annual salary B19 = 0.0 - C19 = 0.00% B20 = 1.0 - C20 = 0.63% B21 = 2.0 - C21 = 1.56% B22 = 3.0 - C22 = 2.81% B23 = 4.0 - C23 = 5.00% Restrictions: The total average bonus amount must be 2.5% of the total annual salaries, see above Cell G15. How can I make use of the GOAL SEEK function in order to a) vary the percentage rates above (C19:C23) AND b) restrict the total amount of bonus to 2.5% (C1=G15)? Many thanks for any ideas in this respect! Thank you so much, Niek Otten, for your guidance. This works fine.... There is however one additional factor, i.e. a further restriction, to consider: The maximum bonus percentage rate may not exceed 5.00%. If I e.g. change value C20 to 0.00% (equal to C19 = 0.00%), then the remaining GOAL SEEK calculated values in E21 (and down) change e.g. from 1.56% to 1.95% 2.81% to 3.52% 5.00% to 6.26% However, the maximum bonus percentage rate may not exceed 5.00%. How can this restriction be considered? Once again, many thanks for your advice! René Further to my additional restricition mentioned, limitation to 5.00% can easily be accomplished by using an if-statement =IF(D19*(1+$B$19)<=5%;D19*(1+$B$19);5%) However, then the progressive curve turns into a degressive one, since the top value decreases more than the other remaining values. Thus, the question remains: Is there another way to proportionally affect all values with the 5%-limitation? Many thanks for your considerations! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Challenging the GOAL SEEK function within a bonus scheme
On 28 Jul., 22:54, "Niek Otten" wrote:
Now it becomes difficult! If you allow the ratios between the percentages to change, an infinite number of combinations of percentages may give the same result. You'll have to apply some restrictions, I think. Therefore you may need Solver instead of Goal Seek. I can't help you with that! Google may give you several instruction articles. -- Kind regards, Niek Otten Microsoft MVP - Excel "ran58" wrote in message ... On 28 Jul., 12:45, ran58 wrote: On 27 Jul., 14:03, "Niek Otten" wrote: You can of course also leave the percentages the same and change the threshold values, with the same technique. In that case, apply the multiplication by (1+$B$19) to C19 and down and adjust your VLOOKUP formula -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... In F4 (and down): =VLOOKUP(D4,$C$19:$E$23,3) In G4 etc: =F4*C4 In G17: =C1-G15 In B19: 0.01 In E19 and down: =D19*(1+$B$19) Goalseek G17 to be zero by changing cell B19 -- Kind regards, Niek Otten Microsoft MVP - Excel "ran58" wrote in message ... I am currently confronted with a bonus participation scheme problem as follows: Assumptions: Column B4:B13 Names of 10 employees Employee1:Employee10 Column C4:C13 Annual salary of each afore-mentioned employee Cell C1 = Average Bonus-Amount of 2.5% of the total annual salaries (SUM(C4:C13)/100*2.5) Column D4:D13 Individual Performance Assessment rating from 0.0 (minimum) to 4.0 (maximum) Column F4:F13 Individual Bonus-Amount as a percentage rate Column G4:G13 Individual Bonus-Amount Cell G15 = Cell C1 (i.e. SUM(G4:G13) = C1) Depending on the individual performance assessment, a bonus factor must be applied in order to calculate an exponentially increasing individual bonus amount, e.g. Assessment - Bonus-Amount as a percentage of the annual salary B19 = 0.0 - C19 = 0.00% B20 = 1.0 - C20 = 0.63% B21 = 2.0 - C21 = 1.56% B22 = 3.0 - C22 = 2.81% B23 = 4.0 - C23 = 5.00% Restrictions: The total average bonus amount must be 2.5% of the total annual salaries, see above Cell G15. How can I make use of the GOAL SEEK function in order to a) vary the percentage rates above (C19:C23) AND b) restrict the total amount of bonus to 2.5% (C1=G15)? Many thanks for any ideas in this respect! Thank you so much, Niek Otten, for your guidance. This works fine.... There is however one additional factor, i.e. a further restriction, to consider: The maximum bonus percentage rate may not exceed 5.00%. If I e.g. change value C20 to 0.00% (equal to C19 = 0.00%), then the remaining GOAL SEEK calculated values in E21 (and down) change e.g. from 1.56% to 1.95% 2.81% to 3.52% 5.00% to 6.26% However, the maximum bonus percentage rate may not exceed 5.00%. How can this restriction be considered? Once again, many thanks for your advice! René Further to my additional restricition mentioned, limitation to 5.00% can easily be accomplished by using an if-statement =IF(D19*(1+$B$19)<=5%;D19*(1+$B$19);5%) However, then the progressive curve turns into a degressive one, since the top value decreases more than the other remaining values. Thus, the question remains: Is there another way to proportionally affect all values with the 5%-limitation? Many thanks for your considerations! Thank you for your guidance, Niek! Cheerio! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
goal seek minimize function | Excel Worksheet Functions | |||
goal seek function | Excel Discussion (Misc queries) | |||
Why did the goal seek function stop working? | Excel Discussion (Misc queries) | |||
i think i need goal seek or other type of statistic function | Excel Worksheet Functions | |||
Can I use the Goal Seek function on multiple cells simultanously? | Excel Worksheet Functions |