Portfolio - Devesh Singh

Excel Project

Sales and Finance Analytics Project

In this challenging project, transformed a massive dataset of 1 million records of raw sales data into actionable insights to empower company's growth. Created various Sales & Finance reports for the company based on products, division, markets, region for different quarters & fiscal years.

View Project
Engagement

View Project
on GitHUB

Other Tools

SQL Workbench

Power Query

Pivot Table

Market Performance vs Target Report

Profit & Loss for different Markets

Project Details

🛠️𝗢𝗽𝗲𝗿𝗮𝘁𝗶𝗼𝗻𝘀 𝗣𝗲𝗿𝗳𝗼𝗿𝗺𝗲𝗱:
• Followed ETL (Extract, Transform, Load) techniques to clean and format data with Power Query.
• Used VLOOKUPS, INDEX-MATCH and XLOOKUPS to integrate and analyse data from multiple tables.
• Leveraged Pivot Table and DAX (Data Analysis Expressions) for various analysis, crafting dynamic data models.
• Implemented conditional formatting techniques to improve data visualisation and make insights easier to understand.
• Worked on slicers, measures, and filters, formatting to provide a user-friendly experience.

✅ 𝗞𝗲𝘆 𝗧𝗮𝗸𝗲𝗮𝘄𝗮𝘆𝘀:
▪ Applied business Maths & Statistics to do different analysis
▪ Performed data modelling to establish relationships between tables
▪ Understood how to clean raw data and bring it in a proper format
▪ Learned how to deal with different shareholders while presenting a report
• Learned about the best practices for creating a professional analysis report

Project video

PowerBI Project

Business Insight 360

I created a dashboard with a dataset of 1.5 million records from 2 SQL files and 3 Excel files. The dashboard provides vital insights into finance, sales, marketing, and supply chain performance. It can be used by different stakeholders to make accurate decisions and bring transparency to data.

View Project
Engagement

View Project
on GitHUB

Other Tools

Power Query

Home Page of Complete Dashboard

Data Modeling done to Create Project

Project Details

Dashboards Created:
🏠 Home - Overview of all dashboards
ℹ️ Info - Basic necessary information
💰 Finance View - Displays P & L performance
📊 Sales View - Presents customer performance
🎯 Marketing View - Shows product performance
📦 Supply Chain View - Monitors demand & supply
📈 Executive View - Provides overall company performance

Important KPIs Used:
❇Net Sales = Gross Sales – Discount Given
❇Gross Margin % = (Gross Margin/Net Sales) * 100
❇Net Profit % = (Net Profit/Net Sales) * 100
❇Net Error = Forecasted Quantity – Sold Quantity
❇Absolute Error = Magnitude of Net Error
❇ Forecast Accuracy %

Learnt Power BI Techniques:
❇Data modelling
❇Using KPI indicators
❇Creating calculated columns
❇DAX language for measures
❇Tolerance limit filters and Tooltips
❇Dynamic page navigation with buttons
❇Conditional formatting of visuals
❇Data validation techniques
❇Publishing reports to Power BI services

Project video

Live Interactive Dashboard

SQL Project

10 Ad-hoc Insights

This was 4th Resume Project Challenge conducted by Codebasics for the data geeks. I have completed this project as a part of my learning & practice to work on my skills and gain more experience. In this project, I have used SQL for querying and POWER BI for visualization purpose.

View Project
Engagement

View Project
on GitHUB

Other Tools

Power BI

Canva

OBS Studio

Premiere Pro

This was the 7th request of the challenge.

It contains the visualization of he retrieved SQL query and insights generated from it.

Project Details

🌟 PROBLEM STATEMENT🌟
Atliq Hardware is a leading manufacturer of computer hardware in India and other nations, but the top management has noticed that they do not get enough insights and to make quicker and smart decisions. So they intend to add more junior data analysts to their data analytics team. Tony Sharma, the head of data analytics, conducts a SQL challenge to identify applicants with both technical and soft skills.

🌟 Tools Used 🌟
❇ MySQL - to query the database
❇ Power BI - to visualise the data
❇ Canva - to create presentation
❇ OBS Studio - for recording it

🌟 My Learnings 🌟
❇ CTE (Common Table Expression)
❇ Window Function
❇ row_number(), rank(), dense_rank()
❇ Subqueries, Multiple & Cross joins
❇ Power BI: Map, Data Modeling
❇ Bar Chart, Matrix, Line Chart, Donut Chart, etc.
❇ Putting the our insights by analysing the visual
❇ Worked on my presentation skills

Project video

Python Project

Python AtliQ Grand Project

In this project, I worked on a 3 months data of the "AtliQ Grand Hotel" chain having 1.4 lakh records. Provided the insights to the AtliQ revenue team about the occupancy %, revenue-generated and revenue-realized by the company across different cities, room categories, months etc.

View Project
Engagement

View Project
on GitHUB

Handling Outlier Values

Handling NaN (null) Values

Project Details

🛠️ 𝐓𝐨𝐨𝐥𝐬 𝐔𝐬𝐞𝐝: Jupyter Notebook, Canva

❇️Process followed by me:
📍Reading all the .csv files in Jupyter Notebook
📍Cleaned the booking data by removing -ve value of no_of_guests
📍Found the outlier values in revenue_generated column by using the concept of std
📍Outlier value( higher_limit = value + std*3, lower_limit = value - std*3)
📍 Created occupancy percentage column
📍Performed basic data transformation steps :
– Merging, Aggregation, Value formatting, Concatenation
📍Finally answered all the questions asked by the team

❇️Concepts/Methods/Functions Used:
📍merg(), concat(), groupby()
📍min/max(), sum(), value_counts()
📍unique(), to_datetime()
📍shape, describe(), info()
📍mean(), median(), std()
📍isnull(), fillna(), apply()

Live Interactive Dashboard

PowerBI Project

Mitron Bank Challenge

This project was in banking domain, here I have done the demographic and credit card usage analysis for the dataset of 4000 users provided by Mitron Bank. The key findings of my analysis helped the product strategy team of Mitron, in defining the new line of credit cards for their target customers.

View Project
Engagement

View Project
on GitHUB

A glance of dashboard.

Statistics used in the whole analysis.

Project Details

❇️Important KPIs/Metrics Used:
1) Monthly Income
2) Monthly Spend
3) Income Utilisation %
4) % of Customers
5) Credit Card Spend
6) Credit Card Usage %

❇️Short summary of my learnings:
1) Created the statistic month table
2) Understood the use of ALL(column) function
3) Created the “Clear All Filters” button for the first time
4) Utilised “Edit Interaction” feature for the first time to stop the cross-filtering
5) Designed toggle button for filtering report based on gender & marital status
6) Visuals used are (line chart, line & stacked column chart, line & clustered column chart, donut chart, treemap, ribbon chart, matrix)

Hello, I am

Devesh Singh

1

2

1

1

About

Key

My

Sales and Finance Analytics Project

Business Insight 360

10 Ad-hoc Insights

Python AtliQ Grand Project

Mitron Bank Challenge

Awards

Contact Me

Email

Github

Linkedin

Call

Devesh Singh

Download Resume