I worked on analyzing employee and job posting data using Microsoft Excel, focusing on data transformation, forecasting, and business insights. The project involved handling two datasets:
Employee Dataset – Containing personal, professional, and educational information of employees. Job Posting Dataset – Including job posting dates, salaries, designations, and related details. The project utilized Power Query for ETL, advanced Excel functions for analysis, and interactive dashboards for visualization.
I cleaned and transformed the dataset by:
- Using Power Query for ETL : Merging, filtering, and shaping data from multiple sources.
- Handling missing values : Filling gaps in key fields to ensure data consistency.
- Removing inconsistencies : Standardizing job titles, salary formats, and categorical data.
To derive insights, I used various Excel functionalities:
- Forecasting job postings : Created a forecasting sheet to predict future job postings based on historical data.
- What-If Analysis : One-variable & two-variable data tables --> Analyzed base salary variations over five years under different bonus and raise percentages.
- Goal Seek & Scenario Manager : Compared different total compensation scenarios over time.
- Lookup Functions : Used VLOOKUP and HLOOKUP for cross-referencing data.
To present the findings effectively, I Created multiple Pivot Tables to extract key insights. Built an interactive dashboard with slicers for dynamic filtering. KPI Measures calculated using DAX queries.
The objective of this project was to leverage Excel’s analytical capabilities for workforce planning and salary forecasting, providing valuable business insights.
Key areas of focus:
- Workforce trends
- Job posting analysis
- Compensation planning and forecasting
- Interactive data visualization
This project enabled me to:
- Performed ETL operations using Power Query.
- Applied advanced Excel functions for analysis.
- Used What-If Analysis, Goal Seek, and Scenario Manager for compensation planning.
- Built an interactive dashboards using Pivot Tables and DAX.
- Extracted business insights from large datasets efficiently.