Introduction
In this analysis, I will examine incident data related to different departments and plants. The dataset contains information on the date of the incident, injury location, gender, incident type, location, and other relevant information. Please note that the dataset used for this analysis was obtained online, and no solutions were provided. I want to perform this analysis to demonstrate my proficiency in Excel and data analytics. You can download the final Excel workbook from this link.
The goal is to gain insights and answer specific business questions based on the provided tasks. By completing these tasks, my aim is to gain a comprehensive understanding of the incident data, identify patterns, and draw meaningful conclusions to support decision-making processes and improve overall safety measures within the organization.
Data Description
Date: The date when the incident occurred.
Injury Location: The specific body part or area affected by the injury.
Gender: The gender of the person involved in the incident.
Age Group: The age range of the person involved in the incident.
Incident Type: The type of injury or incident that occurred.
Plant: The location where the incident took place.
Report Type: The classification of the incident report.
Shift: The time shift during which the incident occurred.
Department: The department or area of the organization where the incident occurred.
Incident Cost: The cost associated with the incident, if applicable.
Days Lost: The number of days the person lost due to the incident.
Business Questions
Task 1
In the Incident Counts by Plant sheet, use the "Data" tab and populate the count of incidents by plant and by month (ignore the year), for instances where the Incident Cost was >= $100.
In the Incident Cost by Department sheet, use the "Data" tab and populate the sum of incident cost by the department.
What are the top 3 departments in terms of incident costs?
What is the cumulative % contribution of the top 3 departments towards the total incident cost?
Task 2
In the visualization tab, plot the sum of incident costs by week of the year
Task 3
Using Pivot Tables, answer the following questions:
For each age group, display incident types where the incident cost summed up to >= 20000
What is the Gender Breakdown by Department of instances where the days lost >= 1? E.g. in Maintenance, 18.75% of Incidents were reported by Females & 81.25% were reported by Males
For every incident type, what % does each plant contribute to the total incident cost for that incident type? E.g. Texas contributed 15.99% of incident costs related to burns
** Answers to Task 3 should be displayed as pivot tables in the "Pivots" sheet
Task 4
Provide 3 key insights from the raw data that have not been covered in the tasks above
What I Learned
The data contains a significant imbalance in the number of male employees compared to female employees, it is important to consider the context and potential biases in the analysis.
The Maintenance, Shipping, and Fabrication departments are the top three departments with the highest incident costs. The cumulative contribution of the top three departments towards the total incident cost is 40.07%. It is important for the organization to focus on improving safety measures and implementing preventive measures in these departments.
The 25-34 age group has a higher incident cost contribution compared to other age groups. This finding indicates the need for targeted safety training and awareness campaigns for employees within this age range.
The night shift has the highest incident costs. This insight highlights the importance of ensuring proper safety protocols and resources are in place during the night shift to mitigate risks and prevent incidents.
The Analysis
Task 1.1 In the Incident Counts by Plant sheet, use the "Data" tab and populate the count of incidents by plant and by month (ignore the year), for instances where the Incident Cost was >= $100.
Start by adding a new column in the "Data" tab to extract the month using the MONTH function. This will allow me to isolate the month from the date. Next, use the COUNTIFS function to populate the count of incidents based on conditions (Month, Plant and Incident Cost). I was able to accurately populate the count of incidents by plant and by month for cases where the Incident Cost is equal to or exceeds $100.
Task 1.2 In the Incident Cost by Department sheet, use the "Data" tab and populate the sum of incident cost by department.
For task 1.2, the SUMIFS function was used to sum the incident cost by the department.
To calculate the incident cost as a percentage of the total incident cost, I add up the incident cost for each department and divide it by the total incident cost.
Task 1.3 What are the top 3 departments in terms of incident costs?
I used conditional formatting to highlight the top 3 departments based on their incident costs. The Maintenance, Shipping, and Fabrication departments are the top three departments in terms of incident costs.
Task 1.4 What is the cumulative % contribution of the top 3 departments towards the total incident cost?
To calculate the sum of the top 3 departments in terms of incident costs, this formula adds up the first, second, and third largest values in the range C2:C10. The cumulative contribution of the top 3 departments is 40.07%.
Task 2 In the visualization tab, plot the sum of incident cost by week of the year
To extract the week number and year from the date in the “DATA” tab, I used the WEEKNUM and YEAR functions. These functions allowed me to create two new columns that display the week number and year respectively. I then created a pivot table to break down the incident cost based on the week of the year. Finally, I utilized a line chart to visualize the incident cost trends based on the week of the year.
Task 3a. For each age group, display incident types where the incident cost summed up to >= 20000.
I created a pivot table with the rows containing the age group and incident type. In the values section of the pivot table, I selected the sum of incident cost. I applied a value filter to the sum of incident costs, setting it to display values greater than or equal to 20,000. This pivot table shows the incident types where the incident cost adds up to 20,000 or higher for each age group.
Task 3b. What is the Gender Breakdown by Department of instances where the days lost >= 1? E.g. in Maintenance, 18.75% of Incidents were reported by Females & 81.25% were reported by Males
I created a pivot table and applied a filter only to include instances where the days lost are greater than or equal to 1. Set the columns to display gender (male and female) and rows to display the department. In the values section of the pivot table, I selected the count of incident cost and displayed it as a percentage of the row total.
For task 3c. For every incident type, what % does each plant contribute to the total incident cost for that incident type? E.g. Texas contributed 15.99% of incident costs related to burns
I created a pivot table with rows to display each incident type and columns to display each plant. In the values section of the pivot table, I selected the sum of incident costs and displayed it as a percentage of the total incident costs for each incident type.
Task 4 Provide 3 key insights from the raw data that have not been covered in the tasks above.
1. I created a pivot table with the row set as the shift, columns representing each age group, and a filter applied for days lost greater than or equal to 0.5.
Insights:
The data shows the distribution of incident costs based on age groups and shifts where the number of days lost is greater than or equal to 0.5. The 25-34 age group has a higher incident cost contribution compared to other age groups. Combining both the age group and shift data, we can observe that the night shift has the highest incident costs.
2. I created a pivot table with the row set as the age group and columns representing the gender. The values in the pivot table are displayed as a percentage of the incident costs.
Insights:
The data shows the distribution of incident costs based on gender and age group. Males accounted for a significantly higher proportion of incidents (90.02%) compared to females (9.98%). The age group with the highest incident proportion was the 25-34 age group, which comprised 35.12% of all incidents.
3. I created a pivot table that displays the sum of the incident cost for each department and report type.
Insights:
The data provides a snapshot of the incident costs across different departments and report types. Overall, the Maintenance, Shipping, and Fabrication departments had relatively high incident costs. Among the different report types, Lost Time incidents stand out with the highest costs.
Conclusion
In conclusion, this workplace incident analysis provides valuable insights into incident costs, departmental contributions, incident trends, and demographic factors. These findings can assist in formulating targeted safety strategies, prioritizing safety measures, and improving overall workplace safety to minimize incidents and their associated costs.
Further areas of study could include conducting root cause analysis for incidents with higher costs or frequency, examining age group and gender disparities in incidents to address potential biases, conducting a long-term analysis of incident costs to assess progress over time, and comparing incident data across plants to identify best practices. These studies would provide additional insights into specific areas for improvement, enhance safety strategies, and help reduce incidents and associated costs within the organization.
Thank you for taking the time to read my project! I hope you found the information valuable. If you'd like to connect on LinkedIn, please feel free to send me a connection request. If you have any feedback or suggestions, please don't hesitate to reach out to me. You can send me a message or contact me at jiashengqu@outlook.com.