Structuring the Compensation

[Raghunathan]

Hi,

Need formulae to calculate 2 components automatically on certain conditions as per the illustrative table mentioned below.

- Comp1 & Comp4 or same as existing even after increase
- The value of Comp1 & Comp2 is exceeded to 21000, then comp2 to value should be the difference of 21000 and Comp1 or continue with existing value
- Comp3 is 28.5% of total if the value of comp1 & Comp2 is below 21000

	Existing							Expected Results
	Comp1	Comp2	Comp3	Comp4	Total	Incremental value	Revised Comp	Comp1	Comp2	Comp3	Comp4	Total
Type1	12,992.00	5,898.00	8,668.00	-	27,558.00	2,170.00	29,728.00	12,992.00	8,242.00	8,494.00	-	29,728.00
Type2	23,884.00	5,504.00	45,859.00	1,250.00	76,497.00	4,930.00	81,427.00	23,884.00	5,504.00	50,789.00	1,250.00	81,427.00
Type3	15,378.00	4,712.00	9,577.00	1,250.00	30,917.00	2,400.00	33,317.00	15,378.00	5,622.00	11,067.00	1,250.00	33,317.00
									Need Formula	Need formula

 

[p45cal]

I am not able to understand your requirement

Same here!

 

[Raghunathan]

Need formula to calculate the following fields - Comp2 & Comp3 cells.

Am trying to build compensation design for the company and need some help to compute.

Three type of employees I have taken for illustration and given their existing compensation design with their current compensation breakup and expected amount post increase/increment which distributed between comp2 & Comp3.

Type 1 : Existing compensation of comp1 & Comp2 is below 21000 before increase and continue to below 21000 post increase also.

Type2 : Existing Compensation of Comp1 & Comp2 is above 21000

Type 3 : Existing compensation is below 21000 at the moment but post increase this may go beyond 21000.

The formula to derive on 3 different scenarios.

How I require the revised compensation is ;

• Comp1 & Comp4 is same amount as per the existing compensation design post increase also.
• On a scenario wherein Comp1 & Comp2 total is less than 21000, then Comp3 value is 28.5% of overall compensation (Total mentioned in the grid)
• In case the comp1 & Comp2 total is already above 21K, all incremental values will be in comp3
• In case of Type 3 employees , the increase will be distributed in Comp2 & comp3 until comp2 reaches 21000. Once reaches 21K the entire balance amount can be loaded in Comp3. Or else scenario (b) is applicable.

Trust this clarifies

Thanks

Raghu

 

[mohamed ilyas]

hi

check this..

still i did not get your point because.. type 1 itself getting above 21000 if we add incremental value.. hence
type 1 & type 2 formula will be matched, type 3 separate formula.... just illustrated based on your result only..