Consulting

Results 1 to 4 of 4

Thread: Salary Structure Calculations

  1. #1
    VBAX Newbie
    Joined
    May 2018
    Posts
    2
    Location

    Salary Structure Calculations

    Dear Experts,

    I need to design a macro w.r.t Salary Structure in excel as per following requirement.

    Current Salary Structure is follows:-


    S.No. Category Emp. Name Basic HRA Conv. Med. CCA SPA Take Home Bonus EPF ESIC CTC
    1 1 RK 24,115 12,058 7,235 - 7,235 34,392 85,035 4823 2894 0 92,752
    2 2 MSR 8,200 4,100 2,460 1,640 2,119 - 18,519 1640 984 787 21,930
    3 2 DS 8,200 4,100 2,460 1,640 418 - 16,818 1640 984 715 20,157
    4 1 NS 8,200 4,100 2,460 1,640 - 976 17,376 1640 984 738 20,738
    5 2 RA 8,200 4,100 2,460 1,640 - 976 17,376 1640 984 738 20,738
    6 1 EB 8,200 4,100 2,460 1,640 - 976 17,376 1640 984 738 20,738

    I wish to keep new Salary Structure as follows:-

    % Increment New CTC Gross Increase New Basic New HRA New Conv. New Med. New CCA New SPA New Take Home New Bonus New EPF New ESIC Total CTC

    5% 97,390 4,638 25,321 12,661 7,596 5,064 7,596 31,048 89,287 5,064 3,039 - 97,390








    Following should be the basis of Calculations: -
    New CTC
    "CTC" + "CTC"*%i Increment

    Gross Increase
    CTC - New CTC

    New Basic
    IF Category = 1 Then Max of 9200 OR 26% of New CTC
    IF Category = 2 Then Max of 8200 OR 26% of New CTC
    IF Category = 3 Then Max of 7800 OR 26% of New CTC


    New HRA
    IF((New CTC - (New Basic +New Bonus+New EPF + New ESIC))>0
    then
    Min of ((New CTC - (New Basic + New Bonus+New EPF + New ESIC), 50% of New Basic)


    New Conv.
    IF((New CTC - (New Basic + New HRA+New Bonus+New EPF + New ESIC))>0
    then
    Min of ((New CTC - (New Basic + New HRA+New Bonus+New EPF + New ESIC), 30% of New Basic)


    New Med.
    IF((New CTC - (New Basic + New HRA+New Conv. + New Bonus+New EPF + New ESIC))>0
    then
    Min of ((New CTC - (New Basic + New HRA+New Conv. +New Bonus+New EPF + New ESIC), 20% of New Basic)


    New CCA
    IF((New CTC - (New Basic + New HRA+New Conv. + New Med. + New Bonus+New EPF + New ESIC))>0
    then
    Min of ((New CTC - (New Basic + New HRA+New Conv. + New Med. +New Bonus+New EPF + New ESIC), 30% of New Basic)





    New SPA
    IF((New CTC - (New Basic + New HRA+New Conv. + New Med. + New CCA + New Bonus+New EPF + New ESIC))>0
    then
    New CTC - (New Basic + New HRA+New Conv. + New Med. + New CCA+New Bonus+New EPF + New ESIC)



    New Take Home
    Sum of New Basic + New HRA + New Conv. + New Med. + New CCA + New SPA

    New Bonus
    20% of New Basic

    New EPF
    12% of New Basic

    New ESIC
    IF "New Take Home" < 21000
    Then
    "New Take Home"*4.25%
    Else
    0


    Total CTC
    Sum of New Take Home + New Bonus + New EPF + New ESIC

    While applying formulas in excel it is giving circular error therefore need to have a macro. I wish of to enter only the % of Increment and the succeeded columns should get calculated automatically as per above logics specified. The Macro should get clicked/executed as soon as I enter/change the % of increment.

    To start with We take the simplest example as follows wherein the NewCTC is Rs. 15793

    New CTC New Basic New HRA New Total as per Salary Sheet New Bonus New EPF New ESIC Total CTC
    15,793 9,200 3,125 12,325 1,840 1,104 524 15,793

    Since the following fields can be calculated straight forwardly therefore we will calculate them first

    New Basic (For Category 1)
    IF Category = 1 Then Max of 9200 OR 26% of New CTC
    IF Category = 2 Then Max of 8200 OR 26% of New CTC
    IF Category = 3 Then Max of 7800 OR 26% of New CTC

    Caculated as Rs. 9200/-


    New Bonus
    20% of New Basic
    Calculated as Rs. 1840/-


    New EPF
    12% of New Basic
    Calcuated as Rs. 1104/-


    Now there are 2 fields left (New HRA and New ESIC) which are creating circular references and are posing difficulties in calculation. We know that the total of all the above fields is Rs. 12,144/- and the HRA & ESIC to be calculated in such a way that in totality they should match the CTC value i.e. Rs. 15793/-. In above case the value of HRA is Rs. 3125/- and value of ESIC is Rs. 524/- so that in totality the CTC is coming out to be Rs. 15793/-

    If we can insert the calculation of NewHRA in a loop (by using various values) and thereafter we calculate the value of ESIC within loop only and check the total of all the calculated fields with NewCTC. If it matches we freeze the values of HRA and ESIC and exit the loop but if it doesnot in that case we again go to the start of the loop and again follow the above steps within loop untill we meet the required condition.

    if we are able to calculate the above values accurately we can consider remaining 5 cases in similar way.

    Hope I am clear and not mistaken. Please advice.
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    You left out a bunch Elses.
    eg If Blah Blah > 0 Then
    Bleh Bleh
    ElseIf Blah Blah < 0 Then... What?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Newbie
    Joined
    May 2018
    Posts
    2
    Location
    Thanks for the info. will take care in future.

Posting Permissions

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