Individual Assignment 2

In a dynamic business landscape marked by increasing demands and fierce competition, a retail company seeks your expertise to unlock insights that will drive their 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, states (only for top 4) 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 average of a month. Explain any noteworthy fluctuations and their possible 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 top 15 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 5 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. (6 marks)

4 | P a g e

Submission Guidelines:

• Your report should include appropriate data visualizations, tables, and graphs.
• Ensure clarity, logical flow, and concise communication in your analysis and recommendations.

Remember, the goal of this assignment is not just analysis but also to synthesize actionable insights that can drive the company's success in a competitive market.

Grading rubric: Key points Grade allocation (%) Format (font type, size, tables, and charts) 20 Analysis (grade breakdowns for each question are provided above) 44

Overall content (discussions and recommendations, methodology,

professionalism)

26 Creativity in presenting data 10