HR Metrics: aSSIGNment 2 (15%)

PROJECT SUMMARY

This is an individual project.

You are to create these reports based on your unique dataset within the assignment folder.

a corporate profile report
a data table outlining the projected retirement calculations for all occupations for the next 5 years
a voluntary turnover report for all occupations
a 5-year voluntary turnover forecast report for all occupations

Each student will have a unique dataset to complete this assignment. To find your dataset, go to Content>>Assignments>>Assignment 2 Dataset folder. From there you will download the Excel file with your Student ID in the file name.

IF YOU DOWNLOAD AND USE THE WRONG DATAFILE, YOU WILL RECEIVE A GRADE OF ZERO (0) – AND THERE WILL NOT BE AN OPPORTUNITY TO REDO THE SUBMISSION FOR MARKS (you can upload the corrected version for feedback only).

It is your responsibility to double check you have downloaded the correct file.

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 (3) 24 periods. After that you will receive a grade of 0 for the assignment.

NOTE: THIS LATE PENALTY POLICY ONLY APPLIES IF GRADES AND FEEDBACK HAVE NOT YET BEEN GIVEN TO THE CLASS. If the instructor has published grades and provided feedback, assignments can no longer be submitted for marks.

INSTRUCTIONS AND RESOURCES

Download and review your unique dataset. This file contains employee information about a fictional company.

Your file submission will have four additional sheets as indicated in the Project Summary section (above).

To begin, collect the data from the different division sheets into the Payroll Data sheet. THIS CAN ONLY BE DONE THIS USING THE =VSTACK() FUNCTION. If =VSTACK() doesn’t work, then the only other acceptable method is using Power Query and the Append Data function.

IF YOU DO NOT USE A METHOD MENTIONED IN THE PREVIOUS PARAGRAPH TO COLLECT DATA IN ONE SHEET (e.g. if you copy and paste the divisions into the Payroll Data sheet) YOU WILL RECEIVE A GRADE OF ZERO (0) FOR THE PROJECT.

NOTE: when reporting percentages, only show the data to 1 decimal place (marks will be deducted if this formatting is not followed).

Resources

To help you consolidate the data into one sheet, please refer to “Preparing Data for Pivot Tables using VSTACK v1”.
To help you create the corporate profile and diversity report, please refer to the file, “Profiling the Workforce and Diversity Reports-Walkthrough Help Guide_v5”, 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.

To help you with the retirement calculations, please refer to the file, “Retirement Calculator Walk Through Guide v3” which can be found in Slate under the folder “Walkthrough Guides”.
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”.
For the 5-year voluntary forecast problem refer to the class notes.

Part A: Corporate Profile Report

You are to create a report that contains the following data:

Total Headcount
Active Employees
Leaves of Absence (broken out by LTD, Maternity, Sick and include a total)
Labour Structure (broken 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 Over
Female Diversity (split between male and female with % female)
Indigenous Peoples Diversity (employee counts with % that are Indigenous Peoples)
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.

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 the percent of the population in each of the protected classes. Refer to the sheet in the file labelled, “Part A-More Details” for this information which you will need to use in your report.

Deliverables for Part A

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.
In the sheet called, “Part A-More Details” identify the most important diversity issue the company is facing (female diversity/Indigenous Peoples/disability/visible minority) and explain why.
Based on the protected class you identified in #2 as being under-represented, in the sheet called, “Part A-More Details”, complete the table reporting on 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.

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)
Current Staffing
Projected Retirements, Next 5 Years
% of Current 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

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.
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

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.
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
Current Staffing
Voluntary Turnover for 2020, 2021, 2022 and 2023 (show the numbers for each year)
4-Year Average
5-Year Forecast
5-Year Forecast as % of Current Staffing

Deliverables for Part D

Once the report is completed, ensure that it is professionally formatted to draw the reader’s attention to the totals.
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 current staffing the combined total represents.
Write a brief comment on your thoughts regarding the total forecast for both retirements and voluntary turnover over the next 5 years. Is this an issue?

Rubric – 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 correctly 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 correctly 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 correct or percentages are not rounded to 1 decimal place. (5-8 marks)	Half or less of the numbers are correct. (0-4 marks)
	Correct Identification of Diversity Issue (10 marks)	The correct diversity problem has been identified. The three occupations most underrepresented have been correctly reported. (9-10 marks)	The correct diversity problem has been identified *but* The three occupations most underrepresented have not been correctly reported. (5-8 marks)	The diversity issue has not been correctly identified which means the occupations would also be incorrect. (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 half of the numbers are correct or percentages are not rounded to 1 decimal place. (5-8 marks)	Half or less of the numbers are correct. (0-4 marks)
	Correct Identification of Occupation with Highest # of Forecasted Retirements (5 marks)	The correct occupation has been identified. (5 marks)		The incorrect occupation has been identified. (0 marks)
C) Historical Voluntary Turnover 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 half of the numbers are correct or percentages are not rounded to 1 decimal place. (5-8 marks)	Half or less of the numbers are correct. (0-4 marks)
	Correct Identification of Occupations with Higher Than Average Turnover (5 marks)	The correct occupation(s) has/have been identified. (5 marks)		The incorrect occupation(s) has/have been identified. (0 marks)
D) 5-Year Voluntary Turnover Forecast (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 half of the numbers are correct or percentages are not rounded to 1 decimal place. (5-8 marks)	Half or less of the numbers are correct. (0-4 marks)
	Combined Forecast Results (5 marks)	Provided combined results for retirement and voluntary over 5 years AND provided insights or potential issues. (5 marks)	Provided combined results for retirement and voluntary over 5 years but did *not* provided insight or potential issues. (3-4 marks)	Did not provide combined results or any insights. (0 marks)