This is excel. All the instructions are in what i have attached. After a price quote i will send the data he wants us to use.
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 (3) 24 periods. 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 (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 · 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 (gender/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) · 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 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 · Current 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 Current 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 current staffing the combined total represents. 3. 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? 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. 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