Great Deal! Get Instant $25 FREE in Account on First Order + 10% Cashback on Every Order Order Now

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

1 answer below »

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
Answered 10 days AfterFeb 10, 2022

Solution

Mohd answered on Feb 20 2022
45 Votes
Demographics:
Of the total headcount, 51.24 percent employees are female and 48.76 percent employees are male.
Of the total headcount, 57.49 percent are with no visible minority status and 42.51 percent are with visible minority status.
Of the total employees, 86.02 Percent are not having aboriginal status and 13.98 are having aboriginal status.
Military division has highest headcount in both categories full time or part time.
Main office has lowest headcount among divisions in both categories of part time/ full time.
Age range 20-30 has highest number of employees among all age groups. Age Above 60 range has 781 employees.
    Bin_range
    Bin_range
    Frequency
    Bin_range
    Frequency
    20
    20
    227
    30
    1817
    30
    30
    1817
    40
    1572
    40
    40
    1572
    50
    1539
    50
    50
    1539
    60
    1504
    60
    60
    1504
    More
    781
     
    More than 60
    781
    20
    227
Occupation wise salary:
    Occupation
    Average of Salary
    Accounting
    76683.08
    Administrators
    41598.57
    Business Analysts
    56640.04
    CEO
    748182.93
    CFO
    323713.10
    CHRO
    193277.90
    CIO
    300723.10
    Cleaning Services
    50781.09
    COO
    398515.65
    Co-op Students
    17693.70
    Designers
    79278.09
    HR
    76237.53
    IT
    85096.05
    Legal
    114474.76
    Logistics
    68639.05
    Managers
    215186.16
    Marketing
    65137.27
    Project Leaders
    91998.41
    Purchasing
    86597.30
    Software Enginee
    166006.22
    Software Technician
    93145.41
    Software Teste
    77709.86
    VP Education Software
    358026.40
    VP Government Software
    350985.93
    VP Medical Software
    496802.35
    VP Military Software
    319134.00
    Grand Total
    97687.38
Average service years by occupation:
CHRO has lowest average service period and CIO has highest service period of 25.
    Occupation
    Average of Service Years
    Accounting
    12
    Administrators
    12
    Business Analysts
    12
    CEO
    22
    CFO
    2
    CHRO
    1
    CIO
    25
    Cleaning Services
    13
    COO
    17
    Co-op Students
    1
    Designers
    12
    HR
    13
    IT
    12
    Legal
    11
    Logistics
    12
    Managers
    12
    Marketing
    13
    Project Leaders
    12
    Purchasing
    12
    Software Enginee
    12
    Software Technician
    12
    Software Teste
    12
    VP Education Software
    26
    VP Government Software
    20
    VP Medical Software
    30
    VP Military Software
    13
Percent voluntary termination headcount by Occupation and termination reason:
    Count of Occupation
    Column Labels
    
    
    
    
    Row Labels
    Family Issues
    Health Reasons
    Resignation
    Return to School
    Grand Total
    Accounting
    17.18%
    12.24%
    15.56%
    14.96%
    15.22%
    Administrators
    2.59%
    2.69%
    4.58%
    2.56%
    3.41%
    Business Analysts
    4.71%
    2.99%
    4.12%
    3.74%
    3.97%
    Cleaning Services
    3.29%
    2.99%
    3.32%
    4.33%
    3.50%
    Designers
    4.00%
    5.07%
    4.81%
    3.54%
    4.39%
    HR
    3.53%
    3.88%
    3.78%
    3.74%
    3.73%
    IT
    3.06%
    2.99%
    2.63%
    2.56%
    2.75%
    Legal
    3.06%
    2.99%
    3.20%
    3.54%
    3.22%
    Logistics
    4.47%
    6.27%
    5.38%
    5.31%
    5.32%
    Managers
    14.35%
    19.10%
    16.59%
    17.32%
    16.71%
    Marketing
    2.12%
    2.69%
    3.78%
    2.76%
    3.03%
    Project Leaders
    4.00%
    1.49%
    3.89%
    3.54%
    3.45%
    Purchasing
    3.76%
    4.18%
    3.89%
    3.94%
    3.92%
    Software Enginee
    17.65%
    14.93%
    13.84%
    16.34%
    15.36%
    Software Technician
    6.12%
    7.46%
    5.15%
    6.30%
    5.98%
    Software...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here