TASK 1:
EMPLOYEE DATA ANALYSIS
Using Microsoft Excel
Prepared by:
Gustiyan Islahuzaman
1 DATA QUESTIONS
1. Can you create a pivot table to summarize the total number of
employees in each department?
The company has 3000 employees in different
departments. The largest team is in "Production"
with 2020 employees, and other key areas include
"Sales" and "Software Engineering." Some people
work in support roles in "Admin Offices" and
"Executive Office." The company is also big on
technology, with 430 employees in "IT/IS" and 115 in
"Software Engineering." So, there's a mix of roles,
but the main focus is on making things, selling
them, and using technology.
2 DATA QUESTIONS
2. Apply conditional formatting to highlight employees with a
"Performance Score" below 3 in red.
A total of 781 employees in the company
have a performance rating below 3. This
indicates that a substantial portion of the
workforce falls below the satisfactory
performance level. It's essential for the
company to consider strategies for
improvement, such as providing additional
support, training, or feedback to enhance
overall employee performance and
contribute to the organization's success.
3 DATA QUESTIONS
3. Calculate the average "Satisfaction Score" for male and female
employees separately using a pivot table.
The satisfaction scores, on average, are the same for both male and female employees,
with both groups having an average score of 3.0. This suggests that, on average, there is a
balanced level of satisfaction between male and female employees in the workplace.
4 DATA QUESTIONS
4. Create a chart to visualize the distribution of "Work-Life
Balance Score" for different job functions.
5 DATA QUESTIONS
5. Filter the data to display only terminated employees and find
out the most common "Termination Type."
Based on the provided data, the most
common "Termination Type" among
terminated employees is "Involuntary,"
with a total of 388 employees. This
indicates that a significant portion of the
terminated employees left due to reasons
classified as involuntary. The other
termination types include "Retirement"
with 377 employees, "Voluntary" with 388
employees, and "Resignation" with 380
employees. The total number of
terminated employees is 1533.
6 DATA QUESTIONS
6. Calculate the average "Engagement Score" for each
department using a pivot table.
Employee engagement scores vary by
department. The "Executive Office" has
the highest score at 3.4, showing strong
engagement. "IT/IS," "Sales," and
"Software Engineering" have an average
score of 3.0, indicating moderate
engagement. "Admin Offices" and
"Production" have a slightly lower
average of 2.9. Improving engagement in
these areas might be worth exploring.
7 DATA QUESTIONS
7. Use VLOOKUP to find the supervisor's email address for a
specific employee.
VLOOKUP is a valuable tool in Excel that enhances data retrieval,
accuracy, and efficiency, making it an essential function for users
working with large datasets and complex workbooks.
8 DATA QUESTIONS
8. Can you identify the department with the highest average
"Employee Rating”?
Employee ratings across departments
vary, with Admin Offices having the highest
average at 3.03, indicating positive feedback.
The Executive Office has a slightly lower
average of 2.79, suggesting potential areas
for improvement in employee satisfaction.
Meanwhile, IT/IS, Production, Sales, and
Software Engineering departments all have
moderate average ratings, ranging from 2.90
to 2.98.
9 DATA QUESTIONS
9. Create a scatter plot to explore the relationship between
"Training Duration (Days)" and "Training Cost."
In the scatter plot exploring the
relationship between "Training Duration
(Days)" and "Training Cost," a discernible
positive correlation is evident. As the
training duration increases, there is a
corresponding rise in training costs. This
positive relationship suggests that longer
training programs tend to incur higher
costs.
10 DATA QUESTIONS
10. Build a pivot table that shows the count of employees by
"RaceDesc" and "GenderCode."
The provided data presents a breakdown of
the total number of employees based on
both race and gender. Notably, the "Asian"
and "White" categories have the highest
representation, with 629 and 599 employees
respectively. Additionally, the data indicates
a balanced gender distribution within each
racial category. However, it's essential to
consider not just the total numbers but also
the proportions within each category to gain
a comprehensive understanding of the
workforce composition.
11 DATA QUESTIONS
11. Use INDEX and MATCH functions to find the "Training
Program Name" for an employee with a specific ID.
Using the INDEX and MATCH functions in Excel to find the "Training Program Name" for an
employee with a specific ID enhances data retrieval and flexibility. The combination of
these functions allows for a dynamic lookup, ensuring accuracy without the need for
sorting data. The INDEX function retrieves the value from the specified column (in this case,
the "Training Program Name" column), while the MATCH function finds the position of the
specific ID within the ID column. This approach is particularly advantageous when dealing
with large datasets or when frequent updates are made to the information.
12 DATA QUESTIONS
12. Create a multi-level pivot table to analyze the "Performance
Score" by "BusinessUnit" and "JobFunctionDescription."
13 DATA QUESTIONS
13. Design a dynamic chart that allows users to select and
visualize the performance of any employee over time.
14 DATA QUESTIONS
14. Calculate the total training cost for each "Training Program
Name" and display it in a bar chart.
The provided data displays the
"Training Program Name" along with
the corresponding total "Training
Cost" for each program. Notably, the
"Communication Skills" training
program has the highest associated
cost at $365,023.24, suggesting that it
may involve more extensive resources
or specialized content. On the other
hand, the "Customer Service"
program has the lowest cost among
the listed programs at $320,575.04.
15 DATA QUESTIONS
15. Apply advanced conditional formatting to highlight the top 10%
and bottom 10% of employees based on "Current Employee Rating."
16 DATA QUESTIONS
16. Use a calculated field in a pivot table to determine the
average "Engagement Score" per year.
The data presented indicates the average engagement scores for the years 2022 and 2023.
There is a slight positive trend, with the average engagement score increasing from 2.92 in
2022 to 2.95 in 2023. This improvement suggests a potential positive shift in employee
engagement levels over the one-year period. While the change is relatively modest, it
could still reflect positive organizational developments or initiatives that have positively
impacted the overall employee experience.
17 DATA QUESTIONS
17. Can you build a macro that automates the process of
updating and refreshing all pivot tables in the workbook?
18 DATA QUESTIONS
18. Create a histogram to understand the distribution of
"ExitDate" for terminated employees.
19 DATA QUESTIONS
19. Utilize the SUMPRODUCT function to calculate the total
training cost for employees in a specific location.
In summary, the SUMPRODUCT function is a powerful tool that simplifies and automates
conditional summation, providing an efficient and dynamic solution for calculating totals
based on specific criteria, such as the total training cost for employees in a particular
location.
20 DASHBOARD