Sales Dashboard – Superstore Dataset (Excel)

Overview

This project analyzes Superstore retail sales data in Microsoft Excel to uncover trends in revenue, profit, customer segments, regional performance, and return impact. The goal is to demonstrate practical Excel analytics skills used in business environments: Power Query (ETL), pivot-driven analysis, KPI modeling, and dashboard design.

Business Context

This analysis simulates a retail company evaluating sales performance, profitability, customer behavior, and operational efficiency to support data-driven decision making by executives and category managers.

Objectives

• Define and calculate core KPIs: revenue, profit, profit margin, units sold, and return rate
• Clean and standardize raw orders using Power Query (data types, text cleanup, de-duplication, derived date fields)
• Analyze performance using pivot tables and calculated fields (time trends, product mix, regional efficiency, segments, returns)
• Build an executive-style dashboard with connected slicers for interactive exploration

Dataset Overview

• Dataset: Superstore (public retail sample dataset)
• Time range: 2014–2017
• Granularity: One row per order line item
• Core tables: Orders, Returns

Tools & Skills Demonstrated

• Power Query: ETL, data types, text cleanup, de-duplication, derived date fields
• Pivot Tables: grouping, sorting, filters, calculated fields
• KPI Modeling: profit margin, return rate, performance comparisons
• Excel Functions: XLOOKUP, SUMIFS/COUNTIFS, IF/IFERROR, date & text functions
• Visualization: pivot charts, conditional formatting, slicers, dashboard layout

KPI Definitions

• Revenue: SUM(Sales)
• Profit: SUM(Profit)
• Profit Margin: Profit / Revenue
• Units Sold: SUM(Quantity)
• Return Rate: Returned Sales / Total Sales (sales-based return rate)

Before building KPIs, pivot tables, and charts, I cleaned and standardized the Superstore Orders dataset using Excel Power Query. The goal was to create a reliable, refreshable table (Clean_Orders) that serves as the single source of truth for all downstream analysis and dashboarding.

ETL Summary

• Input: Raw Orders data (.xls) preserved as Raw_Orders (no manual edits)
• Tool: Excel Power Query (Get & Transform)
• Output: Cleaned dataset loaded to Clean_Orders (used by all pivots, charts, and KPIs)
• Refreshable: Can update via Data → Refresh All without redoing manual steps

Power Query Applied Steps (Evidence)

Cleaning & Transformation Steps

1. Import & preserve raw data
   • Imported raw Superstore Orders and preserved the original table unchanged.
2. Standardize data types
   • Converted Order Date and Ship Date to Date-only values.
   • Set Postal Code to Text to preserve leading zeros.
   • Validated numeric fields (Sales, Profit, Discount, Quantity) as numeric types.
3. Clean text columns
   • Trimmed whitespace and removed non-printable characters across key text fields.
   • Improved pivot grouping stability (e.g., prevents "duplicate labels" caused by trailing spaces).
4. Remove invalid / blank records
   • Removed blank rows and incomplete records that could distort totals and trends.
5. Remove duplicates
   • Removed duplicates using a composite key: Order ID + Product ID.
   • Ensured each row represents a unique order line item (avoids double counting).
6. Create derived time fields
   • Created Order Year, Order Month, and Order Year-Month (YYYY-MM) for consistent time-series reporting.
7. Load to Clean_Orders
   • Loaded the final cleaned table to Excel for analysis, pivots, and dashboarding.

Why This Matters

• Accuracy: prevents inflated KPIs due to duplicates and invalid records
• Consistency: stable grouping and filtering in pivots (clean types + clean text)
• Repeatability: refreshable pipeline for maintainable analytics workflows

Business Question

How have sales and profitability evolved over time from 2014 to 2017? Are there trends, seasonality, or periods of volatility that could inform forecasting, inventory planning, and cost control?

Method

• Used Clean_Orders as the data source.
• Built pivot tables grouped by Order Year-Month.
• Created line charts for monthly revenue and monthly profit.
• Reviewed overall KPIs to contextualize the trends.

Results

Key Insights

• Revenue grows consistently: Sales show a clear upward trend from 2014 to 2017 with predictable seasonal peaks.
• Profit is volatile: While revenue grows steadily, profit swings significantly month-to-month, including several negative-profit periods.
• Margin pressure during growth: Revenue increases don't always translate to proportional profit gains, suggesting cost or discount issues.
• Seasonality is predictable: Recurring high-demand periods offer opportunities for better inventory and capacity planning.

Business Recommendations

• Plan inventory around seasonal peaks: Use historical patterns to stock high-demand products 60-90 days ahead of peak periods.
• Investigate negative-profit months: Drill down by category, discount level, and shipping method to find the root causes.
• Set discount limits: Require manager approval for discounts above 20%, especially on low-margin products like Furniture.
• Track profit margin monthly: Add margin to executive dashboards alongside revenue to prevent hidden profit erosion.

Business Question

Which categories and products contribute the most to revenue and profit? Where do profitability differences suggest pricing, discounting, inventory, or product strategy changes?

Method

• Used Clean_Orders as the data source.
• Built category pivots for Revenue, Profit, and Profit Margin (Profit / Sales).
• Built a product pivot sorted by Profit (descending) to identify top profit contributors.
• Created pivot charts with labels for fast comparison.

Category KPI Summary

Results (Charts)

Key Insights

• Technology and Office Supplies drive 93.6% of profit: These two categories generate $267,633 of the $286,014 total profit despite being only 67.7% of revenue.
• Furniture has a margin problem: Furniture generates 32.3% of revenue but only 6.4% of profit, with a 2.5% margin that's 7x lower than top categories.
• Profit is concentrated in few products: The top 10 products contribute a disproportionate share of total profit.
• Categories need different strategies: The 7x margin difference between categories means uniform pricing and discount policies don't work.

Business Recommendations

• Prioritize high-margin category inventory: Ensure 98%+ in-stock rates for Technology and Office Supplies to protect the profit core.
• Fix Furniture profitability: Analyze Furniture by sub-category, discount level, and shipping method to find margin killers. Raise prices or reduce discounts.
• Limit discounts by category margin: Set maximum discounts based on category baseline–Technology/Office Supplies (25% max), Furniture (15% max).
• Eliminate low-profit products: Review the bottom 20% of SKUs by profit and discontinue or consolidate overlapping low-margin items.

Business Question

Which regions are driving the most profit and where are we seeing efficiency gaps (low profit margin)? Which states and cities are the largest profit contributors–and which locations are consistently unprofitable?

Method

• Used Clean_Orders as the source for all pivots.
• Built region pivots for Revenue, Profit, and Profit Margin (Profit / Sales).
• Ranked Top 10 and Bottom 10 States and Cities by Profit.
• Visualized results using pivot charts with labels for readability.

Results (Charts)

Key Insights

• Profit is concentrated geographically: California and New York alone generate ~52% of total profit, creating geographic risk.
• Regional margins vary significantly: West and East regions show higher efficiency than South and Central, suggesting operational differences.
• Loss markets are concentrated: Bottom 10 states collectively represent $98K+ in losses, with Texas ($-25,729), Ohio ($-16,959), and Pennsylvania ($-15,560) as the biggest drags.
• Urban markets perform best: Top cities (NYC, LA, Seattle) demonstrate that dense markets support premium pricing and efficient delivery.

Business Recommendations

• Protect top-profit markets: Prioritize service levels and inventory for California and New York to defend the 52% profit base.
• Fix loss markets: Analyze Texas, Ohio, and Pennsylvania by category, discount level, and shipping method. Implement 15% discount caps and consider 3-5% price increases.
• Copy successful region strategies: Document and replicate West/East region practices in South/Central to improve their margins by 200-300 basis points.
• Add geographic tracking: Build state and city-level dashboards with monthly reviews to catch declining markets early.

Business Question

Which customer segments drive the most revenue and profit, and which segments are the most efficient (highest profit margin)? How should the business prioritize growth, pricing, and retention across Consumer, Corporate, and Home Office?

Method

• Used Clean_Orders as the data source.
• Built a pivot grouped by Segment for Sales, Profit, and Profit Margin.
• Created charts for Sales, Profit, and Profit Margin to compare segment performance.

Results (Charts)

Key Insights

• Consumer dominates volume but has lowest margin: Generates 50.6% of revenue ($1,161,013) but only 11.5% margin–every 1% improvement yields $11,600 profit.
• Home Office is most efficient: Achieves 14.0% margin, 220 basis points above average, despite being only 18.7% of revenue.
• Corporate balances scale and efficiency: Strong 13.0% margin with substantial $705,602 revenue represents the scalable profit sweet spot.
• Segment strategies should differ: The 250 basis point margin spread means uniform discounting over-discounts high-margin segments while under-addressing Consumer issues.

Business Recommendations

• Improve Consumer margin: Analyze Consumer by discount level, category, and shipping. Focus discounts on high-margin items only. Test 2-3% price increases on commodities.
• Grow Home Office segment: Launch targeted campaigns, subscription programs, and business-focused bundles to scale the highest-margin segment by 25-30%.
• Scale Corporate with contracts: Develop tiered programs with volume commitments and standing orders. Offer 10-15% discounts that still beat Consumer margins.
• Track segment KPIs monthly: Set margin targets (Consumer 12.5%, Corporate 13.5%, Home Office 14.0%) and tie incentives to segment profitability, not just revenue.

Business Question

• How much revenue and profit are impacted by returns?
• Which sub-categories have the highest return rates (sales-based)?
• Which products drive the greatest return impact by sales and profit?

Method

• Created a return flag by joining Orders to Returns using Order ID.
• Built pivots for Returned vs Not Returned (Sales, Profit, return %).
• Built return-rate pivots by Month and Sub-Category.
• Ranked products by Returned Sales and Returned Profit impact.

Results (Charts)

Key Insights

• Returns erase one month of profit: $180,504 in returned sales (7.86%) and $23,232 in lost profit (8.12%)–effectively eliminating one month's earnings.
• Return rates vary by category: Copiers (12.84%), Furnishings (10.48%), Appliances (9.42%) vs Binders (4.91%)–a 2.6x spread suggesting product-specific issues.
• Returns hit high-margin products harder: Returns destroy more profit (8.12%) than revenue (7.86%), indicating returned items tend to be higher-margin.
• Impact is concentrated: Top 10 products drive disproportionate return impact, enabling targeted fixes rather than broad policy changes.

Business Recommendations

• Fix high-return categories first: Focus on Copiers (12.84%) and Furnishings (10.48%). Improve product descriptions, add comparison charts, enhance packaging, and conduct vendor quality audits.
• Target high-impact products: Analyze top 10 return products for quality issues, listing accuracy, and packaging problems. Create setup guides and offer proactive customer support.
• Investigate return spikes: Filter high-return months by discount level, shipping method, region, and product to find root causes. Tighten discount limits on return-prone items.
• Track returns as a KPI: Add return rate tracking to dashboards by category and month. Set targets: high-complexity products (≤8%), standard (≤6%), commodities (≤4%).

Workbook Structure

• Raw_Orders – Original imported dataset (preserved; no manual edits)
• Clean_Orders – Power Query cleaned, analysis-ready table (single source of truth)
• Returns – Returned orders reference table
• People – Region/manager reference table
• Pivots – Pivot tables + pivot charts used for analysis and the dashboard
• Dashboard – Final profit-oriented, single-screen interactive dashboard

Dashboard Features

• Profit-oriented, single-screen layout designed for clean portfolio screenshots
• Connected slicers for Order Year and Order Month that filter every chart
• Core visuals: Monthly Profit Trend, Return Profit Impact, Top Profit Sub-Categories, Top Profit States, Segment Profit

Final Dashboard

Downloads

• Excel Workbook: Download Superstore_Portfolio_Excel_Project.xlsx
  Best viewed in Microsoft Excel (desktop) to use slicers and full interactivity.
• Raw Dataset: Download superstore_raw.xls

Conclusion

This project demonstrates an end-to-end Excel analytics workflow: importing raw retail data, transforming it with Power Query, building pivot-driven analysis, and delivering a polished, interactive dashboard optimized for stakeholder reporting. The final dashboard supports fast profit-based exploration by time and surfaces key profitability drivers (category mix, region performance, segment efficiency) along with the revenue/profit impact of returns.