Police Department Crime Analysis Dashboard
📊 Project Overview
This is a comprehensive crime analysis examining 60,000 criminal incidents to identify patterns, hotspots, and trends. This analysis supports data-driven decision-making for law enforcement resource allocation and crime prevention strategies.
Tools Used: Microsoft Excel (Advanced formulas, Pivot Tables, Conditional Formatting, Heatmaps, Data Visualization)
🎯 Key Objectives
- Identify crime patterns by time, location, and demographics
- Analyze suspect and victim profiles for targeted interventions
- Track crime trends and case resolution rates
- Provide actionable insights for resource allocation and crime prevention
📈 Dashboards
Dashboard 1: Time & Trend Analysis

Metrics Analyzed:
- Total crimes: 40,740 incidents
- Crime timing patterns (hourly, daily, weekly)
- Month-over-month trends and seasonal variations
- Crime characteristics (injuries, property damage, weapons)
Dashboard 2: Geographic & Demographic Analysis

Metrics Analyzed:
- Crime distribution across states and locations
- Suspect demographics (race, age)
- Crime types and severity levels
- Case status tracking (open, pending, closed)
📥 Download Files
📊 Download Excel Workbook - Full analysis with all data and calculations
🔍 Key Insights
Crime Patterns & Timing:
- 40,740 total crimes with 79.7% resulting in injuries (32,472 cases)
- 2 PM, 12 PM, and 8 PM are the most dangerous times, contributing 41.7% of all crimes
- Weekday crimes (71%) significantly outnumber weekend crimes (29%)
- August shows highest crime volume (3,507 incidents) with +286 MoM increase
- Peak crime days: Thursday and Friday with 800+ incidents each
Crime Characteristics:
- 56.6% of crimes involved weapons (23,839 incidents)
- 86.6% resulted in property damage (35,273 properties)
- 24.69% of cases under investigation (14,814 cases)
- 66.90% of cases closed, 19.60% remain open, 13.51% pending
Geographic Distribution:
- California leads with 11.2K crimes, followed by Pennsylvania (8.0K)
- Top crime locations: Residential areas (12.0K), Industrial areas (9.0K), University campus (7.8K)
- Tech-related crimes are rising in residential areas and university campuses
Suspect & Victim Demographics:
- Black suspects: 8,000 (2024) vs 6,000 (2023) - significant increase
- Average victim age: 52 years
- Average suspect age: 31 years
- 25-34 age group shows highest suspect volume (20,971 suspects)
Crime Types & Severity:
- High severity crimes: 20.2K incidents (top priority)
- Top crime types: Cyber security (7.2K), Domestic offenses (7.2K), Burglary (7.2K)
- Critical areas: Residential areas (9.0K) and Industrial areas account for 62% of total crimes
💡 Recommendations
Immediate Actions:
- Increase patrols during peak hours (2 PM, 12 PM, 8 PM) which account for 41.7% of crimes
- Deploy additional resources on Thursdays and Fridays (800+ crimes per day)
- Focus digital surveillance and cybersecurity taskforce on rising tech-related crimes (62% of total)
- Prioritize burglary and drug offense crackdowns with localized enforcement
Strategic Initiatives:
- Launch youth intervention programs targeting 25-34 age demographic (highest suspect volume)
- Increase surveillance in high-crime locations: Residential areas (12K), Industrial areas (9K), University campuses (7.8K)
- Focus law enforcement in California and Pennsylvania which contribute 55.7% of all crimes
- Partner with local authorities in highlighted zones for early-warning systems
- Expand local intervention programs targeting youth and vulnerable populations
Operational Improvements:
- Reduce pending cases from 13.51% to <10% through better case management
- Investigate August crime spike (286 MoM increase) to identify root causes
- Address weekday crime concentration (71%) with targeted weekday patrols
🛠️ Technical Skills Demonstrated
- Crime data analysis and pattern recognition
- Time series analysis and trend forecasting
- Geographic crime mapping and hotspot identification
- Advanced Excel formulas (SUMIFS, COUNTIFS, AVERAGEIFS)
- Interactive dashboards with multiple filters and slicers
- Heatmap creation for time-based analysis
- Demographic analysis and visualization
- KPI tracking and performance metrics
🎓 Skills Demonstrated
- Law enforcement data analytics
- Pattern recognition and predictive analysis
- Geographic information analysis
- Statistical analysis and data interpretation
- Dashboard design with conditional formatting
- Business intelligence for public safety
- Data-driven decision support
This analysis demonstrates proficiency in crime analytics, time series analysis, and data visualization using Microsoft Excel for law enforcement applications.