HR Metrics: aSSIGNment 2 (15%)
PROJECT SUMMARY
This is an individual project.
You are to create these reports based on the dataset within the Assignment 2 folder:
1. a corporate profile report
2. a data table outlining the projected retirement calculations for all occupations for the next 5 years
3. a voluntary turnover report for all occupations
4. a 5-year voluntary turnover forecast report for all occupations
NOTE: The data for this assignment is neither based on Generesta nor the data for Assignment 1.
DUE DATE AND SUBMISSION
Please refer to Slate for the due date.
Upload the file to the Assignment Folder labelled, “Assignment 2”.
If submitted late a penalty of 10% off per 24 hours will be applied for up to three XXXXXXXXXXperiods. After that you will receive a grade of 0 for the assignment.
INSTRUCTIONS AND RESOURCES
Download the file titled, “HR Metrics_ Assignment 2 Data”. This file contains employee information about a fictional company.
Your file submission will have four sheets as indicated in the Project Summary section (above).
NOTE: when reporting percentages for all sheets, only show the data to 1 decimal place.
Part A: Corporate Profile Report
The company is mindful of employment equity and wishes to ensure that their workforce is representative of the regional community it operates in. They have done research and found that the percent of the population in each of the protected classes are as follows:
· Women: 50.6%
· Aboriginal: 8.4%
· Disability: 9.4%
· Visible Minority: 37.3%
You are to create a report that contains the following data:
· Total Headcount
· Active Employees
· Leaves of Absence (
oken out by LTD, Maternity, Sick and include a total)
· Labour Structure (
oken out between Full Time, Part Time, Full-LTD, Part-LTD, Full Time FTE, Part Time FTE, Total FTE). Round FTE to 1 decimal place.
· For the FTE calculation, you must look at the data to determine how to complete the calculation as the hours for each Part Time employee varies (unlike in class/walkthrough guides where you were given the FTE for part time employees.) HINT: create another column in the Payroll tab to do the calculations before doing a pivot table.
· Age Demographics with the age categories as follows: 20 and Under, 21-30, 31-40, 41-50, 51-60, 61 and Ove
· Gender Diversity (split between male and female with % female)
· Aboriginal Diversity (employee counts with % that are Aboriginal)
· Visible Minority Diversity (employee counts with % that are Visible Minority)
· Disability Diversity (employee counts with % that identify as having a disability)
For this report, create a “Executive Leadership” grouping for the executive team (include only the C-Suite occupations: CEO, CFO, CHRO, CIO and COO). Create a second grouping “VP & Managers” and include the VP and Manager occupations. Ensure that these two groupings have been collapsed so only the group headings are visible.
Then create another group for all other employees and label it “All Other Employees”. For this group, make sure it is expanded to show the occupations that are included in this section.
Deliverables for Part A
1. Once the report is completed, ensure that it is professionally formatted to draw the reader’s attention to the groupings as well totals and sub-totals.
2. In Excel, underneath the report identify the most important diversity issue the company is facing (gende
Aboriginal/disability/visible minority) and explain why.
3. Based on the protected class you identified in #2 as being under-represented, state the three occupations that are most problematic (most under-represented), the number of employees in each occupation and what percent identify as the protected class in each occupation.
Occupation
Number of Employees of Identified Group in Each Occupation
% of Employees of Identified Group in Each Occupation
1.
2.
3.
Part B: Retirement 5-Year Forecast
Use the raw data from the file to determine the projected number of retirements for the next 5 years using the “Retirement Calculator Generesta”.
Include the following data from the ‘Results’ tab and paste into your answer file in a new sheet:
· Occupations (under Group)
· Cu
ent Staffing
· Projected Retirements, Next 5 Years
· % of Cu
ent Staffing
Note: Do not group the occupations for this report like you did for the corporate diversity report and profile from Part A.
Deliverables for Part B
1. Once the data from the Results tab from the Retirement Calculator Generesta” has been posted in a new sheet, format the report in a professional manner using borders and colour for the headings. Remove any unnecessary rows to clean up the report.
2. Identify the occupation which will have the highest number (not highest %) of retirements in the next 5 years by highlighting the occupation along with the results in a colour of your choice.
Part C: Historical Voluntary Turnover Report
The data file for this assignment contains two additional tabs, “Terminated Employees” and “Headcount History”. With the information from these two tabs create a Voluntary Turnover Report which shows the turnover rates by occupation by year. Your report should include conditional formatting to highlight the occupations with greater than the overall corporate voluntary turnover each year.
Deliverables for Part C
1. Once the report is completed, ensure that it is professionally formatted to draw the reader’s attention to the yearly totals and occupations that are above overall company voluntary turnover using conditional formatting.
2. Identify the occupations which have been above the yearly voluntary turnover for the company across ALL four years by bolding and italicizing the entire occupation row including all data and shading the cell in a colour of your choice.
Part D: 5-Year Voluntary Turnover Forecast
Using the data from the “Terminated Employees” tab, create another report forecasting the projected turnover that will occur over the next 5 years. For the projections, model what was done in class and use straight-line forecasting by taking the average of the previous years as the basis for future results.
The report should include the following columns:
· Occupations
· Cu
ent Staffing
· Voluntary Turnover for 2018, 2019, 2020 and 2021 (show the numbers for each year)
· 4-Year Average
· 5-Year Forecast
· 5-Year Forecast as % of Cu
ent Staffing
Deliverables for Part D
1. Once the report is completed, ensure that it is professionally formatted to draw the reader’s attention to the totals.
2. Consider the data you retrieved from Part B (retirement calculations). Just underneath the report, state the projected retirements over the next five years and the projected voluntary turnovers over the next five years. Include the total numbers and indicate what percent of cu
ent staffing the combined total represents.
3. Write a
ief comment on your thoughts regarding the total forecast for both retirements and voluntary turnover over the next 5 years. Is this an issue?
Resources
1. To help you create the corporate profile and diversity report, please refer to the file, “Profiling the Workforce and Diversity Reports-Walkthrough Help Guide_v3”, which can be found in Slate under the folder “Walkthrough Guides” as well as the completed diversity report found in the Class 4 (Demographics and Retirement) folder.
2. To help you with the retirement calculations, please refer to the file, “Retirement Calculator Walk Through Guide v2” which can be found in Slate under the folder “Walkthrough Guides”.
3. To help you with the voluntary turnover report, please refer the file, “Voluntary Turnover Rate Help Guide” which can be found in Slate under the folder “Walkthrough Guides”.
4. For the 5-year voluntary forecast problem refer to the class notes.
Ru
ic – 140 Marks
Report
Report Elements
Excellent
Satisfactory
Needs Improvement
A) Corporate Profile and Diversity Report
(50 marks)
Inclusion of All Data
(18 marks)
All data elements have been included in the report.
(16-18 marks)
Not all but more than half of the data elements have been included.
(9-15 marks)
Half or less of the data elements have been included.
(0-8 marks)
Occupational Grouping
(6 marks)
All three occupational groupings are present. The “Executive Leadership” and “VP and Managers” groupings have been collapsed and the “All Other Occupations” grouping is expanded to show each occupation. All occupations are also co
ectly included in each group as per the instructions.
(6 marks)
All three occupational groupings are present but either the “Executive Leadership” or “VP and Managers” groups are expanded showing the occupations or the “All Other Occupations” is collapsed. All occupations are also co
ectly included in each group as per the instructions.
(3-5 marks)
No occupational groupings have been used or occupations have not been placed into the proper grouping.
(0-2 marks)
Report Formatting
(6 marks)
The report is professionally presented making use of borders, cell shading and font formatting to draw the reader to important points.
(6 marks)
The report is professionally presented making use of some of the following elements: borders, cell shading and font formatting to draw the reader to important points. Using the completed corporate profile report posted in Slate would have been helpful.
(3-5 marks)
The report is not professionally presented and does not make use of the following elements: borders, cell shading and font formatting to draw the reader to important points. Using the completed corporate profile report posted in Slate would have been helpful.
(0 marks)
Data Accuracy
(10 marks)
All data is accurate without any mistakes including FTE calculations. Percentages are rounded to 1 decimal place.
(9-10 marks)
More than half of the numbers are co
ect or percentages are not rounded to 1 decimal place.
(5-8 marks)
Half or less of the numbers are co
ect.
(0-4 marks)
Co
ect Identification of Diversity Issue
(10 marks)
The co
ect diversity problem has been identified.
The three occupations most unde
epresented have been co
ectly reported.
(9-10 marks)
The co
ect diversity problem has been identified but
The three occupations most unde
epresented have not been co
ectly reported.
(5-8 marks)
The diversity issue has not been co
ectly identified which means the occupations would also be inco
ect.
(0 marks)
B) Retirement Report
(30 marks)
Inclusion of All Data
(10 marks)
All data elements have been included in the report.
(9-10 marks)
Not all but more than half of the data elements have been included.
(5-8 marks)
Half or less of the data elements have been included.
(0-4 marks)
Report Formatting
(5 marks)
The report is professionally presented making use of borders, cell shading and font formatting to draw the reader to important points.
(5 marks)
The report is professionally presented making use of some of the following elements: borders, cell shading and font formatting to draw the reader to important points.
(3-4 marks)
The report is not professionally presented and does not make use of the following elements: borders, cell shading and font formatting to draw the reader to important points.
(0 marks)
Data Accuracy
(10 marks)
All data is accurate without any mistakes. Percentages are rounded to 1 decimal place.
(9-10 marks)
More than