SALES DASHBOARD (bike factory)

SALES DASHBOARD (bike factory)

Objectives

The dashboard indicates that the business is focused on multi-regional expansion and product portfolio management across two fiscal years (2021–2022). The goal appears to be maintaining a healthy profit margin while scaling sales across diverse market segments (Government, Small Business, Enterprise) and international territories.

Skills Applied:

•      Data Cleaning & Transformation (Power Query)

•      Pivot Table and Pivot Chart Analysis

•      Dynamic Title Creation

•      Custom Dashboard Layout & Design

Tools Used:

•      Ms Excel - Visualisation, Pivot table, and Data Report.

•      Power Query – Data Cleaning and Transformation.

Key Metrics Overview:

✅ Highlight total sales, units sold, Profit margin and total profit for quick performance snapshots. 

✅ Sales Trend Monthly Chart: Visualize monthly trend to identify peak sales month.

✅ Sunburst chart: For products yearly sales.

✅Top selling Product: Identify Top Selling Product with Sales.

✅ Custom combination chart: Generate the amount of sales generated by each customer.

✅ Sales by Country: A pie chart of sales by each countries.

✅ Units Sold: A dynamic pie chart of units of product sold.

✅Profit Generated: Visualize monthly trend of Profit generated over the month.

✅Sales Break-up: A clustered bar chart of Sales breakup % Segment.

Key Insights

✅Year-over-Year (YoY) Performance: The line chart Profit generated over months reveals a critical insight, Profitability is stagnant. While there is a slight seasonal peak in June and December, the baseline for 2022 is almost identical to 2021.

✅The Volume Trap: The business is moving over 1.1 million units, but the flat profit line suggests that as sales increase, operational costs or discounts are increasing at the exact same rate.

✅Product Performance: PROD_ID_002 is the powerhouse of the company, generating ₦33,011,144 (approx. 28% of total revenue). In terms of volume, the Paseo bike model leads the "Units Sold Split" at 30%.

✅Segment Dominance: The Government (44%) and Small Business (36%) segments make up the vast majority (80%) of the sales breakup. The Enterprise and Midmarket segments are significantly under-penetrated.

✅Geographic Distribution: Sales are well-distributed internationally. France (15%) and Germany (14%) are the leading markets, while Canada (9%) is currently the smallest.

✅Seasonality & Profit Stability: The Profit generated over months trend line shows a very stable, flat trajectory from 2021 through 2022. While stability is good, it indicates a lack of growth momentum in profit despite the high volume of units sold.

✅Customer Concentration: A few key customers (notably CUST_ID_004) generate significantly revenue more than others, indicating a reliance on a small number of high-value accounts.

✅Regional Growth Strategy: The sales by country donut chart shows a very "flat" global distribution (most countries are between 11% and 15%).

Strategic Recommendations

✅For Year-over-Year (YoY) Performance increase: we need to Implement a price optimization strategy for 2023. Even a 2% increase in price across the Paseo and Velo lines which account for 44% of volume could significantly shift that flat profit line upward without necessarily hurting volume.

✅A. Diversify Customer Segments: With 80% of sales tied to Government and Small Business, the company is vulnerable to shifts in public spending or small business economic cycles.

Action to be taken: Is to launch a targeted Enterprise campaign to increase its current 17% share, focusing on corporate fleet sales.

✅Optimize Product Profitability: The Paseo model represents 30% of the unit volume, but the overall profit margin is 14.23%.

Action to be taken: Is to conduct a cost-of-goods-sold (COGS) analysis on the Paseo model. If it is a low-margin volume driver, attempt to bundle it with higher-margin accessories or service contracts to lift the average profit percentage.

✅Regional growth: Focus on Germany and France. Since they are our leading markets they occupy 29%, even small optimizations in their supply chains or localized marketing could yield higher returns than trying to fix the underperforming Canadian market (9%) from scratch.

✅Upsell Top Customers Customer CUST_ID_004 is a major outlier in revenue.We need to dedicated Account Manager to this client to ensure retention and explore Level 2 product offerings or long-term exclusivity contracts.

To download the Report CLICK HERE

To download the Raw file CLICK HERE

To download the Readme file CLICK HERE