Assignment:
You are the Chief Technology Officer (CTO) for a medium sized organization with approximately 1500 employees. Due to problems within the industry, the company's sales force needs to be reduced by at least 25%. As the head of the Technology department, you must review the performance of all the technical sales people and determine who will be kept and who will be laid off. At present, all of the sales force employees are earning a salary between $50,000 and $100,000.
The employees each had to complete a product knowledge exam and have been rated by their managers. Both the exam scores and the manager evaluations were based out of 100 points. The scores and evaluations are provided in the spreadsheet. One employee earned a perfect score (100) on the product knowledge exam. Your decisions on who to layoff will be based primarily on their overall score. Click here to download the spreadsheet for this assignment.
1. Review the provided spreadsheet to ensure the accuracy of the current data and correct any errors that you identify. These could be typos or errors in functions or formulas.
2. Complete the remaining analysis and format the spreadsheet to include the following:
1. Someone made an error when inputting the employees ID numbers and formatted them with decimals. These should be formatted like a Social Security Number.
2. Calculate the Overall Score for each employee out of 100 points. The Product Knowledge Exam is worth 30% of their score and the manager's evaluation is worth 70%.
3. In the "Layoff?" column, use a VLOOKUP function to identify those employees who will be laid off based on their Overall Score that was calculated and using the provided Table Array found on Sheet 2. Use an absolute reference where appropriate.
4. Format the fonts - colors, sizes, and types to be both professional and visually appealing
5. Format the rows and columns to appropriate heights or widths for the data
6. Choose a company name and replace "Company Name" in cell A1 with the new name.
7. Add a company logo that will correspond with the organization's name. Assure that the logo you choose or design is not copyright protected.
8. Sort the data based on the "Layoff?" column so that the employees who are recommended to be laid off are shown first.
9. Create a line chart that shows the employee names, product knowledge exam scores, and manager ratings. The chart must be on a separate sheet within the file.
10. Make a recommendation to the CEO under the "RECOMMENDATION" cell. You must recommend at least 25% of the employees for layoff. Use the criteria provided for the "Layoff?" column to assist with your recommendation. For any employees recommended for layoff who do not fall below the minimum 70 overall score (indicated in the table on Sheet2), you must justify your reasoning for not keeping the employee.
11. Use a function or formula that uses cell references to calculate the total annual cost savings for the company after your recommended layoffs.
12. Calculate the total salary for remaining employees based on total salary and the savings due to laid off employees that was calculated previously.