Data Visualization Case Study
This interactive web application offers a story-driven business analytics case study centered on Apex Logistics, a regional last-mile delivery provider. Stepping into the role of a junior data analytics intern reporting to the Director of Operations, students transform raw delivery records into clear visual stories, diagnostic distribution charts, and an executive-ready operations dashboard.
Learning Objectives
By completing this interactive case study, students learn to:
- Construct PivotTables & Crosstabulations: Build two-way frequency tables, group continuous variables, and calculate row and column percentages.
- Design Effective Categorical Visuals: Select and format vertical column and horizontal bar charts from aggregated summaries.
- Analyze Distribution Spread & Outliers: Create single and comparative box plots to evaluate median, IQR, and isolated operational delay outliers.
- Examine Bivariate Associations & Trends: Plot scatter diagrams, calculate Pearson correlation coefficients (
=CORREL), and build line charts to track 30-day performance. - Build Dynamic Multi-Series PivotCharts: Generate multi-line and stacked column PivotCharts directly from transaction records.
- Assemble Executive Dashboards: Create KPI summary cards and assemble a clean grid layout to support strategic decision-making.
Interactive Modules & Curricular Mapping
Module 1: Operational Summaries & Crosstabulations
Students begin by examining raw transaction records to understand baseline operations across four regional fulfillment hubs. Working in Microsoft Excel, students construct two-way frequency crosstabs, compute row percentages to evaluate green fleet adoption, calculate average delivery times across vehicle types, and group continuous trip distances into custom bins.
Module 2: Categorical Comparisons with Bar & Column Charts
Students translate dense summary tables into executive-friendly visuals. They create vertical column charts to contrast regional turnaround times and build sorted horizontal bar charts to visualize overall fleet volume across vehicle categories.
Module 3: Delivery Distributions & Outlier Spotting
Moving beyond simple averages, students investigate extreme customer delays by constructing single and comparative Box and Whisker plots. They evaluate medians, quartiles, and interquartile ranges (IQR) across vehicle fleets while identifying root causes such as traffic bottlenecks and weather disruptions.
Module 4: Bivariate Relationships & Time-Series Trends
Students evaluate operational relationships by pairing trip distance with delivery duration on a scatter plot and computing the correlation coefficient. They also plot daily delivery averages over a 30-day window to track network stability over time.
Module 5: Dynamic Multi-Series PivotCharts
Students build interactive visuals linked directly to underlying datasets. They construct multi-line PivotCharts to compare daily fleet durations and design 100% stacked column charts to diagnose delay root causes by hub.
Module 6: Executive Board Operations Dashboard
In the final capstone module, students assemble top-line KPI summary cards (Delivery Volume, On-Time Service Level, CSAT, and Fuel Expenditure) and arrange diagnostic charts into a professional 2x2 dashboard grid. The module culminates in a strategic briefing memo for executive leadership.