This project may be completed individually or in groups of up to 3 people per group.
***Regardless if you choose to do the project by yourself or in a group, you must sign up to a group. If you don’t, the Assignment dropbox will NOT appear, you will not be able to download the dataset and you will NOT be able to upload your file.
You are to create these reports based on the dataset within the Assignment 4 folder:
1. a layoff savings report
2. a report identifying specific employees found within a group and providing financial details of a program to reduce voluntary turnover
3. a report calculating recruitment metrics
4. a report indicating the most useful selection tests for future employees based on actual performance
NOTE: The data for this assignment is neither based on Generesta nor the data for Assignment 1, 2 or 3.
Download the file titled, “HR Metrics_ Assignment 4 Data”. This file contains employee information about a fictional company.
Submit only one file by using multiple tabs for your answers. Do not submit separate files for different questions. Marks will be taken off if this instruction is not followed.
NOTE: when reporting percentages for all sheets, only show the data to 1 decimal place.
Using the data from the “Terminated Employees” tab and the “Payroll” tab, create a report showing how much money the company has saved over four years due to layoffs. Since salaries haven’t changed over the past four years, use the salary data from the “Payroll” tab to calculate savings.
The report should include the following columns:
· Occupation
· Average Salary (this is the average salary by each occupation)
· Layoffs by year for 2016, 2017, 2018 and 2019
· Total Layoffs
· Estimated 4-Year Labour Cost Savings (for each occupation and provide a grand total)
1. Once the report is completed, ensure that it is professionally formatted to draw the reader’s attention to the totals and sub-totals.
Part B: Joining Multiple Tables Together
The company has specific concerns about voluntary turnover for a specialized group of employees. These roles are critical to the company and the knowledge these employees have must be retained. These employees work on secret government projects and are grouped either into Security Clearance Level 1 or Security Clearance Level 2.
Senior management has told you to stop all voluntary turnover going forward. They have told you that all of these specialized employees are to receive one-time extra “retention” bonuses outlined as follows:
· $25,000 of company stock issued as common shares. This will be paid to BOTH Level 1 and Level 2 security clearance employees.
· A cash bonus paid immediately based on the employee’s performance rating from the previous year and their tenure (years of service). This will be paid ONLY to Level 2 security clearance employees.
Senior management believes this should, in part with other initiatives, eliminate most of the voluntary turnover. They have asked you to create a report outlining the total cost for this intervention.
This problem will require the use of advanced Excel functions. Consider using formulas such as IF(), VLOOKUP(), and a combination of INDEX() and MATCH().
1. In the Payroll tab, create a new column and use VLOOKUP() to identify if an employee has a security clearance of either Level 1 or Level 2 from the Security Clearance Lookup Data tab. If the employee doesn’t have a security clearance designation the cell should show “None”.
2. Now, create another column labelled “Security Bonus %” and use a formula to bring in the percent info for each employee. Remember, this is only for Level 2 employees based on performance rating and tenure.
3. Now create another column with the label, “Issued Stock”.
4. Besides the “Issued Stock” column, create a new column at the end of the existing data with the label, “Cash Bonus”.
5. And finally create the last column labelled, “Total Payout” which is the sum of “Issued Stock” and “Cash Bonus”.
6. Use the formulas indicated above (or others you know of) to fill in the data for the new columns. NOTE: You must use formulas for these columns that are applied to all employees. For example, the formula used for the first employee must be used for all employees in each column. If you use filters to sort the data and input numbers or use inconsistent formulas, you will receive a grade of zero (0) for this entire question.
7. Once done, use a pivot table to extract data to create a professional looking report. Make sure the pivot table includes Sub Totals and Grand Totals for the report.
The report must contain the following data:
· Security Level
· Occupations
· Headcount by Security Level and Occupation
· Total Issued Stock (in Currency format and rounded to the nearest dollar)
· Total Cash Bonus $ (in Currency format and rounded to the nearest dollar)
· Total Retention $ (in Currency format and rounded to the nearest dollar) à (this is the total of Issued Stock and Cash Bonus)
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
