Excel Part-1 Assignment
SALARY STATEMENT FOR THE MONTH OF APRIL
EMPNO NAME DESG Branch BASIC DA(Ans 2)
1 RAJ OFFICER Mumbai 5000
500
2 RAJESH CLERK Mumbai 3500
175
3 ANAND MANAGER Delhi 7000 1050
4 RAJU CLERK Delhi 4000 200
5 HEMANT MANAGER Mumbai 8000 1200
6 SANTOSH CLERK Delhi 3780
189
7 BHAUMIK OFFICER Delhi 4200
420
8 MANJIT OFFICER Mumbai 5000 500
9 KAMAL OFFICER Delhi 3800
380
10 SONU CLERK Mumbai 2500 125
1. Copy the data and Paste in Excel & format it .Then solve the below Question
2. Calculate DA for Officer 10%, Manager 15%, & Clerk 5% of Basic (using Nested IF).
3. Calculate HRA as following condition
For Officer 1000, Manager 1500, Clerk 500
4. Calculate Convence 10 % of Basic
5. Calculate Gross [ie Basic + DA +HRA+CONV]
6. Calculate ESIC according to Slabwise on Basic by Nested IF
i.e., From 0 - 1000 = 50 0-1000=50
1001 - 3000 = 100 1001-3000=100
3001 - 5000 = 250 3001-5000=250
>5000 = 300 >5000=300
7. Calculate Tax according to Slabwise on Gross by if function
i.e. <10000 = 0
>10000 & <15000 = 750
>15000 = 1500
8. Calculate P.F as 5 % on Basic Salary
9. Calculate Loan 2% of Gross
10. Calculate Net Salary ie [Gross-[ESIC+IT+PF+LOAN]]
HRA Conveyance Gross ESIC Tax
0 500 6000 300
500 350 4525 250
500 700 9250 300 i.e. <10000
500 400 5100 250 >10000 & <150
500 800 10500 300 >15000 =
500 378 4847 250
500 420 5540 250
500 500 6500 300 For Officer 1000, M
500 380 5060 250
500 250 3375 100
sing Nested IF).
01-3000=100
01-5000=250
i.e. <10000 = 0
>10000 & <15000 = 750
>15000 = 1500
For Officer 1000, Manager 1500, Clerk 500
Ans 2
Nested IF
