Cosmetics Sales Analysis

OVERVIEW

CLEARX Cosmetics faced challenges in consolidating sales data from its three branches, extracting actionable insights, and effectively assessing product overstocking or understocking risks. This project delivered a robust Sales & Stock Analysis Report and interactive dashboards designed to provide a unified view of sales performance, profitability, and inventory turnover. The goal was to empower decision-makers with the insights needed to optimize sales strategies, manage inventory efficiently, and enhance overall business performance. 

OBJECTIVES

The primary objectives of this project were to:

• Consolidate sales data from three distinct branches into a single, unified space.

• Transform raw data into actionable insights regarding sales performance and profitability.

• Assess products at risk of overstocking or understocking to optimize resource allocation.

• Analyze revenue trends by various factors including competition level, economic conditions, marketing campaigns, branches, gender, age group, and location.

• Identify top-selling and slow-moving products based on turnover rates.

• Provide strategic recommendations for improving customer retention, re-evaluating marketing efforts, capitalizing on star products, and driving higher revenue through quantity sold.

KEY COLUMNS

The analysis would have involved various data points related to sales, products, customers, and operational factors. Key data points analyzed include:

• Financial Metrics: Revenue, Profit, Average Order Value (AOV), Profit Margin.

• Sales Volume: Average Quantity Sold, Monthly Sales Figures.

• Product Details: Product Name, Turnover Rate.

• Market Conditions: Competition Level (High, Low, Medium), Economic Conditions (Fair, Good, Poor).

• Marketing Data: Marketing Campaign Level (High, Low, Medium, None).

• Branch Data: Sales by Branch (Branch A, B, C).

• Customer Demographics: Revenue by Gender (Female, Male), Revenue by Age Group (18-24, 25-34, 35-44, 45-54, 55-65), Revenue by Location (City, Rural, Suburb).

• Customer Behavior: Purchase Frequency, Customer Retention Rate.

TOOLS

The primary tools utilized for this sales analytics project were:

• Data Analysis & Visualization: Excel

APPROACH

The project followed a systematic approach to consolidate data, extract insights, and present actionable recommendations:

Phase 1: Data Consolidation & Initial Metrics

• Consolidated fragmented sales data from three branches into a single, unified dataset.

• Calculated core financial metrics: Total Revenue, Profit, Average Order Value (AOV), and Profit Margin for 2021 and 2022.

• Determined the Average Quantity of items sold.

Phase 2: Performance Trend Analysis

• Analyzed monthly revenue growth rates for 2021 and 2022 to identify strong growth periods (e.g., March, May) and declining trends (e.g., Q4).

• Investigated the impact of pricing strategy versus quantity sold on revenue.

Phase 3: Revenue Drivers Analysis

• Competition Level: Assessed revenue performance across different competition levels (High, Low, Medium) to understand market dynamics.

• Economic Conditions: Analyzed revenue generated under various economic conditions (Fair, Good, Poor) to understand external influences.

• Marketing Campaign Effectiveness: Evaluated revenue generated under different marketing campaign exposures (High, Low, Medium, None) to gauge ROI.

• Branch Performance: Examined revenue distribution across different branches to identify performance gaps.

• Demographic Analysis: Explored revenue by gender, age group, and location to understand customer segmentation and preferences.

Phase 4: Inventory & Product Performance Analysis

• Analyzed product turnover rates to categorize products into High, Moderate, and Low turnover.

• Identified top-selling products (Fragrances: Perfume, Body Mist, Cologne) and slow-moving inventory (Conditioner, Earrings, Sunscreen, Necklace, Styling Gel).

Phase 5: Insights & Recommendations

• Derived key insights from all analytical dimensions, highlighting strengths, weaknesses, and opportunities.

• Developed actionable recommendations focused on customer retention, marketing strategy re-evaluation, capitalizing on star products, and leveraging quantity sold for revenue growth.

KEY INSIGHTS

The CLEARX Cosmetics Sales & Stock Analysis Report yielded several critical insights:

• Stable Financial Performance: Consistent annual revenue of NGN 16M (total NGN 32M over 2 years) with an 80% profit margin and a stable AOV of NGN 213.9, indicating sustained business performance.

• Inconsistent Revenue Growth: Revenue growth showed alternating increases (e.g., March +21.9%, May +9.1%) and declines (e.g., Q4), suggesting seasonality or fluctuating demand.

• Quantity Drives Revenue: Quantity sold had a greater impact on revenue than price changes, indicating strong demand even with price increases.

• Medium Competition Optimality: Medium competition levels drove the highest revenue (~50%), suggesting a healthy market demand under moderate rivalry. High competition significantly reduced revenue (~12%).

• Economic Sensitivity: Poor economic conditions led to a sharp decline in revenue, while there was little difference between Fair and Good conditions.

• Ineffective Marketing Spend: Low or no marketing exposure still generated high revenue (~37%), while higher spending on marketing (Medium & High Campaigns) did not proportionally increase revenue (~12.5%-12.7%), suggesting ineffective or poorly targeted campaigns.

• Even Branch Distribution: Revenue was nearly evenly distributed across all three branches, indicating similar customer demand and operational efficiencies across locations.

• Gender-Neutral Revenue: Revenue was almost equally split between genders, with perfumes, colognes, and body mists being top sellers for both.

• Older Customer Spending Power: Older age groups (55-65) contributed the highest revenue (22.88%), suggesting greater purchasing power or brand loyalty.

• Customer Retention Challenge: Most revenue came from new or infrequent customers (73.33% had only one purchase), indicating a lack of strong customer retention strategies.

• Fragrances as Star Products: Perfume, Body Mist, and Cologne consistently ranked as the top three best-selling products with high turnover rates.

• Product Turnover Categories: Products were clearly categorized by turnover rate (High, Moderate, Low), informing inventory management.

IMPACTS

This project delivered significant impacts for CLEARX Cosmetics:

• Optimized Sales & Marketing Strategies: Insights into revenue drivers, marketing effectiveness, and customer behavior enable the company to refine its sales and marketing approaches for better ROI.

• Improved Inventory Management: Clear categorization of products by turnover rate allows for more efficient stock management, reducing risks of overstocking or stockouts.

• Enhanced Profitability: By focusing on high-performing products and optimizing marketing spend, the company can improve its overall profit margins.

• Data-Driven Decision Making: The report provides a consolidated, insightful view of business performance, empowering decision-makers to make informed choices.

• Identified Growth Opportunities: Highlighted areas such as customer retention, fragrance-focused marketing, and sales volume increase in decline months as key opportunities for growth.

DELIVERABLES

The key deliverables for this project included:

• CLEARX Cosmetics Sales & Stock Analysis Report (PDF): A comprehensive report detailing the project overview, business challenges, key financial metrics, revenue growth trends, analysis by competition, economic conditions, marketing, branches, demographics, customer retention, product performance, and actionable recommendations.

• Interactive Dashboards: Dashboards designed to facilitate the monitoring of sales performance for decision-makers.