Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Max Increase (Excel 2003)

    Hi Excel Experts,

    I am looking for a formula to fix the max increase i need to give to an employee. I am attaching the file for your reference.

    The file has three sheets
    Benchmark-07 - Has the median Salary details for all levels
    Matrix - Has the Increment Matrix
    Master Data - The the employee data.

    Col F - Is the current Salary, Col G - Pre increase Compa (Current salary/Benchmark for the level), Col H is the Proposed Increment % based on Col E & G. Col I - New Salary, Col J - New Compa (New Salary/Benchmark for the level)

    I need a formula in Col H over and above the existing formula. If Col G is > 115% then 0% Increase. However based on the matrix if the new increase is less than 120% Compa, the differential should be put in Cashout i.e. Col K.
    Also, if Col G is < 115% and the recommended increase is >115%, then 115% and the differentials should be put in Col K, the amount not exceeding 120% i.e. 115% + Cashout not > 120%

    For Eg if Col G is 105% and the Recommended Increase is 15% (Col H), the Actual Output should Give an Increase upto 115% i.e. 10% and the differential as cashout not > 120%

    Hope my question is clear, else let me know if you have any clarification.

    Looking forward to a great and simple formula :-).

    Regards
    Baiju
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Max Increase (Excel 2003)

    What do you mean by "However based on the matrix if the new increase is less than 120% Compa, the differential should be put in Cashout i.e. Col K."? What differential?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Max Increase (Excel 2003)

    Hi Rory,

    If the employee is >115% and is T20 under Technology the employee will get 6.3% increase, which will take him to 121.3%, however would like to restrict the Increase to 0%, the differential between 115 to 120 to be put in Cashout.

    Hope this clarifies.

    Regards
    Baiju

  4. #4
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Max Increase (Excel 2003)

    Hi Rory,

    Hope this example helps.

    For those who are currently above 115% compa ratio (prior to any increase), a Cashout (not more than) 5% is to be applied (if they’re at 117% then a 3% Cashout will apply)
    For those whose compa after increases is above 115% increase to max 115% balance Cashout not greater than 120%.
    For example: Mr. X is currently on 112% compa, he is a 2A and the increase recommended is 7%- apply just a 3% TEC increase and the balance as a cash out (but not beyond 5% or 120%)
    For those above 120% at any stage will not receive an increase or payout

    Regards
    Baiju

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Max Increase (Excel 2003)

    Let's look at line 15 specifically. Pre increase compa is 101.4%. Implied increase is 13.9% but that would give a new Compa of 115.5%. Should the increase be used at 13.9% because 13.9% is less than 15%, or should the increase be capped at a level that makes the final compa 115% exactly, with the balance going in to the cashout column?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Max Increase (Excel 2003)

    Does this do what you want?
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Max Increase (Excel 2003)

    Hi Rory,

    At the out look like you have got what i exactly was looking at... thanx a ton... i have close to 1500 employee.. i shall use this formula and see if it works fine.

    You have made my job very easy.

    Thanks a ton.

    Regards
    Baiju

  8. #8
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Max Increase (Excel 2003)

    Hi Rory,

    Thanks for the formula, however a small tweeking needs to be done.. if current compa > 115% & Proposed Increase is Zero as per the Matrix, then Zero NO Cashout
    AND if Current compa >115% and Proposed Increase is > Zero, then Cashout upto 120% Compa

    Also in some cases, the new increase + Cashout is greater than the proposed increase without the upper limit of 115% compa. and in some cases Increment + Cashout is greater than120% compa.

    I have attached a file with the earlier formula without the Max 115% compa and your proposed formula.

    Regards
    Baiju
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •