Government of all the states of India have introduced new policies to avoid accident due to driver response from the year 2023. This is a comparative study of how the number of accidents due to driver response has trended from 2022 to 2023 that is when no policies were in place to when policies have been established to avoid accidents due to driver response.
Download the Excel workbook for all the details about the project.
• Create a comparative report for accident due to driver response for the year 2022 and 2023.
• Create a multipage report with dashboards in each page. Make sure that there is enough space in each page and is covering all the eight columns starting from Alcohol Intake to Other Causes.
• Create a comparison study for all the columns individually comparing each column value for both the years. Repeat this process for all the columns.
• Create an interactive slicer for all the regions of the country. The Slicer should work for all the pages at once.
• Maintain a plain black background for all the pages. Keep it as vanilla as possible.
• Add Small transparent images to the headers to make the dashboard attractive.
• Provide cards for Accidents in 2022 and 2023 and it must be interactive with the slicer.
• Provide cards for Top State and Region for both 2022 and 2023.
• Provide buttons to move around the pages of the report and also provide a button that points to the dataset used.
• Removed all the unnecessary columns from the dataset that is derived from a government website.
• Created an index field in the dataset and sorted the dataset according to region to check if the data is correct or not in terms of the format. Renamed the columns to make it look more meaningful.
• Added new calculated columns namely Total_22, Total_23, Top_State_22, Top_State_23, Region_Sum_22, Region_Sum_23, Top_Region_22, Top_Region_23 for calculating the total accidents due to driver response for each year thereby calculating the top state and region for each year. The calculation was carried out using SUMIF and RANKEQ functions.
• Created a black plain background as required and built a plan for the layout. Setup pivot table and charts for each of the columns 2022 and 2023 value to understand the trend and pick up the actual insights required.
• Created a slicer for the regions column and made it interactive to all the charts using report connections.
• Created a folder with different logos to use as per the heading to be given on the dashboard on each page.
• Placed the charts on the dashboard and used 4 pages to place 8 charts. Formatted the chart to change the color and removed chart title, data labels, and all the unnecessary data on the chart. It was repeated for all the charts. Proper headings were given with appropriate logos beside it.
• Created interactive Cards for Total Accident 2022 and 2023 such that its value changes according to selection in the slicer. Created cards for top state and region for 2022 and 2023.
• Created Buttons for moving around the pages and a button pointing towards the dataset was also created and placed on the dashboard appropriately.
• The Total Accidents for 2023 has increased by 32.6% when compared to Total Accidents in 2022.
• The Top State and Region has remained the same for both 2022 and 2023.
• The Trends off all the different charts clearly shows that the cases for all kinds of driver response has increased considerably in 2023 when compared to 2022.
• From the analysis we can come to the conclusion that whatever policies government has introduced in 2023 in order to decrease the accidents due to user response has failed as per the trends as there is a considerable increase when compared to 2022 so all the measures that were introduced has to be reviewed again and proper changes have to be implemented in order to reduce the accident due to user response in the coming years.
An urge to understand how the Government policies regarding Accident due to driver caused issues have worked over the course of the year made me work on this report.