Management Question
Instructions:
- Prepare the files for the assignment. These files include:
- One excel file called “Job Data Exercise” that you’ve created for Assignment #1, which includes several worksheets of data reflecting compensation data from your company. You can use your file from the previous assignment as a starting point, but additional vlookups are necessary for this Assignment. If you weren’t sure whether the dataset you compiled was correct, I’ve also attached a file called “CORRECT INDV ASSIGN 1 Values” below for you to use.
- One excel file called “Benchmarking Survey,” which include 2 worksheets (one with job descriptions, the other with the benchmark survey) representing a third-party survey to which you will be responding with your company data (which will be pulled from the “Job Data Exercise” file).
- One file folder consisting of approximately 35 job descriptions in pdf format; these represent job description in your company.
Using these three file sets, you will need to complete “Benchmarking Survey”
file using the combined data from difference sources.
Steps include:
- Building off of Assignment 2, combine the worksheets from the “Job Data Exercise” file, such that all data points per individual are included on a single worksheet. You can start with the spreadsheet from Assignment 2, but Bonus information needs to be added.
- Job Matching: Using the job descriptions provided in the job descriptions file folder and the worksheet labeled “Job Descriptions” in the “Benchmarking Survey” file, identify “job matches” based on basic duties and qualifications (experience and education). Input the “matched title for your company” in column C (Company Title Match) in the worksheet labeled “Salary Benchmark Survey.”
- Create a pivot table in the “Job Data Exercise” file to report the remaining information requested in the survey (# of incumbents, avg salary, bonus count and average bonus amount).
- Using the data summarized in the pivot table, complete the “Benchmark survey” worksheets.
