Module 2: Assignment 2
Making employee salary sheet using MS Excel with the following points:
1. The employee database should have minimum 10 records of individual employees.
2. The fields of the database are:
- Employee ID
- Name
- Date of Birth
- Address
- Date of Joining
- Position Held
- Basic Salary
- HRA
- DA
- TA
- Other Allowances
- Gross Salary
- TDS
- Net Salary
3. The basic salary should be within the range 40k to 80k depending on the employee's situation.
4. HRA should lie between 15%-30% of basic:
- 15% for rural
- 20% for semi-urban
- 30% for urban
5. Travel Allowance (TA): 10% of Basic
6. Medical Allowance: Flat 2000 INR
7. TDS: 4% of Gross Salary
8. Net Salary = Gross Salary - TDS
9. Gross Salary = Basic + HRA + DA + TA + Medical + Other Allowances
Employee Name Date of BirAddress Date of JoiPosition HeBasic SalarHRA DA TA
E100 Amit 32888 Delhi 42125 Manager 44369 13310.7 4436.9 4436.9
E101 Sneha 32314 Mumbai 41897 HR 46809 14042.7 4680.9 4680.9
E102 Ravi 33852 Rural MP 43110 Executive 44194 6629.1 4419.4 4419.4
E103 Priya 34285 Kolkata 42571 Analyst 40429 12128.7 4042.9 4042.9
E104 Arjun 31966 Semi-urba 41338 Clerk 40573 8114.6 4057.3 4057.3
E105 Neha 33311 Delhi 43069 Admin 73129 21938.7 7312.9 7312.9
E106 Karan 34690 Rural Bihar 43641 Technician 40782 6117.3 4078.2 4078.2
E107 Isha 32646 Mumbai 41260 HR 40849 12254.7 4084.9 4084.9
E108 Manav 33147 Chennai 44057 Manager 78568 23570.4 7856.8 7856.8
E109 Tina 33721 Semi-urba 40643 Executive 59777 11955.4 5977.7 5977.7
Medical Other Allo Gross SalarTDS Net Salary
2000 1662 70215.5 2808.62 67406.88
2000 1146 73359.5 2934.38 70425.12
2000 2862 64523.9 2580.956 61942.94
2000 1451 64094.5 2563.78 61530.72
2000 2646 61448.2 2457.928 58990.27
2000 2854 114547.5 4581.9 109965.6
2000 1875 58930.7 2357.228 56573.47
2000 1594 64867.5 2594.7 62272.8
2000 2498 122350 4894 117456
2000 1438 87125.8 3485.032 83640.77
Employee Net Salary
Amit 67406.88
Sneha 70425.12 Net Salary - Pie Chart
Ravi 61942.94
Priya 61530.72 Amit; 67406.88; 9% Amit
Tina; 83640.768; 11%
Arjun 58990.27 Sneha; 70425.12; Sneha
9% Ravi
Neha 109965.6 Manav; 117456; Priya
Karan 56573.47 16% Ravi; 61942.944; Arjun
Isha 62272.8 8% Neha
Manav 117456 Karan
Tina 83640.77 Isha
Isha; 62272.8; 8% Priya; 61530.72; Manav
8% Tina
Karan; Arjun;
56573.472; 8% 58990.272; 8%
Neha; 109965.6; 15%
Net Salary - Column Chart
140000
117456
120000 109965.6
100000
83640.768
8000067406.88
Net Salary
70425.12
61942.944
61530.72 62272.8
58990.27256573.472 Net Salary
60000
40000
20000
0
AmitSneha Ravi PriyaArjunNehaKaran IshaManavTina
Employee
Amit
Sneha
Ravi
Priya
4; Arjun
Neha
Karan
Isha
Manav
Tina
.768
Net Salary