Client Problem
A small retail business has daily sales records, but the information is scattered and unstructured. The owner lacks clarity on:
- Which products and categories sell the most.
- Which days of the week generate the most revenue.
- Which categories are the most profitable.
- Which inventory requires immediate attention.
- What their main business KPIs are.
- How to make data-driven decisions.
Analysis Workflow
1
Raw Data
2
Data Cleaning
3
KPI Calculation
4
Dashboard Design
5
Insights
6
Business Decisions
Dataset & Data Cleaning
1. Raw Data Structure
| Date | Product | Category | Units | Unit Price | Total Revenue | Est. Cost | Profit | Payment | Sales Rep | Branch |
|---|---|---|---|---|---|---|---|---|---|---|
| May 12, 2026 | Premium Chocolate | Candy | 15 | $2.50 | $37.50 | $15.00 | $22.50 | Card | Ana | Downtown |
| May 12, 2026 | Gummy Mix | Candy | 30 | $1.00 | $30.00 | $9.00 | $21.00 | Cash | Beto | North |
| May 13, 2026 | 600ml Soda | Beverages | 20 | $1.50 | $30.00 | $14.00 | $16.00 | Card | Ana | Downtown |
Sample preview: full dataset includes 250+ simulated sales records.
2. Data Cleaning Process
- ✔ Elimination of duplicates and empty rows.
- ✔ Uniform formatting of dates (Month Day, Year) and currency.
- ✔ Data validation (Dropdowns for Category, Sales Rep, Branch).
- ✔ Calculated columns: Total Revenue (Units × Price).
- ✔ Calculated columns: Profit (Revenue - Cost).
- ✔ Text standardization (No extra spaces, title format).
Key Performance Indicators
Total Sales
$48,250
Total Profit
$21,100
Avg Order Value
$32.40
Units Sold
1,480
Sample KPI Logic
Revenue = Units × Unit Price
Profit = Revenue - Estimated Cost
Profit Margin = Profit / Revenue
Average Order Value = Total Revenue / Orders
Visualizations & Filters
Applied Filters (Slicers)
Month: May
Category: All
Branch: Downtown
Sales Rep: Ana
Sales by Category
Candy$22,000
Beverages$15,000
Snacks$11,250
Profit by Category
Candy$10,500
Beverages$6,200
Snacks$4,400
Top 5 Products by Revenue
Choc. Prem
Gummies
Soda
Cookies
Water
Weekly Sales Trend
Mon
Tue
Wed
Thu
Fri
Sat
Sun
Insights & Recommendations
Insights
-
→
Best Performer: "Premium Chocolate" generates the highest absolute profit margin, even if not the highest in units sold.
-
→
Attention Required: The "Snacks" category has high rotation but a low profit margin (25%).
-
→
Peak Days: In the sample dataset, Fridays and Saturdays represent the strongest sales days. Tuesdays have almost no movement.
-
→
Payment Method: In the sample dataset, 70% of high-value sales are paid by card.
Recommendations
-
✔
Inventory: Increase stock of "Premium Chocolate" for weekends to avoid stockouts.
-
✔
Pricing: Review prices of the "Snacks" category or renegotiate with suppliers to improve margins.
-
✔
Promotions: Create "2x1" promotions or discounts on Tuesdays to encourage foot traffic.
-
✔
Purchasing: Optimize orders focusing on strong categories (Candy and Beverages) to improve cash flow.
Business Impact
- ✔ Identified top-selling products and strongest categories.
- ✔ Detected low-margin product groups.
- ✔ Highlighted peak sales days for inventory planning.
- ✔ Supported pricing and promotion decisions.
- ✔ Improved visibility for small business owners.
What I Delivered
- ▸ Clean sales dataset structure.
- ▸ KPI framework.
- ▸ Dashboard preview.
- ▸ Sales and profit visualizations.
- ▸ Business insights and recommendations.
- ▸ Portfolio-ready case study.
Tools Used
- ▸ Microsoft Excel / Google Sheets for dashboard structure.
- ▸ CSV dataset for sales records.
- ▸ Pivot-style analysis for KPI summaries.
- ▸ HTML / CSS for portfolio presentation.
- ▸ Manual review for business insights.
This is a portfolio preview. The full editable dashboard, formulas, and source files are reserved for contracted client work.
🔒 Excel Dashboard (.xlsx)
🔒 CSV Database
🔒 PDF Executive Summary