Title: HR Data Analysis and Dashboard Creation

Objective: This assignment involves analyzing and consolidating HR data across three Excel files. You will clean and process the data, integrate it into a single file, and create a comprehensive dashboard. The dashboard should visually represent various insights such as gender distribution, department composition, performance metrics, and more.

Files Provided

Department Information - Contains department details.
Employee Information - Contains individual employee data.
HR Database - Contains HR-related records.

Instructions:

Part 1: Data Cleaning and Preparation

Load the Files:
- Open the provided Excel files (Department Information, Employee Information, and HR Database).
Data Quality Check:
- Review each file for any errors or inconsistencies in data entries.
- Identify and document any discrepancies or inconsistencies in the data.
Null Value Handling:
- Check for any missing values across all columns in the datasets.
- For any columns with null values, apply appropriate techniques to handle them (e.g., filling with averages, forward-fill, backward-fill, or other methods you deem fit).

Part 2: Data Integration Using VLOOKUP

Combine Files:
- Using the VLOOKUP function in Excel, integrate the three files into a single consolidated dataset.
- Match records from the Employee Information and Department Information files with the HR Database, based on appropriate keys (e.g., Employee ID, Department ID).
- Ensure there are no duplicate entries and that all relevant columns are included in the consolidated dataset.
Verify Data Accuracy:
- After merging, review the consolidated file for any mismatches or data inconsistencies.
- Correct any issues found to ensure data accuracy across the merged dataset.

Part 3: Dashboard Creation

Set Up the Dashboard Worksheet:
- Create a new worksheet in the consolidated Excel file dedicated to the dashboard.
Required Dashboard Components:
- Gender Distribution: Use a chart to represent the percentage of male and female employees.
- Sales by Position and Department: Show sales performance categorized by both employee position and department.
- Age Distribution: Use a histogram or bar chart to display the age distribution across employees.
- Employee Tenure by Department: Create a visual showing the tenure (years worked) of employees broken down by department.
- Total and Average Performance Review by Department: Summarize the total and average performance review scores per department.
- Department Composition: Represent the number of employees in each department.
- Average Employee Tenure by Department: Show the average tenure for employees within each department.
Dashboard Formatting and Presentation:
- Use charts and tables that are clear and easy to interpret.
- Add labels, titles, and data legends where appropriate for clarity.
- Use color-coding or other visual elements to highlight key metrics and trends.

Deliverables

Cleaned and Consolidated Excel File: Submit the consolidated Excel file containing the integrated data from all three original files.
Dashboard: The dashboard should be on a separate worksheet within the consolidated file, complete with all required charts and visuals.

Grading Criteria

Data Cleaning and Handling of Null Values: 20%
Data Integration with VLOOKUP: 25%
Dashboard Creation and Visual Quality: 35%
Accuracy and Completeness of Analysis: 20%

Good luck with your analysis and dashboard creation!

Download the three datasets: Employeeinfromation.xlsx, Departmentinformation.xlsx and HRDatabase.xlsx

Submit your work to mathewshem@yahoo.com for review

10 Day Data Analysis for Excel

Course Syllabus