Road Accident Dashboard Project Using Excel

Introduction:

A major issue that has an impact on communities all across the world is road safety. I'm excited to announce the Road Accident Analytics Dashboard, a powerful tool designed to analyze and visualize road accident data for the years 2021 and 2022. This Excel-based dashboard empowers stakeholders with valuable insights to make data-driven decisions and pave the way towards safer roads.

Requirements and KPIs:

Clients want to create a road accident dashboard for the years 2021 and 2022 so that they can have insight into the requirements.

?? Total Casualties took place after the accident

?? Total Casualties & percentage of total with respect to accident severity and maximum casualties by type of vehicle

?? Total Casualties with respect to vehicle type

??Monthly trend showing a comparison of casualties for the current year and the previous year

?? Maximum Casualties by Road type

?? Distribution of total casualties by road surface

?? Relation between Casualties by Area/ Location & by Day/ Night

Unveiling Key Insights:

The Road Accident Analytics Dashboard provides an array of key performance indicators and metrics, offering profound insights into road accidents and casualties. Let's dive into some of the significant findings:

1. Total Casualties Analysis: The dashboard reveals that a staggering 417,883 casualties occurred after accidents during the two-year period. This sobering statistic highlights the urgency to prioritize road safety measures.
2. Casualties by Severity and Vehicle Type: Analysis of accident severity and the type of vehicles involved showcases intriguing patterns. Car accidents accounted for the highest number of casualties, contributing to 79.8% of the total. Conversely, accidents involving other vehicle types exhibited minimal casualties.
3. Casualties by Vehicle Type: The ability to understand casualties based on different vehicle types is invaluable for implementing targeted safety interventions. The dashboard presents a comprehensive breakdown, enabling stakeholders to focus efforts where they are most needed.

1. Monthly Trend Analysis: By comparing casualty trends between the current and previous years on a monthly basis, the dashboard identifies critical periods and seasonal patterns. This knowledge allows authorities to implement measures and campaigns to reduce accidents during high-risk periods.

1. Road Type Analysis: Identifying the road types associated with the highest number of casualties is instrumental in developing effective road safety strategies. The dashboard highlights specific road types requiring enhanced safety measures and infrastructure improvements.

1. Casualties Distribution by Road Surface: Understanding casualty distribution based on different road surface conditions helps pinpoint areas where road maintenance and surface improvements are essential. This data-driven approach enhances decision-making and resource allocation.

1. Casualties Relation by Area/Location & Day/Night: Analyzing the relationship between casualties and the area/location of accidents, as well as the distinction between day and night accidents, provides valuable insights. These findings enable targeted interventions to improve road safety in specific areas and during specific timeframes.

Harnessing Excel's Power:

The Road Accident Analytics Dashboard was developed using Excel's robust features, enabling seamless data cleaning, processing, analysis, and visualization. Leveraging its capabilities, I transformed raw data into compelling charts, graphs, and interactive visuals. This user-friendly interface ensures stakeholders can easily access and comprehend the information, promoting effective decision-making.

Steps

1. Requirement gathering from client

2. Identify the Stakeholders of the Project

3. Data cleaning as per the requirement

4. Data Processing by adding some customized columns in data

5. Data Analysis by Pivot Tables and Excel Functions

6. Data Visualization to create charts and custom sheets to show the insights

7. Report/ Dashboard creation from start to end

Steps Followed

The following steps were followed during the project:

1. Data Cleaning: In this step, the data was cleaned by removing inconsistencies, errors, and duplicates. The goal was to ensure the accuracy and reliability of the data for further analysis. Example- Replaced Fetal to Fatal in Accident_Severity Column, Replaced Auto traffic sig to Auto traffic signal in Junction_Control Column

1. Data Processing: New columns were created to organize, sort, and filter the data for extracting meaningful insights. Example- Add a column of Month by applying formula =TEXT(B2,"MMMM"), Add a column of Year by applying formula =TEXT(B23,"MMMM")

1. Data Analysis: Various statistical methods were applied to derive valuable insights from the data.
2. Data Visualization: Excel was utilized as a visualization tool to create attractive charts, graphs, and interactive visuals for presenting the data in an easily understandable manner.
3. Dashboard Creation: Finally, a dashboard was built in Excel by incorporating slicers and timelines, enabling users to interact with the data and explore different perspectives.

Key Insights:

The key insights obtained from this project are truly eye-opening:

? The total number of casualties that occurred after accidents is a staggering 417,883.

? Car accidents accounted for the highest number of casualties, contributing to 79.8% of the total. On the other hand, casualties were minimal in accidents involving other vehicle types.

? In the years 2021 and 2022, the total casualties were 222,146 and 195,737, respectively. Analyzing the trends between these two years provides valuable insights for targeted interventions.

? The analysis revealed specific months with the highest and lowest casualties, enabling focused strategies for different times of the year.

? By identifying the road types and surfaces associated with the maximum casualties, authorities can prioritize safety measures and road maintenance efforts accordingly.

This Road Accident Analytics Dashboard opens the door to data-driven decision-making, enabling stakeholders to implement evidence-based interventions that enhance road safety. It serves as a valuable tool for policymakers, traffic authorities, and safety advocates alike.

If you're interested in exploring the dashboard and gaining deeper insights, please reach out to me. I would be delighted to share more about this project and discuss how it can benefit your organization or community. Together, we can make a significant impact on road safety! ????