Excel Final Assignment
SALARY STATEMENT FOR THE MONTH OF APRIL
Emp Code Employee Name Deptt Name Location DOJ Designation Basic
E104 Namita Mishra IT Delhi 10-Sep-10 Assistant Manager 38000
E116 Legal Delhi 20-Mar-11 Assistant Manager 38000
Ulrica Paul
E126 IT Bangalore 28-May-11 Assistant Manager 38000
Deepa Sharma
E134 Client Servicing Mumbai 12-Aug-11 Assistant Manager 38000
Mangala Goel
E155 Mukeh Butani Client Servicing Mumbai 10-Mar-13 Assistant Manager 38000
E156 Nitin Baijal Client Servicing Delhi 10-Mar-13 Assistant Manager 38000
E158 Parul Jain Client Servicing Delhi 10-Mar-13 Assistant Manager 38000
E160 Priyanka Sahi Client Servicing Delhi 15-Apr-13 Assistant Manager 38000
E102 Rupesh Malik Marketing Mumbai 10-Sep-10 Executive 40000
E108 Sandeep Goel Marketing Delhi 10-Sep-10 Executive 40000
E109 Vivek Gupta Human Resources Delhi 10-Sep-10 Executive 40000
E110 Ruchika Sarawat IT Delhi 14-Jan-11 Executive 40000
E111 Sumit Narwankar Marketing Delhi 14-Jan-11 Executive 40000
E117 Procurement Mumbai 20-Mar-11 Executive 40000
Suresh Krishnan
E119 Administration Mumbai 20-Mar-11 Executive 40000
Rashmi Gupta
E121 Marketing Bangalore 25-May-11 Executive 40000
Laxman Babu
E122 Marketing Bangalore 26-May-11 Executive 40000
Ameya Kapoor
E123 Marketing Bangalore 27-May-11 Executive 40000
Aarti Mehra
E124 Finance Bangalore 28-May-11 Executive 40000
Tanya D'Silva
E127 Client Servicing Mumbai 28-May-11 Executive 40000
Anita Malhotra
E128 Client Servicing Mumbai 28-May-11 Executive 40000
Manisha Kishore
E129 Client Servicing Mumbai 28-May-11 Executive 40000
Raj Kumar
E130 Client Servicing Mumbai 28-May-11 Executive 40000
Harpreet Singh
E131 Client Servicing Mumbai 12-Aug-11 Executive 40000
Varun Chopra
E132 Client Servicing Mumbai 12-Aug-11 Executive 40000
Vikas Sahni
E135 Client Servicing Mumbai 12-Aug-11 Executive 40000
Shyamli Biswas
E136 Client Servicing Delhi 12-Aug-11 Executive 40000
Sucheta Ghosh
E137 Client Servicing Delhi 18-Dec-11 Executive 40000
Debashree Basu
E138 Client Servicing Delhi 19-Dec-11 Executive 40000
Priyank Ahluwalia
E142 Client Servicing Delhi 20-Dec-11 Executive 40000
Varsha Chawla
E143 Client Servicing Delhi 20-Dec-11 Executive 40000
Dikshit Kapoor
E144 Client Servicing Delhi 20-Dec-11 Executive 40000
Farooque Ahmed
E145 Client Servicing Delhi 15-Jan-12 Executive 40000
Brian Dsouza
E146 Client Servicing Delhi 15-Jan-12 Executive 40000
Vidya Srinivasan
E147 Client Servicing Bangalore 15-Jan-12 Executive 40000
Kadambari Gopinath
E148 Client Servicing Bangalore 15-Jan-12 Executive 40000
Rishu Shukla
E149 Client Servicing Bangalore 15-Jan-12 Executive 40000
Richard Nolan
E150 Client Servicing Bangalore 15-Jan-12 Executive 40000
Abhijit Kapoor
E157 Nitin Balhara Client Servicing Mumbai 10-Mar-13 Executive 40000
E159 Prerna Mehndiratta Client Servicing Bangalore 10-Mar-13 Executive 40000
E161 Puja Nalam Client Servicing Bangalore 15-Apr-13 Executive 40000
E162 Puneet Gupta Client Servicing Bangalore 15-Apr-13 Executive 40000
E164 Sahiba Tandon Client Servicing Delhi 15-Apr-13 Executive 40000
E167 Shuchi Chawla Client Servicing Bangalore 29-May-13 Executive 40000
E169 Shweta Kashyap Client Servicing Delhi 15-Jun-13 Executive 40000
E170 Sumit Singhania Client Servicing Delhi 15-Jun-13 Executive 40000
E171 Vibhuti Pahwa Client Servicing Delhi 15-Jun-13 Executive 40000
E172 Vimi Gupta Client Servicing Delhi 15-Jun-13 Executive 40000
E173 Vishal Gupta Client Servicing Delhi 2-Jul-13 Executive 40000
E174 Vivek Kataria Client Servicing Delhi 2-Jul-13 Executive 40000
E101 Rahul Jain Finance Delhi 8-Sep-10 Manager 50000
E115 Legal Mumbai 19-Mar-11 Manager 50000
Snehangshu Goel
E118 Administration Mumbai 20-Mar-11 Manager 50000
Sandesh Adjani
E139 Client Servicing Delhi 20-Dec-11 Manager 50000
Sayantani Banerjee
E153 Client Servicing Delhi 18-Dec-12 Manager 50000
Ketan Malhotra
E154 Mohit Agarwal Client Servicing Bangalore 18-Dec-12 Manager 50000
E163 Rahul Gupta Client Servicing Bangalore 15-Apr-13 Manager 50000
E175 Yatin Sharma Client Servicing Delhi 2-Jul-13 Manager 50000
E103 Sandhya Menon Human Resources Bangalore 10-Sep-10 Sr. Executive 50000
E113 Marketing Delhi 14-Jan-11 Sr. Executive 50000
Rekha Kujur
E125 Human Resources Bangalore 28-May-11 Sr. Executive 50000
Abhijit Manohar
E105 Sanjana Paul Legal Delhi 10-Sep-10 Sr. Manager 55000
E106 Abhishek Sharma Procurement Delhi 10-Sep-10 Sr. Manager 55000
E107 Atul Parashar Marketing Delhi 10-Sep-10 Sr. Manager 55000
E120 Marketing Bangalore 20-Mar-11 Sr. Manager 55000
Arun Sharma
E133 Client Servicing Mumbai 12-Aug-11 Sr. Manager 55000
Kushal Chand Dever
E151 Client Servicing Bangalore 22-Mar-12 Sr. Manager 55000
Rishi Sharma
E152 Client Servicing Mumbai 22-Mar-12 Sr. Manager 55000
Shubhra Sehgal
E112 Ameena Khan Marketing Delhi 14-Jan-11 Vice President 100000
80000
E114 Human Resources Delhi 14-Jan-11 Vice President
Rina Malik
E140 Client Servicing Delhi 20-Dec-11 Vice President 80000
Bikram Bansal
E141 Client Servicing Bangalore 20-Dec-11 Vice President 80000
Geetika Sahni
E165 Sandeep Gupta Client Servicing Bangalore 15-Apr-13 Vice President 80000
E166 Sanjiv Malhotra Client Servicing Delhi 24-May-13 Vice President 80000
E168 Shweta Aggarwal Finance Bangalore 1-Jun-13 Vice President 80000
1. Copy the data and Paste in Excel & format it .Then solve the below Question
2. Calculate Conveyance 10 % of Basic [Using Absolute cell reference]
3. Calculate HRA as following condition [If condition]
For Delhi 10000, Mumbai 15000, Bangalore 12000,
4. Calculate Travelling Allowance(TA) of Basic (using Vlookup).
Vice President 25%
Sr. Manager 20%
Manager 15%
Assistant manager 10%
Sr. Executive 7%
Executive 5%
5. Calculate Gross [ie Basic + TA +HRA+CONV]
6. Calculate P.F as 5 % on Basic Salary
7. Calculate Loan 2% of Gross
8. Calculate Net Salary ie [Gross- [PF+LOAN]]
9. Draw a graph of Name, Basic, Gross & Net [Should well formatted] of 10 records
10. Sort your Record according to Location wise. [ie Delhi, Mumbai, Bangalore]
11. Display only the record of Delhi or Mumbai
12. Split the full name into first name and last name.
13. Get the total salary branch wise by subtotal
14. Create a Pivot Report Branch wise, Desg wise Sum Net Salary, Count the No of Employee, and % of Salary.
15. Freeze the Name column & the heading of the data.
16. Highlight the cells where the Designation = Manager
17. Protect the Whole sheet in a such a fashion that you can make changes in Salary column and hide the
formulas.
