Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
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.