Excel Training

Excel Final Assignment

SALARY STATEMENT FOR THE MONTH OF APRIL

EMPNO	NAME	DESG	Branch	BASIC
1	RAJ	OFFICER	Mumbai	5000
2	RAJESH	CLERK	Mumbai	3500
3	ANAND	MANAGER	Delhi	7000
4	RAJU	CLERK	Delhi	4000
5	HEMANT	MANAGER	Mumbai	8000
6	SANTOSH	CLERK	Delhi	3780
7	BHAUMIK	OFFICER	Delhi	4200
8	MANJIT	OFFICER	Mumbai	5000
9	KAMAL	OFFICER	Delhi	3800
10	SONU	CLERK	Mumbai	2500

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 Vlookup).

3. Calculate HRA as following condition[if ]

4. For Officer 1000, Manager 1500, Clerk 500

5. Calculate Convence 10 % of Basic

6. Calculate Gross [ie Basic + DA +HRA+CONV]

7. Calculate ESIC according to Slabwise on Basic by Vlookup

ie From 0 - 1000 = 50

1001 - 3000 = 100

3001 - 5000 = 250

>5000 = 300

8. Calculate Tax according to Slabwise on Gross by if function

i.e. <10000 = 0

>10000 & <15000 = 750

>15000 = 1500

9. Calculate P.F as 5 % on Basic Salary

10. Calculate Loan 2% of Gross

11. Calculate Net Salary ie [Gross-[ESIC+IT+PF+LOAN]]

12. Draw a graph of Name, Basic, Gross & Net [Graph of 3D & should well formatted]

13. Sort your Record according to Desg wise. [ie Manager then officer and then clerk] [

14. Display only the record of Clerk & Manager

15. Display the Records of Net Salary between 1000 –3000. And 6000-9000 and get the result on a new page

16. Get the total salary branch wise by subtotal

17. Create a Pivot Report Branch wise, Desg wise Net Salary & count the No of Employee.

18. Freeze the Name column & the heading of the data .

19. Highlight the Entire Row where the Designation = Manager

20. Protect the Whole sheet in a such a fashion that I you can make changes in Salary column and hide the formulas

21. Create a Scenario where if you increase the Kamal an Sonu by 2500 then what will be the net Salary.