E-Commerce Microsoft Excel Pivot Tables Multi-Market Analysis

E-Commerce Sales Performance

A comprehensive, interactive sales performance dashboard built entirely in Microsoft Excel — consolidating millions of transactions across 7 store divisions and multiple international markets into a single, decision-ready analytics interface.

$105.4M

Total Revenue

+16.1%

YoY Growth

6M+

Units Sold

9,191

Total Customers

Objective & Problem

The Objective

The goal was to design and deliver a comprehensive, interactive sales performance dashboard that empowers stakeholders to make faster, better-informed, and data-driven business decisions — replacing fragmented static reports with a single, self-refreshing analytics interface.

Specific aims included tracking revenue and YoY growth, identifying top-performing products and suppliers, mapping geographical sales distribution, analysing customer transaction behaviour, and revealing monthly and weekly demand cycles to support inventory planning and promotional scheduling.

The Business Problem

A mid-to-large e-commerce business operating across 7 regional divisions and multiple international markets was generating enormous volumes of transactional data daily — but had no consolidated view of performance. Critical insights were buried in spreadsheets, inaccessible to the decision-makers who needed them most.

Without structured analytics, resource allocation decisions — marketing spend, inventory investment, and supplier negotiations — were made with incomplete information. Operations teams lacked demand visibility to prevent under- or over-supply, particularly in high-velocity categories like Energy/Protein Beverages and Healthy Food.

My Approach

An end-to-end Excel analytics build — from raw data ingestion to a fully interactive dashboard.

1

Data Ingestion & Cleaning — Loaded raw transactional e-commerce records into Excel. Structured data across four interconnected sheets (CALCULATIONS, CALCULATION II, OVERVIEW, PRODUCT) to create a clean, relational data model enabling cross-dimensional analysis.
2

KPI Development & YoY Comparison — Used SUMIF, COUNTIF, IFERROR, and percentage change formulas to compute headline KPIs: total revenue ($105.4M), prior year revenue ($90.8M), YoY growth (16.1%), average unit price ($17.56), and total customer count (9,191).
3

Pivot Table Modelling — Built pivot tables summarizing millions of records by product, region, supplier, and time period. Applied advanced array formulas for dynamic ranked lookups and conditional aggregations across multiple analytical dimensions.
4

Insight Generation — Identified Dhaka as the dominant division ($40.8M, 38.7% of revenue), uncovered end-of-month demand spikes (Week 4: $33M vs $24.1M average), flagged bottom-performing products as discontinuation candidates, and mapped international market performance across Bangladesh, India, Lithuania, Poland, and Germany.
5

Dashboard Design — Assembled the OVERVIEW sheet as an interactive dashboard canvas featuring KPI headline cards, bar charts, donut charts, line/trend charts, map visuals, and conditional formatting — designed for non-technical stakeholder consumption.
6

Business Communication — Translated all findings into clear, actionable recommendations around inventory prioritization, supplier negotiations, regional investment, and digital payment strategy — without requiring technical expertise from end users.

Metrics Tracked

Six core performance dimensions monitored across the dashboard.

+16.1%

Revenue Growth — YoY comparison from $90.8M to $105.4M across all divisions

Top 10

Product Performance — ranked by revenue and units sold, with bottom-5 flagged for review

Supplier

Vendor Contribution — top suppliers ranked by quantity to identify key dependencies

7 Regions

Divisional Performance — Barisal, Chittagong, Dhaka, Khulna, Rajshahi, Rangpur, Sylhet

~90%

Card Transaction Rate — with mobile payments (~2%) flagged as an emerging growth channel

Week 4

Peak Demand Period — $33M in Week 4 vs $24.1M average in weeks 1–3 each month

Key Insights

What the data revealed — and the strategic actions it supports.

Dhaka drives disproportionate revenue. At $40.8M (38.7% of total divisional revenue), Dhaka is the dominant market by a significant margin over Chittagong ($19.8M) and Khulna ($11.3M). This concentration signals both a growth anchor and a dependency risk that warrants geographic diversification investment.

Week 4 demand spikes are consistent and predictable. The 4th week of each month consistently records $33.0M in sales — 37% above the $24.1M average for weeks 1–3. This pattern likely reflects payroll-driven purchasing behaviour and represents a clear opportunity for targeted end-of-month promotions and pre-stocked inventory.

International markets are performing competitively. Bangladesh leads international sales at $13.3M, closely followed by India ($13.2M), Lithuania ($11.7M), Poland ($11.0M), and Germany ($11.0M). The tight clustering across markets suggests untapped scale potential in currently underserved geographies.

Mobile payments represent a significant growth gap. With ~90% card-based transactions and mobile payments at only ~2%, the business is underinvesting in digital payment infrastructure. Expanding mobile payment options could improve conversion rates, particularly in mobile-first international markets.

Bottom-performing products signal portfolio inefficiency. Coffee K-Cups Tea ($201K) and Coffee Stirrers ($386K) generate minimal revenue relative to the overall portfolio. These represent clear candidates for discontinuation or repositioning — freeing up shelf space, working capital, and marketing budget for higher-yield categories.

Outcome & Impact

From Raw Transactions to Strategic Clarity

The delivered dashboard consolidated millions of transactional records across 7 divisions and multiple international markets into a single, interactive Excel interface. Business leaders and operations teams gained real-time visibility into revenue trends, product rankings, supplier performance, geographic distribution, and demand cycles — without needing technical support. The project replaced fragmented static reports with a scalable, reusable analytics framework that can be refreshed with new data, supporting ongoing business monitoring and faster, evidence-backed strategic decisions.

Divisional Revenue Breakdown

Division	Revenue	Share of Total	Strategic Note
Dhaka	$40.8M	38.7%	Dominant market; dependency risk if growth plateaus
Chittagong	$19.8M	18.8%	Strong second market; scale investment opportunity
Khulna	$11.3M	10.7%	Mid-tier performer with regional growth potential
Barisal / Rajshahi / Rangpur / Sylhet	Remaining	~31.8%	Underrepresented — candidates for targeted market development

Tech Stack

Microsoft Excel Pivot Tables SUMIF / COUNTIF Array Formulas Data Modeling Bar Charts Donut Chart Line / Trend Charts Map Visuals Conditional Formatting KPI Card Design

Key Learning Points

Data Preparation & Modeling — structuring raw transactional data into a relational, multi-sheet analytics framework
Analytical Thinking — identifying demand cycles, regional gaps, and portfolio inefficiencies from raw sales data
Visualization & Communication — designing an intuitive dashboard for non-technical stakeholder audiences
Business Acumen — translating data findings into concrete recommendations around inventory, supplier, and market strategy