BH6073 APPLICATION OF TEHCNOLOGY IN HUMAN CAPITAL MANAGEMENT SEMESTER 2 SESSION 2023/2024

发布时间：2024-06-12

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

BH6073

APPLICATION OF TEHCNOLOGY IN HUMAN CAPITAL MANAGEMENT

SEMESTER 2 SESSION 2023/2024

JUNE 2024

Question

Creating a dashboard using PivotTables in Excel and Power BI tools on an HR dataset can help company to understand HR metrics and visualize data effectively. You have been provided with a dataset containing HR data for a Andi OZ Berhad. Using this dataset, perform the following tasks to create an interactive HR dashboard in Excel and Power B.I

Part 1: Data Preparation

1. Clean the Data:

- Check for and handle any missing or duplicate values.

- Ensure that all dates and numeric values are in the correct format.

2. Create PivotTables:

- Create a PivotTable to summarize the average salary by department.

- Create a PivotTable to summarize the count of employees by job title.

- Create a PivotTable to show the gender distribution across departments.

- Create a PivotTable to display the average performance rating by department.

- Create a PivotTable to show the attrition rate by department.

- Create a PivotTable to show the total number of trainings by department.

- Create a PivotTable to display the variances of numbers of years with the company across department.

- Create a PivotTable to summarize performance rating and years since last promotion by department.

- Create a PivotTable to summarize the level of employees satisfaction and years at the company by department

Part 2: Pivot Charts

1. Generate Pivot Charts:

- Create a bar chart to display the average salary by department.

- Create a column chart to show the count of employees by job title.

- Create a pie chart to illustrate the gender distribution across departments.

- Create a bar chart to display the average performance rating by department.

- Create a line chart to show the attrition rate by department over time.

- Create a ribbon chart to show the distribution of number of training by department.

- Create a combo bar to summarize performance rating and years since last promotion by department.

- Create a combo bar chart to summarize the level of employees’ satisfaction and years at the company by department.

Part 3: Dashboard Construction Utilising Excel Pivot and Power BI

1. Assemble the Dashboard:

- Arrange the Pivot Charts on a single worksheet to create a dashboard.

- Add slicers for interactive filtering (e.g., by department, job title, gender).

- Ensure that the slicers are linked to the relevant PivotTables and Pivot Charts.

2. Enhance the Dashboard:

- Add a title and any necessary labels to make the dashboard easy to understand.

- Apply formatting to improve the visual appeal of the dashboard (e.g., colors, fonts, borders).

Part 4: Descriptive Analysis and Interpretation

1. Analyze the Data:

- Identify which department has the highest and lowest average salary.

- Determine the most and least common job titles in the company.

- Analyze the gender distribution to see if there is a gender imbalance in any department.

- Identify which department has the highest and lowest average performance rating.

- Determine the department with the highest attrition rate and discuss potential reasons.

- Analyze which department has the highest and lowest number of training.

- Analyze which department has the highest number of employees being promoted.

- Do you think there is a relationship between employees rating and employees promotion? Justify your answer.

- Do you think there is a relationship between the level of employees’ satisfaction and years at the company by department? Justify your answer.

2. Present Your Findings:

- Write a brief summary of your key findings from the data analysis.

- Highlight any insights that can help the HR department make informed decisions regarding employee management and retention.