Employee Data Analysis using Excel

STUDENT NAME: Sham.s

REGISTER NO: 22BIS033(asunm422200140)
DEPARTMENT: BCOM(information system management )
COLLEGE: MOHAMED SATHAK COLLEGE ARTS AND SCIENCE

1
PROJECT
TITLE
Employee Performance Analysis
using Excel

2
AGEN
DA [Link] Statement
[Link] Overview
[Link] Users
[Link] Solution and Proposition
[Link] Description
[Link] Approach
[Link] and Discussion
[Link]

3/21/2024 Annual 3
Review
PROBLEM
STATEMENT
• Data Collection: Gathering relevant information such as
performance metrics, attendance records, feedback
surveys, and demographic details.
• Data Cleaning and Preparation: Ensuring data accuracy
and consistency by removing errors, duplicates, and
irrelevant information.
• Data Analysis: Using statistical methods, visualization
tools, and analytical techniques to uncover patterns,
trends, correlations, and anomalies within the data.
• Interpretation and Insight Generation: Drawing
conclusions and actionable insights from the analyzed
data to support decision-making, improve HR practices,
and optimize workforce management strategies.
• Application: Implementing findings to enhance
employee engagement, productivity, retention, and
overall organizational performance.

4
PROJECT
OVERVIEW
•. Collect and integrate employee data from various sources (e.g., HR systems,
surveys, performance reviews)
- Clean, transform, and prepare data for analysis- Develop Excel dashboards and
reports to visualize key metrics, including: - Demographics (age, tenure,
department, etc.) - Performance ratings and trends

5
WHO ARE THE
END USERS?
Informed Decision-Making:
•Provides managers and executives with data-driven
insights to make strategic decisions about
promotions, resource allocation, and organizational
improvements.
2. Targeted Training and Development:
•Identifies specific skill gaps and areas for
improvement, allowing HR and training teams to
create effective, targeted training programs.
3. Enhanced Employee Engagement:
•Offers employees clear feedback on their
performance, which boosts motivation, engagement,
and alignment with the organization’s goals.
4. Optimized Compensation and Rewards:
•Ensures that compensation strategies are fair and
performance-based, helping to retain high
performers and motivate the workforce.
5. Organizational Improvement and Growth:
•Supports continuous improvement by identifying
areas where the organization can invest in
development and drive overall growth.
6
OUR SOLUTION AND ITS VALUE
PROPOSITION
Conditional formatting = missing the values
[Link] Conditional Formatting to highlight blank cells in a
dataset, applying a custom format (e.g., red fill) to cells
containing missing values (=Is blank(A1)). Select the range,
go to Home > Conditional Formatting > New Rule > Use a
formula to determine which cells to format. Enter the
formula =ISBLANK(A1) and set the desired format.
Filter = remove the missing
1. Filter: Data > Filter > Blanks.2. Go To Special: Ctrl + G >
Special > Blanks.3. Conditional Formatting: Home >
Highlight Cells Rules > Blank Cells.
Formula of perform analysis
syntax
Syntax:- logical_test1, logical_test2, ...: Conditions to
evaluate- value_if_true1, value_if_true2, ...: Values to
return if conditions are true

8
Formula = checking for performance

=IFS(A1>10000, "High", A1>5000, "Medium", "Low")These formulas categorize data in

cells A1, B1, and C1 based on specified conditions, returning corresponding values ("High",
"Medium", "Low", etc.). Use these formulas to analyze and classify your data, and make
informed decisions!

Pivot table

• 1. Select the data range you want to summarize.2. Go to the "Insert" tab and click on
"PivotTable".3. Drag the field you want to summarize (e.g., "Sports") to the "Row
Labels" area.4. Drag the field you want to summarize (e.g., "Total Number Students") to
the "Values" area.5. Right-click on the "Values" field and select "Summarize" >
"Summarize by" > "Average" (or any other summary function you need).6. To show only
the top 3 lines, right-click on the "Row Labels" field and select "Filter" > "Top" > "Top 3".
Graph

• Step 1: Select Data Choose the data range you want to graph, including headers. Go to
the "Insert" tab in the ribbon.

• Step 2: Choose Graph Type- Click on the graph type you want to create (e.g., Column,
Line, Pie, Bar).- Select a subtype (e.g., 2D or 3D).

• Step 3: Customize Graph- Right-click on the graph to access formatting options.- Adjust
elements like titles, labels, colors, and fonts.

• Step 4: Add Data Labels- Right-click on the graph and select "Add Data Labels".- Choose
where to display labels (e.g., above, below, or inside data points).

• Step 5: Finalize- Review and adjust your graph as needed.- Save your workbook.
Dataset Description
1. Employee ID (unique identifier)
2. Name( First name ,last name)
3. Department
4. Job Title
5. Hire Date
6. Performance Ratings (e.g., 1-5 scale, low to very high)
7. Gender
THE "WOW" IN OUR
SOLUTION
Performance analysis formula

=IFS(G5>=5,"VERY
HIGH",G5>=4,"HEIGH",G5>=3,"MED",TR
UE,"LOW")

3/21/2024 Annual 12
Review
MODELLI
NGData collection *
• Step 1: Define the Problem and
Objectives- Identify the goals of the
analysis (e.g., employee turnover,
performance, engagement)- Determine
the key questions to answer
• Step 2: Choose a Dataset- Search for
relevant employee datasets on Kaggle
(e.g., HR Analytics, Employee Attrition)-
Select a dataset that aligns with your
objectives
13
 Step 3: Import and Explore the Data- Import the dataset into a
Kaggle notebook or Excel- Explore the data using summary
statistics, visualizations, and data profiling

Feature collection

- HR systems (e.g., Workday, BambooHR)- Performance

management tools (e.g., Lattice, 15Five)- Employee
engagement surveys (e.g., Culture Amp, SurveyMonkey)-
Time-off and attendance systems (e.g., ADP, Namely)- Training
and development platforms (e.g., Udemy, LinkedIn Learning)
Data cleaning

• Remove irrelevant data

• Eliminate columns or rows unrelated
to performance analysis.
• Handle missing values
• Decide on a strategy for missing
performance ratings, feedback, or
other relevant data.
Performance level

Step 1: Prepare Your Data Collect and import relevant

data, such as employee performance ratings, goals, and
feedback Ensure data is organized and formatted
consistently
Step 2: Categorize Performance Levels- Define
performance levels (e.g., Excellent, Meets Expectations,
Needs Improvement)- Assign numerical values or codes to
each level
Step 3: Calculate Performance Scores- Use formulas to
calculate performance scores based on ratings, goals, and
feedback- Consider using weighted averages or indexes to
combine multiple metrics
Step 4: Identify High and Low Performers- Set thresholds
for high and low performers based on performance scores-
Use conditional formatting or filtering to highlight high
and low performers

Pivot summary

1. Data Aggregation: Summarize data by

sum, average, count, or other functions.

2. Data Rotation: Rotate data to view

different perspectives (e.g., switch rows
and columns).
3. Customization: Create personalized summaries by
selecting specific fields and filters.

4. Drill-Down Capability: Double-click to view detailed

data behind summary values.
RESUL
TS HIGH

BPC CCDR EW MSC NEL

PL PYZ SVG TNS WBL

19
 employee data analysis
80

70

60

50

40

30

20

10

0
BPC CCDR EW MSC NEL PL PYZ SVG TNS WBL

HIGH LOW MED VERY HIGH

conclusion
- "Data analysis is not just about
numbers, it's about telling a story that
drives action and improves employee
lives."