Excel Revenue & Customer Analytics Dashboard

1. Project Title & Branding

Project Name: Business Master Interactive Sales & Customer Insights Dashboard.
System Environment: Microsoft Excel (utilizing Power Pivot, advanced charting, and custom UI design layers).

Problem: The organization lacked a unified, scannable interface to track multi-year sales performance, regional revenue distributions, and customer acquisition metrics simultaneously.
Objective: To build a highly interactive, enterprise-grade analytical dashboard that empowers executives to analyze 2013 fiscal data while benchmarking performance against previous years and monthly timelines.

Executive Leadership: For tracking macro organizational health, total revenue targets, and market share expansion.
Sales & Product Managers: For evaluating granular product subcategory performance, tracking seasonal revenue patterns, and identifying low-performing inventory.
Marketing & CRM Teams: For monitoring new vs. returning customer distributions and regional customer densities.

Transaction Ledger: Historical sales records containing order IDs, quantities sold, unit prices, and transaction dates spanning 2011 to 2014.
Customer Master Data: Segmented account logs detailing customer locations and acquisition types (New vs. Old).
Product Catalog: A multi-tiered hierarchy mapping individual SKUs to specific subcategories and parent categories.

Data Extraction: Imported flat transactional data and dimensional lookup tables into Excel's Power Pivot engine.
Data Cleansing: Standardized regional naming conventions, handled missing variables, and formatted monetary expressions into unified currencies.
Time-Intelligence Alignment: Established a dedicated date framework to enable smooth month-over-month (MoM) and year-over-year (YoY) performance calculations.

Schema Design: Implemented a Star Schema within Power Pivot.
Fact Table: Centered around the core sales transactions ledger (containing metrics like Revenue, Quantity, and Orders).
Dimension Tables: Surrounding lookup tables including Calendar/Time, Geography/Regions, Customers, and Product Hierarchy linked via clean one-to-many ($1 \rightarrow *$) relationships.

Revenue: Total gross dollar value generated from sales ($43,922,051 for the active reporting period).
Quantity: Total absolute volume of product units moved (131,788 units).
Number of Orders: Total count of unique invoices processed (14,182 orders).
Average Order Value (AOV): Calculated as $\frac{\text{Total Revenue}}{\text{Total Orders}}$, averaging $7,197.
Customer Count: Unique active customer entities, totaling 11,095 accounts.

Design Philosophy: Replicated a modern BI application look inside Excel using a dark blue background framework to prevent eye strain and maximize visual contrast.
Card UI Concept: Grouped distinct analytical views into isolated, light-teal container cards with rounded edges to make the layout clean and easily scannable.

Executive Summary: Focuses on macro trends. Features high-level KPI cards with directional indicators showing Year-Over-Year shifts.
Visualizations:
- Revenue by Region Bar Chart: Ranks regional performance, highlighting leading territories like the Northwest and Southwest regions.
- Revenue by Year Column Chart: Shows high-level growth trajectories across 2011 ($12.6M), 2012 ($33.7M), 2013 ($43.9M), and 2014 ($20.1M).
- Revenue by Orders & Unit Price Combo Chart: Correlates absolute financial returns with structural order volume patterns.

Granular Product Focus: Built for operational deep-dives into SKU and subcategory variances.
Visualizations:
- Monthly Revenue Trend Line Chart: Features a dual-line system tracking current-month revenue alongside Last Year (LY) benchmarks across the 12-month calendar.
- Top 5 & Bottom 5 Product Subcategories: Side-by-side bar charts contrasting major drivers (e.g., Road Bikes at $14.8M) with slow-moving inventory (e.g., Socks, Cleaners).
- Performance Matrix Tables: Data sheets calculating exact performance metrics down to specific item sizes and styles.

Market Penetration Focus: Measures customer demographic health and onboarding momentum.
Visualizations:
- Cohort Analysis Stacked Bar Chart: Tracks customer account segments dynamically across multiple fiscal years.
- Regional Customer Footprint: Highlights customer concentration per geography (e.g., France at 986 customers).
- YTD Cumulative Growth Line Chart: Tracks the cumulative percentage acceleration of customer acquisition across fiscal months.

Advanced measures were constructed in Power Pivot to provide context-aware time intelligence:

Last Month (LM) Variance: Evaluates velocity against the immediately preceding month.
Same Month Last Year (SMLY) Variance: Mitigates seasonal anomalies by benchmarking current performance against the identical month from the prior fiscal cycle.
New vs. Old Customer Split: Dynamically segments customer records based on historical transaction flags.

Typography: Clean, uniform presentation style with bolded metrics to establish a clear visual hierarchy.
Color Coding: Uses distinct colors to differentiate metrics (such as deep blue and purple for regional bars, and contrasting line tracks for historical comparisons).
Dynamic Header Slices: Tailored dashboard headers that change dynamically depending on the active slicer parameters to ensure clarity.

A dedicated right-hand global filter layout allows users to manipulate the data on the fly:

Temporal Slicers: Quick filters for Year (2011–2014) and Month.
Product Slicer: Breaks down visuals by Product Category (Accessories, Bikes, Clothing, Components).
Geographic Slicer: Cross-filters by Region Name (Australia, Canada, Central, France, Germany, Northeast, Northwest, Southeast, Southwest).

Product Dominance: Bike sales serve as the primary engine for organizational revenue, with Road Bikes ($14.8M) and Mountain Bikes ($13.0M) driving the highest volumes.
Customer Acquisition Health: In 2013, 79% of the customer ecosystem was made up of New Customers, highlighting successful marketing reach, though pointing to a potential opportunity to improve customer retention (21% returning).
Geographic Footprint: The Northwest and Southwest territories represent the largest domestic revenue strongholds.

Inventory Reallocation: Scale back inventory budgets for bottom-performing subcategories like Socks, Locks, and Pumps, and redirect capital toward high-margin Bike assemblies.
Retention Marketing: Launch dedicated loyalty and email campaigns targeting the large block of one-time New Customers to shift them into high-value Old Customer cohorts.
Targeted Regional Expansion: Expand regional marketing playbooks from the successful Northwest division into lower-volume international zones like the United Kingdom.

Platform Constraints: Building a heavy data asset inside Excel can impact file sizes and performance compared to standalone BI tools.
Manual Refreshing: The underlying database connection requires manual trigger updates rather than relying on automated cloud scheduling pipelines.

Migration to Power BI: Move the data model into Power BI to utilize cloud refreshes, automated report scheduling, and advanced access controls.
Predictive Forecasting: Incorporate machine learning scripts (such as Python integration or Excel's Forecast Sheet tool) to project upcoming seasonal revenue peaks into 2015.