Portfolio - Mrinal Bisht

Excel Project

Excel Sales Analysis– Brick & mortar business

The project is about AtliQ Hardware a brick-and-mortar business and they required their Excel files to be converted into meaningful data insights. Sales and Finance reports are generated using Excel and its various features like advanced formulas, power query, and power pivot.

View Project
Engagement

View Project
on GitHUB

Excel Sales Analysis– Brick & mortar business

AtliQ Hardware - Profit & Loss Statements by Fiscal Months (FY starts from sep for this organization) for different years.

AtliQ Hardware - Profit & Loss statements as per markets for different years

Project Details

Sales Report:
• Project objective:
1. Create a customer performance report
2. Conduct a comprehensive comparison between market performance and sales targets
• Purpose of sales analytics: Empower businesses to monitor and evaluate their sales activities and performance.
• Importance of analysing sales data: Identify sales patterns and track key performance indicators (KPIs).
Finance Report:
• Project objective:
1. Create Profit and Loss (P&L) reports by Fiscal Year& Months
2. Create Profit and Loss (P&L) reports by Markets
• Purpose of sales analytics: Evaluation of financial performance, support decision-making, and facilitate communication with stakeholders.
• Importance of analysing Finance data: Aid in benchmarking against industry peers and previous periods Foundation for budgeting and forecasting.
Technical Skills:
• ETL methodology (Extract, Transform, Load).
• Skills to generate a date table using Power Query.
• Ability to derive fiscal months and quarters.

SQL Project

AtliQ Hardware Retail Sales Analysis– Brick & mortar business

Used SQL skills from beginner to advanced level in this comprehensive project to gain insights and draw meaningful conclusions from a data set of more than 1.8 million records of AtliQ Hardware company which operates in Brick and mortar industry and sells products worldwide markets

View Project
Engagement

View Project
on GitHUB

Other Tools

Excel

AtliQ Hardware Retail Sales Analysis– Brick & mortar business

Tables constructed with an accompanying stored procedure facilitate quick customization based on market, product, or customer for a given period.

The goal is to identify variances between the forecasted sales quantity and the actual sold quantity. (forecast accuracy = 100% - absolute error%)

Project Details

Project Overview
AtliQ Hardware is an online retailer specializing in electronic products, operating through a variety of sales channels, including direct, distributor, and physical retail stores with a strong online presence. Serving customers across the globe, AtliQ offers a diverse product catalog consisting of about 400 SKUs. These products, including mice, processors, laptops, keyboards, etc., are customized for all users. In the last 5 years, AtliQ has accumulated a vast dataset of over 1 million sales records, prompting managers to make strategic decisions for the overall health of their operations.

SQL Skills
The project involves various SQL skills, including but not limited to SQL basics, joins, subqueries, CTEs, views, temporary tables, fundamentals of ETL, data warehousing, OLAP, OLTP, data engineering-specific topics such as indexes, triggers, events, user accounts & privileges, and proficiency in user-defined functions, stored procedures, and window functions.

PowerBI Project

Hospitality-Analysis-AtliQ-Grand-Revenue-Insights

AtliQ Grands owns multiple five-star hotels across India. They have been in the hospitality industry for the past 20 years. As a strategic move, the managing director of AtliQ Grands wanted to incorporate "Business and Data Intelligence" to regain their market share and revenue.

View Project
Engagement

View Project
on GitHUB

Other Tools

Excel

Hospitality-Analysis-AtliQ-Grand-Revenue-Insights

This shows the data modelling done in this project by making the required relationships between relevant tables.

This view shows data filtered of Bangalore city for Presidential rooms through all platforms of bookings and their property key metrics

Project Details

Project Overview
The Power BI report delves into three months of booking data, providing actionable insights and recommendations for pricing and cost-cutting strategies. This initiative is geared towards aiding AtliQ Grands in boosting revenue and reclaiming market share.

Project Objectives
Create metrics based on the provided metric list.
Develop a dashboard aligned with stakeholder mock-ups.
Generate relevant insights beyond the metric list dashboard.

Key Metrics
Most crucial metrics for success in the hospitality domain:
ADR (Average Daily Revenue)
DBRN (Daily Booked Room Nights)
DSRN (Daily Sellable Room Nights)
DURN (Daily Utilized Room Nights)
RevPAR (Revenue Per Available Room)
Occupancy Percentage
No-Show Rate
Realization Percentage

Project video

Live Interactive Dashboard

PowerBI Project

Business 360 - Brick & mortar and e-commerce

Business Insights 360 Project is a comprehensive data visualization created using Power BI designed to provide stakeholders with real-time insights customized to their specific needs. Primary views: Finance, Sales, Marketing, Supply Chain, Executive, each of which emphasizes key metrics and Kxecuti

View Project
Engagement

View Project
on GitHUB

Other Tools

SQL

Excel

Business 360 - Brick & mortar and e-commerce

This shows just a part of the power query editor being used.

This shows the marketing view for the financial year 2021.

Project Details

Problem Statement
AtliQ Hardware, a global supplier of computer peripherals, initially enjoyed success but encountered significant losses in recent years. This decline can be attributed to their reliance on static Excel reports, which failed to deliver the insights necessary for informed decision-making. As a result, they sought the expertise of a data analytics team to develop a dynamic Power BI dashboard, serving as a multifunctional tool offering insights from various departments, including Finance, Sales, Marketing, and Supply Chain.

Solution Unveiled
Exciting news! The Power BI dashboard is now fully operational for AtliQ Hardware. Leveraging our expertise in data modeling, Power Query, DAX, data validation, and dashboard design, successfully handled over 1.8 million records. The dashboard offers various views tailored to the specific needs of each team, enabling the Finance, Sales, Marketing, and Supply Chain teams to make well-informed decisions.

Check GitHub for more

Project video

Live Interactive Dashboard

Python Project

AtliQ Grands Hotel Chain Analysis

In this project through the Python pandas library, we do EDA (Exploratory Data Analysis) - Data cleaning, Data Transformation, and generation of Data Insights for AtliQ Grands hotel chain operating in the hospitality domain that owns multiple five-star hotels across India.

View Project
Engagement

View Project
on GitHUB

Data Transformation - Adding occ_pct column in data after calculating it and then applying lambda function to convert the derived values in round val

Printing average rating per city by grouping cities and getting the mean of ratings given. Print a pie chart of revenue realized per booking platform.

Project Details

In this project through the Python pandas library, we do EDA (Exploratory Data Analysis) - Data cleaning, Data Transformation, and generation of Data Insights for AtliQ Grands hotel chain operating in the hospitality domain that owns multiple five-star hotels across India. They have been in the hospitality industry for the past 20 years. The GitHub link shows the Python files that contain the concepts I learned before moving on to the exploratory data analysis of this project through the Pandas library in Jupyter Notebook. I did the Data cleaning, Data Transformation, and generation of Data Insights for the AtliQ Grands hotel chain while answering some scenario-based questions that can be raised by the management of any hotel chain which is important from their respective regarding how these insights impact their business.

Hello, I am

Mrinal Bisht

1

1

2

1

About

Key

My

Excel Sales Analysis– Brick & mortar business

AtliQ Hardware Retail Sales Analysis– Brick & mortar business

Hospitality-Analysis-AtliQ-Grand-Revenue-Insights

Business 360 - Brick & mortar and e-commerce

AtliQ Grands Hotel Chain Analysis

Contact Me

Email

Github

Linkedin

Call

Mrinal Bisht

Download Resume