ANLY482 AY2017-18T2 Group13 Analysis & Findings

Home

Project Overview

Analysis & Findings

Project Management

Documentation

Main Page

DATA CLEANING & MAPPING

Manipulation and Cleaning of Training Records

In order to rectify these missing data points, we replaced the values whenever possible as the team hopes to analyze as many data points as possible. When replacement of values are not possible, records are excluded from analysis.

Missing data (Field)	Action
Start and End date	1. Attempted to find corresponding dates from exact course titles, unsuccessful 2. Attempt to take 'Create date' however inaccurate as record could have been created after actual start and end date 3. Excluded (Final action)
Category	Replaced missing data with corresponding records with exact course title

Groupings within Training Records

Course Titles Groupings

Using text explorer and vetting through our Sponsor, course titles were grouped in order to narrow down our analysis to specific areas. Grouping was done with a calculated field to facilitated breakdown analysis in later stage.

[Text explorer screenshot]

Groupings :

- Bulk

- HSEQ

- Equipment and Maintenance

- Miscellaneous

- Packaging

- Packaging and Raw Material

- Warehouse

- Others

Staff Department Groups

Staff department groupings (refer to Groupings within Staff Records) were also applied in Training Records.

Manipulation and Cleaning of Staff List

1. As the staff list provided were in separate sheets, data fields were standardized and merged into 1 data set.

Previous field name	New field name	Sheet affected
Job Code	Job Title	Dec'13 & Dec '14
Job Group	Staff Group	Dec'14
Designation	Job Title	Dec'15, Dec'16, Dec'17
Staff Grade	Staff Group	Dec'15, Dec'16, Dec'17
Section	Department	Dec'15, Dec'16, Dec'17
Department	Location	Dec '14, Dec'15, Dec'16, Dec'17

2. Standardization of variables within columns were also made to ensure consistency throughout the years.

Issue	Action taken	Sheet affected
Location column missing from sheets and location information was combined in the naming assigned to the departments (e.g. EMOS7000)	Location Group column created to extract the front letters from the department in Excel with a calculated field	Dec'13 & Dec '14
Department variables inconsistent with other years as it was combined with location. Other sheets used a single descriptive field to specify departments (e.g. EMOS Shared Services) [SNAPSHOT]	Used corresponding descriptive department name based on matching of employee number to other sheets	Dec’13 & Dec'14
Variables as Job Title was inconsistent with other years and were in the form of acronyms	Cross referenced employee number to other years and Training Records for corresponding job title	Dec'13 & Dec ‘14
Variables in Cost Centre , Citizenship and Serial Number fields were inconsistent over the years (e.g. Missing entire field) and had missing records that could not be matched with other years	As these fields were not within the scope and were advised by the Sponsor to exclude, these fields were excluded from the data analysis entirely	Dec'13, Dec'14, Dec'17

Groupings within Staff Records

As provided by the Sponsor, staff departments were grouped into the following :

- Bagging

- CSR

- Operations

- Technical

- Towhead

- Transport

- Warehouse

- Others: Includes all other departments not belonging to either of the above

Mapping Staff List and Training Record data set

Employee number is unique to each employee hence it was used an identifier. While an left join was considered, a full outer join was used instead to include all the records from both data sets independent from whether there were matching employee numbers.

As distinct count of employees in the Training Record is greater than that in the Staff List, the team attributed this to the fact that there are employees who received training but left in the short term to be captured in the Stall List records.

A full outer join would : - Include employees who undergone training but left the company in a short term hence will not be traceable in the annual Staff List - Include employees who did not undergo training However, with a full outer join, crucial fields such as employee number, location etc will be duplicated and may present differing variables. Meanwhile, employee records who appeared in staff list but not training records would have null values in training employee fields.

[Screenshot of tableau]

As such additional columns were created generalize columns that combined data from both sets

1.Employee Number

2.Location Group Filter

3.Staff Department Group Filter

Created to first obtain the department group information from the Training Records, if it is unavailable, information from the Staff List would be used.

4.Job Title Group Filter

Upon mapping, discrepancies in the staff department groups were found. Since employees underwent training based on the job title that they are categorized for training purposes,job title group in Training Records took precedence over job title group in Staff Records.

Parameters

INSIGHTS

Data Exploration

Wastage of resources on New Hires

As observed from yearly employee retention, the company experiences high employee turnover. Training of new hires who leave within the short term can result in course expenditure and wastage of resources to conduct these training since these employees would not have contributed enough to the company performance before leaving. Hence, we conduct an analysis of training records for new employees who leave within the year they were employed, using the comparison of Join Date and Leave Date.

[Total Yearly Course Expenditure for New Hires]

In 2017, course expenditure incurred increased by 28% as compared to expenditure in 2013. However this was with exception of the year 2016 where Katoen Natie saw the highest course expenditure for new hires incurred ($557,281), which was 2 times of the expenditure in the previous year. Total cost expenditure incurred for new hires who left accounted for half of the yearly total expenditure across 5 years.

JLT

[EMOS Course Expenditure]

Course expenditure for new hires who left from JLT increased tremendously after 2013 ($20,987). It peaked in 2016 ($233,644) before showing significant decrease in 2017 ($71,676). This wasted expenditure accounted 36% to 48% of overall course expenses for new hires between 2014 and 2017 with an exception for 2013 (69%). While this wasted expenditure accounted for smaller portions of the total expenditure yearly as compared to course expenses for new hires who stayed on, total expenditure incurred for new hires who left the company amounted to a total sum of $216,169 over the 5 years (2013 to 2017) .

[Yearly Wastage w placement]

Total cost wastage in training new hires who left JLT saw an increase from 2013 ($14,529) to 2016 ($111,954) , expanding cost by 670.5% over 4 years. Though 2017 incurred less cost from 2016 ($32,370), it was still a 122% increase from 2013. On the other hand, total training placements peaked in 2013 (171) and 2016 (170) with the lowest wastage recorded in 2014 (69).There was no obvious trend identified in yearly training placements for new hires. Nonetheless, total training placements dropped by 56% in 2017 as compared to 2013.

[Avg Training placement and cost]

Overall, average cost wastage incurred to train new hires who eventually left showed an increasing linear trend over the 5 years, tripling from $538 in 2013 to $2312 in 2017. On average, new hires who left had 5 to 7 training placements each within the year that they were in the company. Common course type identified included HSEQ training (e.g. safety orientation) and Miscellaneous training (e.g. HR orientation).

EMOS

[EMOS Course Expenditure]

Total course expenditure to train all new hires in EMOS showed an increasing trend, with the exception of a significant decrease cited in 2015 ($77,595). Overall, a total amount of $500,730 was incurred to new hires who eventually left EMOS from 2013 - 2017. As of 2017 , course expenditure increased by 55% since 2013 although it was 30% decline from 2016 which recorded the highest expenditure of $223,928 among the 5 years.

Course expenditure for new hires who left EMOS accounted for 35% to 51% across the 5 years. Course expenditure for new hires who left EMOS showed similar trends with the highest course expenditure wastage incurred in 2016 ($164,270). Based on the last observation in 2017, course expenditure for this group increased by 51% since 2013.

[EMOS Yearly Placement and cost]

Total training placements wasted on new hires who left from EMOS peaked in 2014 at 342 placement before dropping significantly the next year to 84 placements, the lowest wastage in placements experienced among 5 years. Overall, total training placements for new hires who left EMOS dropped by 18% in 2017 as compared to 2013.

[Average emos]

A comparison of average cost in training per employee for new hires who left showed an increasing trend. Average cost incurred in training per employee rose by 65% in 2017 ($3,245) since 2013 ($1,968). However ,this was in contrast to an overall decrease in average training placements observed from 2013 (6 placements) to 2017 (5 placements).

This observation could likely be resulted from the longer training hours employees underwent per training placement. Similar to employees in JLT, new hires mostly undergone HSEQ training (e.g. Safety orientation) and Misc (e.g. HR Orientation).

JLT

To be updated