Curriculum
Excel Mastery 1.0 to 3.0 + AI (Complete Course) ⭐
Excel Beginner to Job Ready Excel 1.0
0/0
Excel All Shortcut Keys
0/1
🎯 Excel Interview Questions (2026) – With Answers
0/4
Excel 1.0 1st Week
0/9
Excel 1.0 2nd Week
0/5
Excel 1.0 3rd Week
0/5
Download practice data for Excel
0/2
Complete Payroll Practical Assignment (Excel)
📊 Complete Payroll Practical Assignment (Excel)
🧾 Question Title:
Employee Payroll System with Real-Time Calculation (10 Employees Data Included)
📌 Step 1: Excel Me Ye Table Create Kare
|
Emp ID |
Name |
Basic Salary |
HRA % |
DA % |
PF % |
ESI % |
OT Hours |
OT Rate |
📊 Step 2: Niche Diya Gaya Data Enter Kare (10 Employees)
|
Emp ID |
Name |
Basic Salary |
HRA % |
DA % |
PF % |
ESI % |
OT Hours |
OT Rate |
|
101 |
Amit |
18000 |
20% |
10% |
12% |
0.75% |
8 |
100 |
|
102 |
Neha |
22000 |
20% |
10% |
12% |
0.75% |
12 |
120 |
|
103 |
Rahul |
15000 |
20% |
10% |
12% |
0.75% |
5 |
90 |
|
104 |
Priya |
25000 |
20% |
10% |
12% |
0.75% |
15 |
150 |
|
105 |
Suresh |
12000 |
20% |
10% |
12% |
0.75% |
6 |
80 |
|
106 |
Pooja |
30000 |
20% |
10% |
12% |
0.75% |
10 |
140 |
|
107 |
Rakesh |
17000 |
20% |
10% |
12% |
0.75% |
9 |
110 |
|
108 |
Kavita |
21000 |
20% |
10% |
12% |
0.75% |
11 |
130 |
|
109 |
Mohan |
14000 |
20% |
10% |
12% |
0.75% |
7 |
95 |
|
110 |
Anjali |
26000 |
20% |
10% |
12% |
0.75% |
13 |
160 |
📌 Step 3: New Columns Add Kare (Calculation Ke Liye)
| HRA Amt | DA Amt | Bonus | OT Amt | Gross Salary | PF Amt | ESI Amt | Total Deduction | Net Salary |
🔢 Step 4: Formulas Apply Kare
✔ HRA Amount
= Basic Salary × HRA %
✔ DA Amount
= Basic Salary × DA %
✔ OT Amount
= OT Hours × OT Rate
✔ Bonus (IF Condition)
=IF(Basic>=20000,2000,1000)
✔ Extra Rule (Advanced)
👉 Agar OT Hours > 10 ho to ₹1000 extra add kare:
=IF(OT Hours>10,1000,0)
👉 Isko Bonus me add kar sakte hain
✔ Gross Salary
= Basic + HRA + DA + Bonus + OT
✔ PF Amount
= Basic × 12%
✔ ESI Amount
= Gross × 0.75%
✔ Total Deduction
= PF + ESI
✔ Net Salary
= Gross – Total Deduction
🎯 Step 5: Advanced Excel Practice
✔ Conditional Formatting
- Net Salary < 15000 → Red
✔ Sort
- Highest Net Salary
✔ Filter
- Salary > 20000
✔ Data Validation
- OT Hours ≤ 50
✔ Pivot Table
- Total Salary Summary
⏱ Real Learning Outcome
Is assignment se student seekhega:
✔ Real Payroll System
✔ IF Formula
✔ Percentage Calculation
✔ Logical Thinking
✔ Excel Tools (Sort, Filter, Pivot)
🎓 Bonus Task (Pro Level)
👉 Student Task:
- Salary Slip Design kare
- Dashboard banaye (Total, Avg, Highest Salary)
