Merlin Cycles Sales Analysis Project

Merlin Cycles is a renowned store specializing in selling bikes and related accessories. They required a detailed and dynamic analysis of their internet sales to understand customer purchasing patterns, product performance, and sales trends over time.

The primary objective of this project was to transition Merlin Cycles from static internet sales reports to dynamic visual dashboards. The goals were:

To provide a detailed overview of internet sales, segmented by customers and products.

To facilitate performance tracking against budget and identify key sales trends over time.

To enable sales representatives to filter and analyze data to pinpoint top-performing customers and products.

Total Sales: The total revenue generated from internet sales.

Sales vs Budget: Comparison of actual sales against the planned budget.

Sales by Product Category: Revenue generated from different product categories (Bikes, Accessories, Clothing).

Sales by Customer: Revenue contribution of individual customers.

Monthly Sales and Budget: Monthly breakdown of sales and budget amounts.

Customer City: Geographic location of customers.

Sub Category: Detailed product categories.

Product Name: Specific products sold.

Time: Year, month, and day to analyze sales trends over time.

Total Sales Performance: The total internet sales for the period amounted to $22,239,730, exceeding the budget of $21,100,000 by $1,139,730 (5.4% above budget). This positive variance underscores the effectiveness of current sales strategies and market demand.

Top Customers Contribution: Analysis revealed that the top 10 customers alone contributed $120,345. While this is a significant amount, it represents only a small portion of the total sales, indicating a diverse customer base with no over-reliance on a few customers. Jordan Turner was the highest contributor with 15,999 units sold.

Monthly Sales Trends: A detailed analysis of sales vs. budget on a monthly basis indicated that sales consistently exceeded budget expectations each month. Notably, the highest peaks were observed in June ($2,198,338) and December ($2,498,862), suggesting seasonal trends that should be considered in future planning.

Seasonality Insights: The data showed pronounced seasonality in sales, with significant increases during summer and the holiday season. These insights are critical for inventory planning and promotional activities.

Product Category Performance: The Bikes category dominated sales, contributing $21,199,198 (95.32%) of the total revenue. Within this category, the Mountain-200 series was the best performer, with the Mountain-200 Black, 46 model alone generating $1,371,420.

Geographic Sales Distribution: Geographic analysis indicated that North America and Europe are the primary markets, with substantial sales concentrations. North America accounted for 45% of total sales, while Europe contributed 35%.

Loyalty Programs: Given that a small percentage of customers contribute to a significant portion of sales, implementing targeted loyalty programs can further enhance customer retention and satisfaction. Personalized offers, discounts, and rewards should be designed for top-tier customers like Jordan Turner.

Customer Relationship Management: Utilize CRM systems to track and manage customer interactions and data. This will help in identifying potential high-value customers and tailoring marketing efforts accordingly.

Stock Optimization: Focus on optimizing inventory levels for top-selling products, particularly the Mountain-200 series. Ensure that adequate stock is maintained during peak sales months (June and December) to meet demand without overstocking.

Demand Forecasting: Implement advanced demand forecasting techniques to predict sales trends and adjust inventory levels proactively. This will minimize stockouts and reduce holding costs.

Diversification: While bikes are the dominant product category, expanding the product range in accessories and clothing can attract a broader customer base. Introducing complementary products such as biking gear, maintenance kits, and apparel can boost overall sales.

Market Research: Conduct market research to identify emerging trends and customer preferences in the biking community. Use these insights to introduce new products that meet market demands.

Tailored Campaigns: Develop and execute targeted marketing campaigns for high-concentration regions like North America and Europe. Utilize localized content and promotions to resonate with regional audiences.

Partnerships and Sponsorships: Explore partnerships with local biking events, clubs, and influencers to increase brand visibility and credibility in key markets.

Dynamic Dashboards: Regularly update the Power BI dashboard to monitor real-time sales performance against the budget. This will enable timely decision-making and strategic adjustments based on current data.

KPI Tracking: Establish key performance indicators (KPIs) to track critical metrics such as customer acquisition cost, lifetime value, and return on investment for marketing campaigns. Use these KPIs to measure the effectiveness of strategies and drive continuous improvement.

The dashboard can be found in Power BI here. This dashboard allowing users to track sales against budget, analyze top customers and products, and uncover key sales trends over time.