\"Writing.Com
*Magnify*
SPONSORED LINKS
Printed from https://writing.com/main/view_item/item_id/2022705-Excel-Training
Item Icon
\"Reading Printer Friendly Page Tell A Friend
No ratings.
by Ravi Author IconMail Icon
Rated: E · Other · Other · #2022705
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

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

i.e. <10000 = 0

>10000 & <15000 = 750

>15000 = 1500

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

  2. Calculate Loan 2% of Gross

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

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

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

  6. Display only the record of Clerk & Manager

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

  1. Get the total salary branch wise by subtotal

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

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

  4. Highlight the Entire Row where the Designation = Manager

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

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


© Copyright 2014 Ravi (ravikushwaha at Writing.Com). All rights reserved.
Writing.Com, its affiliates and syndicates have been granted non-exclusive rights to display this work.
Printed from https://writing.com/main/view_item/item_id/2022705-Excel-Training