A Superstore Sales Analysis With Excel: Insights, Key Takeaways and Recommendation
INTRODUCTION:
Jankin Superstores is a retail chain operating in three distinct strategic business units (SBUs) — Furniture, Office Supplies, and Technology — across four regions: Central, East, South, and West. The project’s objective is to analyze sales and profit trends for the years 2014 to 2017 using Excel to create data visualizations.
ANALYSIS AND INSIGHTS:
In the course of the four-year analysis timeframe, Jankin Superstores recorded a total of £2,297,201 in sales and achieved £286,397 in profit.
- Analysis of Sales and Profit by Region: The West region stands out with the highest profit, totalling £108,418 and the highest sales reaching £725,458. Conversely, the Central region recorded the lowest profit of £39,706, and the South region recorded the lowest sales figures amounting to £391,722.
- Analysis of Sales and Profit by Category: The Technology category led with the highest sales and profit in 2017, achieving sales of £271,731 and a profit of £50,684. Conversely, Office Supplies recorded the lowest sales and profit in 2014, with sales amounting to £151,776 and a profit of £3,015.
The Western region not only excels in sales and profit but also boasts the highest customer count, with a total of 3,203 customers, representing 32% of the entire customer base. In contrast, the South region has the lowest customer count, comprising only 16% of the total customer base, with 1,620 customers.
- Analysis of Annual Sales and Profit: In 2015, there was a sales decrease of roughly 2.83%, while profit saw an impressive increase of approximately 24.35%. In 2016, sales experienced a substantial surge of around 29.39%, accompanied by a remarkable profit increase of about 32.84%. The year 2017 witnessed a notable sales increase of approximately 20.32%, complemented by a solid profit growth of about 14.22%.
- Analysis of Shipping Mode: The Standard Class shipping mode emerged as the most commonly used, especially for Office Supplies, representing a significant portion of total sales at 61%. In contrast, the Same-day shipping mode was the least preferred, making up only 18% of sales, with a primary association with Technology products.
KEY TAKEAWAYS:
- The West region outperforms in both sales and profit, indicating its strategic importance.
- Technology has shown significant growth potential in terms of sales and profit.
- Customer count per region varies, with the Western region having the largest customer base.
- Sales and profit increased progressively over the four years, indicating overall growth.
- Standard Class shipping mode and Office Supplies are prominent contributors to sales.
RECOMMENDATION:
Sales Regions:
- Increase Investment in the Central Region: As the Central region has the lowest profit and the second-lowest sales, the superstore needs to consider increasing marketing efforts, expanding product discounts, or exploring cost-efficiency measures to boost profitability.
- Maintain Focus on the West: Given its impressive performance in both sales and profit, the superstore has to continue to invest in the West region. Periodic monitoring of KPIs such as sales growth rate, customer retention, and profit margin is recommended.
Product Category:
- Leverage Technology Category: Given its growth potential, the superstore should allocate resources to expand the Technology category. Consider introducing new products, increasing marketing efforts, and monitoring inventory turnover to optimize profitability.
- Review Office Supplies Sales Strategy: Despite being the highest contributor to sales, assess whether there are opportunities to improve profit margins within the Office Supplies category by renegotiating supplier contracts or optimizing pricing strategies.
Customer Base:
- Enhance Customer Engagement: Focus on building stronger relationships with customers across all regions. Implement loyalty programs, personalized marketing, and customer feedback mechanisms to improve customer satisfaction and retention.
- Monitor Customer Base: Keep an eye on the distribution of customers across regions. If any region is experiencing declining customer counts, take proactive measures to address the issue and retain customers.
Sales and Profit:
- Set Growth Targets: Establish specific annual growth targets for both sales and profit. These targets should be aligned with the company’s overall strategic objectives.
- Regularly Review Sales Mix: Continuously assess the contribution of each product category to overall sales and profit. Adjust inventory and marketing strategies accordingly to optimize the sales mix.
Shipping Mode Efficiency:
- Optimize Shipping Modes: Analyze the cost-effectiveness of different shipping modes and encourage customers to use more cost-efficient options. Monitor the adoption rate of each shipping mode and adjust pricing and incentives accordingly.
Inventory Management:
- Improve Inventory Turnover: Reduce excess inventory by optimizing reorder points, improving demand forecasting, and identifying slow-moving products. This will help free up capital and reduce carrying costs.
By implementing these recommendations and closely monitoring the relevant KPIs, Jankin Superstores can work towards achieving sustained growth, increased profitability, and improved customer satisfaction.
Let’s connect on LinkedIn:
