This is an Excel project on road accident using data for the years 2021 and 2022. Road safety is a significant concern impacting communities worldwide. In response to this issue, I have developed a Road Accident Analytics Dashboard. The dashboard provides stakeholders with critical insights, enabling them to make informed, data-driven decisions to enhance road safety.

Disclaimer : All datasets and reports do not represent any company, institution or country, but just a dummy datasets to demonstrate capabilities of Excel.

Primary KPI: Total casualties taken place after the accident

Primary KPI: Total casualties and percentage of total with respect to accident severity and maximum casualties by type of vehicle

Secondary KPI: Total casualties with respect to vehicle type

Monthly trend showing comparism between casualties for current and previous year

Maximum casualties by Road type

Distribution of total casualties by Road surface

Relation between casualties by Area/Location and by Day/Night

The following Excel features were incorporated:

Data Cleaning Data Transformation

Filtering

Conditional function.

Pivot Tables and Pivot Chart

Data Visualization and Dashboard creation

Ministry of transport

Road Transport Department

Police Force

Emergency Service Department

Road safety corps

Transport Operators

Traffic Management Agencies

Public

Media

File Extension: .xlsx

Number of Rows: 307,974

Number of Fields: 23

You can interact with the report here

I created a "Data Analysis" sheet to summarize all the pivot table data for ease of use by new users, developers, or clients. This sheet features nine pivot tables designed to summarize the data and highlight trends, focusing on the relationship between the number of casualties and various factors such as casualty type, vehicle type, road type, and location. Monthly trends for the years 2021 and 2022 were also analyzed.

Below is a snippet of the Data Analysis Sheet.

Finally, the dashboard was created by inserting and customizing pivot charts based on the corresponding pivot tables. To ensure a user-friendly and interactive experience, slicers and timelines were incorporated. Hyperlinks and connections were also added to relevant icons for seamless navigation.

Below is a snippet of the final dashboard in Excel.

📈 Total Casualties Analysis: The dashboard shows a significant total of 417,883 casualties resulting from accidents over a two-year period.

📆 Peak Months:The number of casualties was slightly higher in 2021 compared to 2022. The highest number of casualties occurred in October and November for both years, while January and February saw the fewest casualties.

🚴‍♂️ Casualties by Vehicle Type: Car accidents were responsible for the majority of casualties, making up 79.8% of the total. Accidents involving other vehicle types resulted in minimal casualties.

🩸 Casualties by accident severity: Most casualties (84.1%) were of slight severity, with fatal severity casualties accounting for only 1.7%.

👣 Road Type Analysis: Single carriageway roads had the highest number of casualties (310,100), whereas slip roads had the fewest (5,100).

⛈️ Casualties Distribution by Road Surface: The majority of casualties (67%) occurred on dry road surfaces.

📖 Casualties Relation by Area/Location: Urban areas accounted for 61% of the casualties following accidents.

🌃 Casualties Distribution by light condition: Most casualties (73%) happened during daylight conditions.

The dashboard’s comparison of casualty trends between the current and previous years on a monthly basis highlights October and November as critical periods. During these high-risk months, the traffic police and other stakeholders must intensify their road safety measures.

Car drivers account for the majority of casualties To address this, targeted awareness campaigns, strict monitoring, and periodic check-ups on safe driving practices are essential.

Enhanced safety measures are necessary on single carriageway roads, and these roads should be upgraded to double lanes wherever feasible.

By analyzing casualty distribution across different road surface conditions, the dashboard helps identify areas where road maintenance and surface improvements are crucial.

Urban areas require focused interventions to improve road safety, particularly during daytime.

The Road Accident Analytics Dashboard facilitates data-driven decision-making, empowering stakeholders to implement evidence-based interventions that enhance road safety. It serves as a valuable tool for policymakers, traffic authorities, and safety advocates.

Thank you for your interest and time. Your valuable suggestions are welcome. Feel free to connect with me for further discussion here