ASSIGNMENT 50

FINAL EXCEL PRACTICAL PROJECT

EMPLOYEE PERFORMANCE & SALARY ANALYSIS

Objective

Create a complete Employee Performance & Salary Analysis Report using Excel.

In this project, you will use:

• IF

• Nested IF

• AND / OR

• IFERROR

• COUNTIF / COUNTIFS

• SUMIF / SUMIFS

• AVERAGEIF / AVERAGEIFS

• VLOOKUP

• XLOOKUP

• INDEX + MATCH

• DATEDIF

• LEFT

• FIND

• LEN

• LOWER / PROPER

• LARGE / SMALL

• NETWORKDAYS

• Conditional Formatting

PART A – EMPLOYEE DATA

Enter the following data in Excel:

ID	Employee Name	Department	City	Joining Date	Salary	Experience	Sales	Attendance %	Rating	Training	Target
101	Aarav Sharma	Sales	Sirsa	15-05-2022	35000	3	45000	92	4.2	Completed	50000
102	Meera Kapoor	HR	Hisar	20-08-2021	42000	5	38000	88	4.5	Completed	40000
103	Rahul Verma	Sales	Sirsa	10-07-2019	50000	7	52000	95	4.8	Completed	50000
104	Pooja Singh	IT	Delhi	25-03-2022	46000	4	60000	96	4.9	Completed	55000
105	Karan Mehta	HR	Hisar	12-11-2023	38000	2	30000	75	3.2	Pending	40000
106	Simran Kaur	Sales	Sirsa	18-06-2020	55000	6	48000	90	4.5	Completed	50000
107	Mohit Kumar	IT	Delhi	05-02-2018	62000	8	72000	94	4.9	Completed	65000
108	Neha Gupta	HR	Hisar	22-09-2022	44000	4	42000	91	4.6	Completed	40000
109	Arjun Verma	Sales	Sirsa	30-01-2025	48000	1	28000	68	2.9	Pending	35000
110	Priya Sharma	IT	Delhi	14-06-2021	52000	5	55000	89	4.4	Completed	50000
111	Rohan Mehta	Sales	Sirsa	01-04-2017	65000	9	65000	97	4.9	Completed	60000
112	Ananya Singh	HR	Hisar	10-10-2020	47000	6	47000	93	4.7	Completed	45000
113	Vikas Kumar	IT	Delhi	15-01-2023	40000	3	40000	82	3.5	Pending	45000
114	Riya Kapoor	Sales	Sirsa	20-05-2022	51000	4	51000	94	4.7	Completed	50000
115	Nitin Sharma	HR	Hisar	14-12-2019	35000	7	35000	80	3.4	Pending	40000

PART B – CALCULATIONS

Add the following columns after the above data.

Column M – Target Status

If Sales >= Target:

Achieved

Otherwise:

Not Achieved

Use IF.

Column N – Achievement %

Calculate:

Sales ÷ Target × 100

Formula:

=H2/L2*100

Column O – Performance Category

Use these conditions:

• Rating >= 4.8 → Outstanding

• Rating >= 4.5 → Excellent

• Rating >= 4.0 → Good

• Rating >= 3.0 → Average

• Below 3.0 → Needs Improvement

Use Nested IF.

Column P – Bonus %

Use:

• Sales >= 60,000 → 15%

• Sales >= 50,000 → 10%

• Sales >= 40,000 → 7%

• Sales >= 30,000 → 5%

• Below 30,000 → 0%

Column Q – Bonus Amount

Calculate:

Salary × Bonus %

Column R – Promotion Status

Employee is eligible if:

• Experience >= 5

• Sales >= Target

• Attendance >= 90%

• Rating >= 4.5

Use:

IF + AND

Return:

• Promotion Recommended

• Promotion Not Recommended

Column S – Attendance Status

Use Nested IF:

• = 90 → Excellent

• = 80 → Good

• < 80 → Poor

Column T – Training Status

If Training is Completed:

Eligible

Otherwise:

Training Required

PART C – LOOKUP PRACTICE

Create a separate Lookup Sheet.

Enter these Employee IDs:

103
106
110
114
115
120

Q1.

Use XLOOKUP to find Employee Name.

Q2.

Use XLOOKUP to find Department.

Q3.

Use XLOOKUP to find Salary.

Q4.

Use XLOOKUP to find Rating.

Q5.

Use XLOOKUP with a custom message:

Employee Not Found

for ID 120.

Q6. INDEX + MATCH

Use INDEX + MATCH to find the salary of:

Rohan Sharma/Mehta (Employee ID 111)

Formula structure:

=INDEX(F2:F16,MATCH(111,A2:A16,0))

PART D – SALES ANALYSIS

Q7.

Calculate total Sales of all employees.

Use:

=SUM(H2:H16)

Q8.

Calculate total Sales of the Sales Department.

Use SUMIF.

Q9.

Calculate total Sales of employees from Sirsa.

Use SUMIF.

Q10.

Calculate total Sales of Sales Department employees from Sirsa.

Use SUMIFS.

PART E – AVERAGE ANALYSIS

Q11.

Calculate average Salary of all employees.

Q12.

Calculate average Salary of the Sales Department.

Use AVERAGEIF.

Q13.

Calculate average Sales where Attendance is >= 90%.

Use AVERAGEIF.

Q14.

Calculate average Rating of employees whose Sales are >= ₹50,000.

Use AVERAGEIF.

PART F – COUNT ANALYSIS

Q15.

Count total employees.

Q16.

Count employees whose Target Status is Achieved.

Q17.

Count employees from the Sales Department.

Q18.

Count employees whose:

• Sales >= ₹50,000

• Attendance >= 90%

Use COUNTIFS.

Q19.

Count employees whose:

• Department = Sales

• Rating >= 4.5

• Training = Completed

Use COUNTIFS.

PART G – AGE / SERVICE CALCULATION

Assume the current date is:

07-10-2026

Q20.

Calculate each employee’s completed years of service using DATEDIF.

Formula:

=DATEDIF(E2,DATE(2026,10,7),"y")

Q21.

Calculate completed months of service.

=DATEDIF(E2,DATE(2026,10,7),"ym")

Q22.

Create a Service Duration statement such as:

“Aarav has completed 4 years and 4 months of service.”

Use & with DATEDIF.

PART H – TEXT FUNCTIONS

Q23.

Extract the first name from Employee Name.

Use:

=LEFT(B2,FIND(" ",B2)-1)

Q24.

Convert Employee Names into lowercase.

Use:

=LOWER(B2)

Q25.

Calculate the length of each Employee Name.

Use:

=LEN(B2)

PART I – TOP & LOWEST PERFORMANCE

Q26.

Find the highest Sales.

Use:

=LARGE(H2:H16,1)

Q27.

Find the second-highest Sales.

Q28.

Find the third-highest Sales.

Q29.

Find the lowest Sales.

Use:

=SMALL(H2:H16,1)

Q30.

Find the second-lowest Sales.

PART J – FINAL PERFORMANCE DASHBOARD

Create a small dashboard containing:

KPIs

1. Total Employees

2. Total Sales

3. Average Salary

4. Highest Sales

5. Lowest Sales

6. Achieved Targets

7. Not Achieved Targets

8. Average Rating

9. Promotion Recommended

10. Training Required

PART K – CONDITIONAL FORMATTING

Apply Conditional Formatting:

1. Sales

Highlight Sales:

>= ₹50,000

2. Attendance

Highlight Attendance:

< 80%

3. Rating

Highlight Rating:

>= 4.8

4. Target Status

• Achieved → Green

• Not Achieved → Red

5. Promotion

• Promotion Recommended → Green

• Promotion Not Recommended → Red

PART L – FINAL QUESTIONS

Q31.

Who has the highest Sales?

Q32.

Who has the lowest Sales?

Q33.

Which department has the highest total Sales?

Q34.

How many employees achieved their targets?

Q35.

How many employees are eligible for promotion?

Q36.

How many employees have Rating >= 4.5?

Q37.

Which employee has the highest Rating?

Q38.

Which employee has the highest Salary?

Q39.

How many employees have Attendance >= 90%?

Q40.

How many employees require training?

FINAL PROJECT CHALLENGE

Create a Professional Employee Performance Dashboard containing:

• Total Employees

• Total Sales

• Average Salary

• Average Rating

• Target Achievement %

• Highest Sales

• Top Performer

• Promotion Recommended

• Training Required

• Department-wise Sales

• Performance-wise Employee Count

Use:

Tables + Formulas + Conditional Formatting + Charts

Recommended charts:

1. Department-wise Sales → Column Chart

2. Performance Category → Pie/Doughnut Chart

3. Employee Sales → Bar Chart

4. Target Achievement → Column Chart

PROJECT RULE

Do not manually type the answers.

Every calculated result must be generated using an Excel formula.

This is your FINAL PRACTICAL PROJECT, so try to build the complete workbook independently before checking the answer key.