answer the follwoing questions based on excel data given before to you: In a dynamic business landscape marked by increasing demands and fierce competition, a retail company

seeks your expertise to unlock insights that will drive its success. Your task is to conduct a

comprehensive analysis of their sales and profitability data, leading to actionable recommendations for

strategic decision$-$making. This assignment revolves around a dataset encompassing various dimensions

of the company's operations. You are also expected to construct a Regression model to predict profit.

The dataset includes the following variables:

$$ Row ID: Unique identifier for each data entry.

$$ Order ID: Distinct order identifier for every customer transaction.

$$ Order Date: Date of order placement.

$$ Ship Date: Date of product shipment.

$$ Ship Mode: Customer$-$specified shipping mode.

$$ Customer ID: Distinct ID for each customer.

$$ Customer Name: Name of the customer.

$$ Segment: Customer segmentation category.

$$ Country: Customer's country of residence.

$$ City: Customer's city of residence.

$$ State: Customer's state of residence.

$$ Postal Code: Postal code of the customer.

$$ Region: Customer's geographical region.

$$ Product ID: Unique product identifier.

$$ Category: Product category.

$$ Sub$-$Category: Sub$-$category of the product.

$$ Product Name: Name of the product.

$$ Sales: Product sales value.

$$ Quantity: Quantity of the product sold.

$$ Discount: Discount provided.

$$ Profit: Profit or loss incurred.

$2|$ P a g e

Your role is to employ both tabular and graphical methods of descriptive statistics to construct a

comprehensive customer profile and evaluate the impact of promotional campaigns. Your final report

must address the following key questions:

$1.$ Optimizing Shipping Modes: Analyze profitability associated with different shipping modes

$($profit average of each mode$)$ and determine the average shipping duration for each mode. $(4$ marks$)$

$2.$ Geographical Performance: Identify top$-$performing and underperforming regions and states $($only

for the top $5)$ based on total sales and average profit. $(4$ marks$)$

$3.$ Sales Trends Over Four Years: Visualize and interpret sales trends over a four$-$year period $($use

the order date$),$ with each data point representing a month. Identify any significant patterns, spikes,

or declines in sales and discuss potential factors that might have contributed to these fluctuations. $(4$

marks$)$

$4.$ Average Profit Trends Over Four Years: Create a chart illustrating average profit trends across

four years, with each data point representing the average of a month. Explain any noteworthy

fluctuations and their potential causes. $(4$ marks$)$

$5.$ Customer Segments Analysis: Assess variations in total sales, profit, and profit margin among

different customer segments. Identify the most profitable segment and provide strategic insights. $(4$

marks$)$

$6.$ Categorizing Profitable Categories and Sub$-$Categories: Define the top$-$performing and

underperforming categories and sub$-$categories based on both sales and profit. For a better

understanding, take a closer look at the Profit Margin and additionally divide sub$-$categories into $3$

groups: highly profitable, moderately profitable, and unprofitable. $(4$ marks$)$

$7.$ Location$-$based Analysis: Plot two charts that present lists of the top $10$ cities based on total sales

and profit. Additionally, identify cities with negative profit and propose potential strategies for

improvement. $(4$ marks$)$

$3|$ P a g e

$8.$ Loyalty and Total Sales Impact: Identify and analyze the top $4$ most loyal customers based on both

frequency of purchases and total order value. How significant is the contribution of these loyal

customers to the company's overall sales in percentage? $(4$ marks$)$

$9.$ Discount and Sales Correlation: Find the top $10$ states with the highest average discount and their

total sales and examine the relationship between average discounts and total sales for these top $10$

states. Include a trendline and analyze findings. $(6$ marks$)$

$10.$ Regression Model for Profit Prediction: Develop a linear regression model utilizing sales,

quantity, discount, and category to predict profit. Interpret the model's significance and limitations$.$