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
| Fecha | Producto | Categoría | Unidades | P. Unitario | Ingreso Total | Costo Est. | Ganancia | Pago | Vendedor | Sucursal |
|---|---|---|---|---|---|---|---|---|---|---|
| 12/05/24 | Chocolate Premium | Dulces | 15 | $2.50 | $37.50 | $15.00 | $22.50 | Tarjeta | Ana | Centro |
| 12/05/24 | Gomitas Mix | Dulces | 30 | $1.00 | $30.00 | $9.00 | $21.00 | Efectivo | Beto | Norte |
| 13/05/24 | Refresco 600ml | Bebidas | 20 | $1.50 | $30.00 | $14.00 | $16.00 | Tarjeta | Ana | Centro |
2. Data Cleaning Process
- ✔ Elimination of duplicates and empty rows.
- ✔ Uniform formatting of dates (DD/MM/YYYY) and currency.
- ✔ Data validation (Dropdowns for Category, Vendor, 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
Best Selling Product
Chocolate Premium
Best Category
Dulces
Sales Growth
+12.5%
Profit Margin
43.7%
Visualizations & Filters
Applied Filters (Slicers)
Month: May
Category: All
Branch: Centro
Vendor: Ana
Sales by Category
Dulces$22,000
Bebidas$15,000
Snacks$11,250
Profit by Category
Dulces$10,500
Bebidas$6,200
Snacks$4,400
Top 5 Products by Revenue
Choc. Prem
Gomitas
Refresco
Galletas
Agua
Weekly Sales Trend
Mon
Tue
Wed
Thu
Fri
Sat
Sun
Insights & Recommendations
Insights
-
→
Best Performer: "Chocolate Premium" 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: Fridays and Saturdays concentrate 65% of weekly sales. Tuesdays have almost no movement.
-
→
Payment Method: 70% of high-value sales are paid by card.
Recommendations
-
✔
Inventory: Increase stock of "Chocolate Premium" 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 (Dulces and Bebidas) 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.
This is a portfolio preview. The full editable dashboard, formulas, and source files are available during client work.
🔒 Excel Dashboard (.xlsx)
🔒 CSV Database
🔒 PDF Executive Summary