Excel Projects

Project Title 1: Superstore Sales Performance Analysis 

Tools Used: Microsoft Excel; Used for data cleaning, PivotTable calculations, and the creation of the interactive dashboard (including Slicers and Timelines). 

Project Description: This project addresses the challenge of identifying key revenue drivers and operational inefficiencies within a large retail dataset. By consolidating fragmented sales data into a centralized dashboard, it solves the problem of "data overload," allowing stakeholders to pinpoint which products, regions, and customer segments are underperforming or driving growth. 

Problem Solved

• Consolidated disparate data points (Sales, Loss, Profit, Quantity) into a single source of truth.

• Identified high-performing states and regions to optimize logistics and marketing.

• Tracked return rates and sub-category sales to highlight potential quality control issues or inventory imbalances.

Key Insights 

• The store achieved a net profit of 442,528 against total sales of 11,488,062, representing a 3.85% overall profit margin.

• Annual sales showed consistent momentum, with 2017 sales increasing by approximately 16.6% compared to 2016.

• The Consumer segment is the primary revenue driver, contributing roughly 51% of total sales compared to Corporate and Home Office segments.

• The West region emerged as the most profitable, while the Central region received the highest discounts but yielded significantly lower profit margins (approx. 12% of total profit).

• Total losses were kept remarkably low at 9,994 representing only 0.08% of the total sales volume. 

Recommendations

1. Optimize Regional Discounting Strategy

The Region giving more discount chart shows a high correlation between heavy discounting in the Central region and lower profit returns.

• Implement a Profit-First discounting cap in the Central region. Reduce aggressive discounting by 10–15% on low-margin items.

• Reallocate those discount budgets to the West and East regions, where the conversion to profit is significantly higher.

2. Address the Sub-Category Performance Gap

The Highest and Lowest Sub-category chart indicates a massive disparity between top performers (like Copiers or Phones) and low-performers (like Fasteners or Art).

• Conduct a Stock Keeping Unit Rationalization. Bundle low-selling sub-categories (Fasteners/Labels) with high-performing Anchor products (Table/Chairs) to clear inventory.

• Focus marketing spend exclusively on the top 3 sub-categories to maximize the 3.85% profit margin.

3. High-Value Customer Retention

The Top 10 Customers chart shows that a tiny fraction of the database drives a massive portion of the 11M+ sales.

• Launch a VIP Loyalty Program specifically for these top 10% of customers. Offer them exclusive early access or volume-based rebates.

• Increasing retention among these high-volume buyers by just 5% could potentially boost total profit by an estimated 2-3%.

4. Mitigation of Most Returned Products

The Most Returned Product bar chart identifies specific items (like Advantus Push Pins and Staple-based wall units) that are consistently failing.

• Perform a Quality Audit on the top 5 most returned items. If the return rate exceeds 10% of its category sales, consider delisting the vendor or improving the packaging.

• Reducing returns by 20% will directly lower "Total Loss" and decrease reverse-logistics costs.

5. Capitalize on Seasonal Momentum

The Sales over the years trend shows a strong upward trajectory, particularly peaking toward 2017.

• Based on the 16.6% growth rate, increase inventory levels for top-selling products by 20% in Q3/Q4 of the upcoming year to avoid stockouts.

• Prevent lost sales during peak periods, ensuring you capture the full momentum of the year-over-year growth.

Project Title 2: Retail Performance & Inventory Dashboard 

Tools Used: Microsoft Excel (Data modeling, PivotTables, and Dashboard visualization). 

Project Description: This project addresses the challenge of inventory mismanagement and sales stagnation by visualizing historical retail data. It aims to synchronize inventory levels with regional demand and weather patterns to maximize revenue and minimize stockouts.

Problem Solved

• Identified which regions (like the East) or categories (like Furniture) are thriving, so the business knows where to invest more marketing budget.

• Tracked how weather (Sunny vs. Snowy) and promotions impact sales, the dashboard allows managers to predict staffing and stock needs with much higher accuracy. 

Key Insights 

• Furniture and Groceries emerged as top performers, contributing to a 22% higher revenue share compared to Electronics.

• Sales are split almost equally, with non-promoted items holding 50.25% of the total volume, suggesting high organic demand.

• The East Region is the primary driver of growth, outperforming the West by approximately 15% in total revenue.

• Sunny conditions drive the highest sales peak, showing a 30% increase in revenue compared to Snowy weather periods.

• Maintained a healthy stock-to-sales ratio, with inventory levels currently sitting at 201% of total units sold to ensure buffer against 2024 demand forecasts. 

Recommendations 

1. Inventory Rebalancing by Region

• The Data: The East and South regions are significantly outperforming the West.

• The Action: Shift 15% to 20% of inventory from West-region warehouses to the East and South. This reduces holding costs in underperforming areas and prevents out-of-stock lost revenue in high-demand zones.

2. Category Specific Marketing Shift

• The Data: Furniture and Groceries are the "cash cows," while Electronics is lagging in both inventory turnover and revenue.

• The Action: Launch a targeted 10% discount promo specifically for Electronics to clear slow-moving stock. Redirect the saved storage space to increase Furniture stock levels by 15%, as it shows the highest revenue yield.

3. Weather-Responsive Supply Chain

• The Data: There is a massive revenue spike during Sunny conditions and a sharp drop during Snowy/Rainy weather.

• The Action: Implement a dynamic logistics schedule. Increase delivery frequency to stores 48 hours before a forecasted Sunny period to ensure shelves are full. During Snowy forecasts, reduce perishable orders by 25% to minimize waste.

4. Promotion Strategy Overhaul

• The Data: No Promo sales (50.25%) are nearly identical to Promo sales (49.75%).

• The Action: This suggests your current promotions are not driving significant extra volume. Test a Flash Sale model (deeper discounts for shorter times) rather than long-term promos to see if you can push the promo-driven revenue share toward 60%.

5. Addressing the 2024 Forecast Gap

• The Data: The Demand Forecast and Units Sold lines show a steep decline leading into 2024.

• The Action: Perform a root-cause audit. If this dip is due to missing data, fix the pipeline. If it is a projected market downturn, immediately freeze new hiring and reduce bulk procurement contracts by 20% to preserve cash flow.