PDA

View Full Version : Salary Structure Calculations



mpapreja
05-10-2018, 01:45 AM
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.

p45cal
05-13-2018, 09:19 AM
Cross posted without links at:
http://www.msofficeforums.com/excel-programming/39025-salary-structure-calculations.html
https://stackoverflow.com/questions/50246579/salary-structure-calculations

Have a read of http://www.excelguru.ca/content.php?184
It's for your own benefit.

SamT
05-13-2018, 01:10 PM
You left out a bunch Elses.
eg If Blah Blah > 0 Then
Bleh Bleh
ElseIf Blah Blah < 0 Then... What?

mpapreja
05-14-2018, 08:29 AM
Thanks for the info. will take care in future.