Challenging the GOAL SEEK function within a bonus scheme

[ran58]

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 D413 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!

 

[Niek Otten]

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

 

[Niek Otten]

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

 

[ran58]

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

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é

 

[ran58]

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!

 

[Niek Otten]

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

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!

 

[ran58]

Now it becomes difficult! If you allow the ratios between the percentagesto
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






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!