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