Jacquiline Calderon

May 10, 2023 Refocus Data Analytics Course See the dashboard here: https://drive.google.com/file/d/1rz7H_DwZ4SugYa0X8ZdA0fgSp0u_gigH/view?usp=sharing PROBLEM As data analysts, we are here to help a medical company of 1000+ employees, assess the attendance performance of their team by analyzing multiple parameters such as arrival/ departure time, vacation, sick days, time off, etc. The company utilizes Clockster, a staff management app that runs attendance, scheduling, and payroll. The company provides business solutions by eliminating the manual tasks that come with managing schedules. With this, we are requested by the top management to measure the following data in particular: • • • Identify the most disciplined and undisciplined employees and divisions; Create a visualization to analyze weekdays and months when most tardiness (under time/late) and absences are observed ; Identify possible patterns in the organization as departments/department heads not implementing sanctions for employees with punctuality/ attendance issues. METHODOLOGY For our methodology, we used the company dataset from the third quarter of 2021 to the fourth quarter of 2023 and load them in SQL. We cleaned and manipulated the data for easier processing and then imported the data from SQL to Power BI. Using Power BI tools specifically merge queries and dashboard, we came up with our analysis and visualizations. From there, our team generated insights and eventually come up with recommendations and suggestions. FINDINGS 1. Employee Discipline Dashboard: This dashboard visualizes the statistics of employee behavior, particularly their discipline. With the given data, we're going to determine their punctuality and tardiness from the department to positions and employees individually. Tardiness per Department. This presents the count of punctuality and tardiness grouped by departments. The results indicate that punctuality was not taken seriously by all departments. The medical department has the highest discrepancy of punctuality to tardiness with 5.647 and 10,670 respectively. Status per Department. The overall status gives the composition of status to determine how various statuses take part of the whole value per department. In terms of being on time, employees working in the PBF department have the highest percentage with 57%. The support Centre department, however, has the highest percentage of late values at 36.6%. And for undertime, employees that are not affiliated with any department mentioned claims the highest with 48.8%. Employees by Attendance: This visual ranks the user’s or employees' count of punctuality and tardiness. This single visual can be interchanged with another visual with the button function below. This function shows the employees with the most and least performance in attendance. User '155509' has the most count of punctuality, making 805 occurrences. On the other hand, employee '125748' holds the highest tardiness score with '1,469'. Punctuality by Position, Tardiness and Absenteeism by Position: These two charts present punctuality and tardiness in terms of all positions in the company. From both visuals, medical staff has the highest number of punctuality and tardiness among all positions with 2,202 and 3,690 counts respectively. 2. Tardiness and Absenteeism Analysis: This dashboard contains an in-depth analysis of tardiness and absenteeism. These also show historical data of recorded values of tardiness, breaking down into monthly and daily records. Leaves Filed by Users shows the recorded leave schedules in a pie. There is a large portion of value depicting the leaves that are classified as day-offs with 94%. The remaining portions are being shared by mainly sick leaves, paid(compensatory), and unpaid leaves. Lastly, annual and special leaves are rarely given. Tardiness and Absenteeism per Month is a graph that analyzes the count of all tardiness and absenteeism values. Results said that the numbers significantly increase up to the third quarter of 2022, wherein the peak count is in October, recorded at 1,368. Approaching the last quarter of the year and the following year, there was a big fall in numbers with the lowest record of 400. Tardiness and Absenteeism per Day. This visual describes the breakdown of tardiness and absenteeism counts per day of the week. From there, it is shown that from the start of the weekday, it has the highest record of tardiness and absenteeism, decreasing towards the end of the weekday. Weekends have the lowest records of 2,929 on Saturday and 2,574 on Sunday. Tardiness and Absenteeism vs. Punctuality per Month. This line chart provides a comparison of tardiness and absenteeism against punctuality in a monthly breakdown. The visual conveyed that there is a gap in favor of tardiness. The gap maintains from the third quarter of 2021, wherein the gap increases from the first up to the third quarter of 2022 up into the third quarter of 2023, where the gap continuously decreases. 3. Employee Favoritism Dashboard: The dashboard solely aims to determine who's getting forgiven despite breaking time schedules and being favored with leaves and vacant schedules. Free Schedules per Month is a visual that analyzes the count of all schedules that are considered vacant. In the chart, an increasing record of values was visualized ending the year 2021 and there is a maintaining average of 270 free schedules in the whole year of 2022. Comparison of Net Pay and Monthly Rate. This line chart shows the comparison of net pay and monthly salary by their monthly sum. Overall, there is a clear gap in net pay towards monthly pay which is expected to happen since there is a considerable number of latest and undertime which is a valid reason to deduct the salary. Except in February 2022, where there is an overlap of two sets of values where the net pay overtook the expected monthly salary like in nurses, pharmacists, and warehouse officers (purchasing) Requested Leaves. The chart shows the requested leaves of employees in a pie chart to identify what type of leaves are the most common requests. Based on the graph, compensated leaves have the largest portion with 38%. Followed by sick and day-offs with 24% and 11%. No. of Filed Leaves per Position. This bar chart identifies the count of leaves filed by every department. From the chart, it shows that the pharmacy department has the highest number of 987 leave schedules INSIGHTS PBF Department has the highest percentage of punctuality among all departments with 57%. Also, it has the highest percentage of “On Time” status with 57%. On the other hand, Support Centre Department has the highest percentage of tardiness and absenteeism. They are also the department that has the highest percentage of values in terms of being “Late”. Individually, employee '155509', personal assistant, has the most count of punctuality, making 805 occurrences. While employee '125748', a medical staff from the medical department, holds the highest tardiness score. The following two employees with bad performance records are from the medical department too. Every month, there is a significant increase in tardiness and absenteeism up to the third quarter of 2022, wherein the peak count is 1,368 in October. Compared to punctuality, the number of tardiness has always been ahead of punctuality but approaching 2023, the gap decreased gradually Every week, weekdays have the highest records of tardiness and absenteeism compared to weekends, which were decreasing, having the lowest records on Saturday and Sunday. By free schedules, the Support Centre department has the highest number of free schedules. However, the pharmacy department has the highest number of 987 leave schedules. Overall, compensated leaves are the most common to request and approve, followed by sick and day-offs. But when filing leave, the most common request is a day off. Followed by sick leaves, paid(compensatory), and unpaid leaves, and lastly, annual and special leaves. RECOMMENDATIONS • • • • • Improve attendance monitoring system. Attendance entries should consider only one (1) time in and time out. Multiple logins create a lot of duplicates. Automate a function where the system calculates the employees’ total time spent at work per day. Impose stricter rules and regulations to combat tardiness. Implementing reward systems for excellent employees in terms of punctuality to encourage attending early or on time. Conduct research regarding the factors affecting the time discipline of employees. Finding the main cause of tardiness and absenteeism could be rooted beyond personal factors and could be influenced by external factors. Medical personnel must have flexible schedules since sudden demands in dire situations can happen anytime mostly in the health sector. In that way, medical personnel can have a stable work-life balance and reduce stress and burnout.