Project 4 - E-Commerce Analytics using Excel

Submission Type:File Upload

Description

Problem Statement

You are tasked with analyzing StellarMart's business data to provide actionable insights for strategic
growth. StellarMart is a mid-sized retail chain specializing in Electronics, Clothing, Home Appliances,
Books, and Groceries. Faced with growing competition and shifting customer preferences, the company
aims to leverage data-driven insights to optimize operations and boost profitability. As a data analytics
consultant, your role is to transform two years of raw sales, customer, and product data into meaningful
insights.

Business Problem
StellarMart has been experiencing challenges in several areas:

1. Sales Decline in Certain Product Categories: While some categories perform exceptionally well,
others show stagnation or decline. Management wants to know which categories and products
are thriving and which require strategic intervention.

2. Seasonal Trends: StellarMart suspects sales trends fluctuate significantly by season or time of
year but lacks clear visibility into these patterns.

3. Regional Performance: Different regions contribute unequally to overall revenue, and

StellarMart wants to prioritize investment in high-performing areas while addressing
underperformance

Your Role
As part of this project, your role is to analyze StellarMart’s data and address the following objectives:

1. Identify sales trends over time, including monthly and seasonal fluctuations.

2. Compare sales performance across product categories and pinpoint underperforming ones.

3. Analyze sales contributions category wise from each region

 4. Create dynamic visualizations to showcase key insights (e.g., sales trends, customer
segmentation, and regional performance).

Dataset

 You can find the dataset here: StellarMart_Raw_Dataset_final.xlsx

Approach

 You will work on this project in your MS Excel application / online excel instance

 For step-by-step guidance on solving the problem, refer to the approach document: here

Submission Guidelines & Model Solution

Once you've completed the project:

1. Save the updated excel file: Ensure your data, analysis, and visualisations are included in
the excel file.

2. Upload the file for submission: Submit the completed .xlsx file

3. Refer to the model solution: Here

To solve the problem statement, follow the steps mentioned below:

1. Data Cleaning

 Remove Duplicate Rows

 Select the dataset.

 Use the "Remove Duplicates" option under the Data tab, ensuring all columns are
checked.

 Handle Missing Values

 Identify missing values in key columns like "Category," "Region," and "Product Name."
  Replace missing values with defaults or remove rows with critical missing data.

 Correct Inconsistent Text Formatting

 Standardize text using formulas (e.g., REPLACE function).

 Fix inconsistencies like lowercase or typos.

 Fix Typos in Product Names

 Use string functions like REPLACE/TRIM to clean suffixes or fix variations.

 Ensure Data Consistency

 Verify product-to-category assignments using VLOOKUP or manual corrections.

2. Data Preprocessing

 Add Time-Based Columns

 Extract "Month-Year" using: =TEXT(Date, "mmm yyyy").

 Add "Quarter-Year" using: ="Q"&ROUNDUP(MONTH(Date)/3,0)&" "&TEXT(Date,"YY").

3. Sales Analysis

 Analyze Sales by Category and Region

 Use a PivotTable:

 Rows: Category, Region.

 Values: Total Sales (Sum), Sale Price (Average), Quantity Sold (Sum).

 Compare Sales Performance

 Highlight sales trends and prioritize high- or low-performing areas.

 Sort PivotTables by "Total Sales" for actionable insights.

4. Data Visualization

 Create Charts
  Stacked bar/column chart: Sales by Category and Region.

 Pie chart: Customer Segmentation.

 Horizontal bar chart: Top Products.

Project 5: Aviation Sector

Submission Type:File Upload

Description

Problem Statement

AeroMax Airways is a mid-sized international airline operating in several regions worldwide. Over the
past few years, the aviation industry has become increasingly competitive, and AeroMax Airways has
been facing challenges in maintaining its market share and profitability. Despite having a strong
operational network, the airline has been struggling with key issues that impact both customer
experience and financial performance.

Business Problem

AeroMax Airways has been experiencing challenges in several areas:

1. Flight Delays: An increasing number of flights are delayed, causing customer dissatisfaction and
negatively affecting the airline's reputation.
 2. Customer Complaints: Passengers frequently report dissatisfaction with onboard services, ticket
pricing, and in-flight experiences.

3. Profit Margins: Rising operational costs, particularly fuel expenses, have squeezed profit
margins. Existing pricing strategies are not yielding optimal results.

The company has collected extensive data on its operations, customer demographics, flight schedules,
and financial metrics. However, this data is uncleaned, inconsistent, and requires significant
preprocessing to derive actionable insights.

Your Role

As part of this project, your role is to analyze AeroMax Airways' data and address the following
objectives:

1. Identify patterns and trends in flight delays and recommend ways to improve punctuality - Find
the flights with highest delays, and the departure and arrival airport with highest delays.

2. Analyze passenger demographics and travel behavior to understand customer satisfaction levels
and suggest targeted improvements - Customer Satisfaction based on age group

3. Conduct a revenue and cost analysis to optimize pricing strategies and identify fuel efficiency
measures to improve profitability - For each flight, check the revenue and operations cost and
check the %.

The insights derived from your analysis will support AeroMax in making data-driven decisions, regaining
its competitive edge, and addressing critical challenges impacting its operations.

Dataset

 You can find the dataset here: Aeromax_Airways_Final.xlsx

Approach

 You will work on this project in your MS Excel application / online excel instance

 For step-by-step guidance on solving the problem, refer to the approach document: here
Submission Guidelines & Model Solution

Once you've completed the project:

1. Save the updated excel file: Ensure your data, analysis, and visualisations are included in
the excel file.

2. Upload the file for submission: Submit the completed .xlsx file

3. Refer to the model solution: Here

Step 1: Data Cleaning

Objective:

Ensure the dataset is free of errors, duplicates, missing values, and inconsistent formats to prepare it for
analysis.

Steps:

1. Remove Duplicates:

 Identify and eliminate duplicate rows based on relevant columns

(e.g., Passenger_ID, Flight_Number).

 Use the Remove Duplicates option in Excel.

2. Identify Missing Data:

 Highlight missing values using Conditional Formatting.

 For numerical data, calculate averages or medians and replace the missing values.

 For categorical data, find the most frequent category or consider assigning "Unknown."

3. Ensure Consistent Formats:

 Check that all date columns are formatted as Date.

 Ensure numerical fields (e.g., Ticket_Price, Revenue) are in Number format.

 Verify time columns are formatted as Time.

4. Validate Data:
  Use Filters to identify anomalies (e.g., extremely high or low values
in Flight_Distance or Flight_Delay_Minutes).

 Consider setting thresholds for reasonable values (e.g., flight distances <20,000 km).

Step 2: Data Processing

Objective:

Enhance the dataset by creating additional columns and grouping data to support deeper analysis.

Steps:

1. Create Age Groups:

 Define age ranges (e.g., 18–29, 30–39, etc.) and group passengers into these ranges.

2. Categorize Delays:

 Classify delays into categories (e.g., On-Time, Moderate, Severe) based on thresholds
like:

 On-Time: ≤30 minutes

 Moderate: 31–120 minutes

 Severe: >120 minutes

3. Add Revenue Metrics:

 Calculate a Revenue to Cost Ratio to compare how efficiently revenue is generated

against operating costs.

4. Group Flight Distances:

 Create categories for Flight_Distance (e.g., Short-haul, Medium-haul, Long-haul) to

analyze performance and trends by distance.

5. Summarize Passenger Feedback:

 Use PivotTables to group feedback scores by Age Group, Ticket_Class,

and Frequent_Flyer_Status.

Step 3: Data Analytics

Objective:

Analyze trends, identify patterns, and derive actionable insights.

Steps:

1. Flight Delays:

 Find the flight with the highest average delay.

 Identify the most congested airports (departure and arrival) by average delay.

 Use PivotTables or sort columns to highlight these trends.

2. Customer Satisfaction:

 Analyze average feedback scores for different passenger groups:

 By age.

 By ticket class.

 By frequent flyer status.

 Visualize feedback trends using charts (e.g., bar or line charts).

3. Revenue vs. Cost:

 Identify routes with high operating costs but low revenue.

 Analyze how ticket classes contribute to overall revenue.

4. Fuel Efficiency:

 Compare fuel consumption across flight distances.

 Create scatter plots to visualize how distance impacts fuel usage.