Portfolio Project

Empty-Package Shrink Dashboard

Excel Forecasting & BI

Open interactive view View all projects
Automation Analytics Excel Time-Series AWS

Context

Empty-package theft was rising fast. Recovered retail value was 5× higher in Q2 2023 than Q1 2021, and leaders needed one view of where it was happening.

Role

  • Built the Excel dashboard workflow (cleanup, drill-downs, and reporting).

Approach

  • Consolidated 5,900+ loss-prevention records (2021–2023).
  • Cleaned the data and anonymized employee IDs, DPCI codes, dates, locations, and retail values.
  • Built an interactive Excel dashboard with drill-downs by associate, department, and recovery location.
  • Summarized the findings in a short report.

Impact

  • Two recovery locations (anonymized) stood out as hotspots.
  • Shrink doubled in under 12 months at one hotspot, with the second doubling in a single quarter.
  • Three departments (anonymized) drove most recoveries; the top department jumped ~4× in two quarters.
  • Three associates (anonymized) accounted for ~47% of recovered value.

Data Cleanup and Governance

I turned messy loss-prevention logs into a usable dashboard while keeping people and locations anonymous.

  • Consolidated 5,900+ records (2021–2023) and standardized dates, locations, departments, and retail values.
  • Anonymized employee and store identifiers so the write-up is shareable.
  • Added quarter fields so trends are easy to track over time.

Dashboard and KPIs

  • Built pivot-based drill-downs by associate, department, and recovery location.
  • Tracked both retail value and item count to separate volume from severity.
  • Added trend views to spot fast growth (doubling patterns), not just totals.

Key Findings

  • Recovered retail value increased five-fold from Q1 2021 to Q2 2023.
  • Two recovery locations were hotspots; one doubled in <12 months and another doubled in a single quarter.
  • A small set of associates and departments drove a disproportionate share of recovered value (~47% from three associates).

What I'd Improve

  • Normalize by store traffic or shipments to separate growth from volume changes.
  • Add control charts or anomaly alerts to flag spikes automatically.
  • Automate refresh via scheduled exports so the dashboard stays current.

Links

PDF Excel Live Demo

Notes

Employee and location identifiers are anonymized in the write-up.