Answer Happy

Level 1 - Evaluating Job Applicants for Winston, Winston & Coombs You work in the Human Resources Department (or simply HR) for the accounting firm Winston, Winston & Coombs. The firm has recently increased its client base and hired several university graduates for entry-level positions. The HR manager has established a formal process for evaluating job applicants. This process takes into account the applicant's academic performance and work experience, as well as the impression made during the personal interview. In addition, all applicants are given a skills-based exam to determine their proficiency in spreadsheet and database applications. Because some applicants are not business majors but might be otherwise qualified for a position, the exam also covers some basic business concepts in accounting, finance, and marketing. You have been asked to evaluate the information on the candidates being considered. Each job application provides the following information: College GPA (valid scores range from 1.5 to 4.0). Major Code, universal standardized system that indicates the applicant's undergraduate major; for example, Engineering=1, Business=2, Economics=3, Physical Science=4, and so on (valid codes for majors are 1 through 100). The total number of references submitted by the applicant. A Personal Interview Rating. If the applicant has previous work experience (TRUE or FALSE). • The Employment Exam score (valid scores are between 300 and 800). The undergraduate school ranking (compared with all colleges across the country) The HR manager has established criteria to determine if an applicant will be automatically disqualified or automatically hired, or if no decision is made. The criteria, which are applied in order, are described in the following list.An applicant is automatically disqualified if any of the following criteria are TRUE: The applicant has submitted an invalid GPA score, Employment Exam score, or MajorCode. The applicant has a GPA less than 3.0.• The applicant provided fewer than two references. The applicant has an Employment Exam score below 600. The applicant has a Personal Interview Rating of less than 3.An applicant is automatically hired if all of the following criteria are TRUE:• The applicant has not been automatically disqualified. The applicant has a GPA score over 3.8.• The applicant has a Major Code between 1 and 25 (inclusive).. The applicant graduated from one of the top 50 schools (ranking of 50 or less).. The applicant has an Employment Exam score above 720. • The applicant has a personal interview rating of 4 or higher. • The applicant has prior work experience. If an applicant is neither automatically disqualified nor automatically hired, the applicant's status is undecided. Complete the following:
1. Open the workbook named Hiring.xlsx located in the Chapter 4 folder, and then save the file as WWC Hiring Analysis.xlsx. 2. Write a formula in cell 14 that can be copied down the column to determine if (TRUE or FALSE) any of the following scores or codes listed for this applicant are invalid: GPA, Major Code, Employment Exam. (Hint: Use the information provided in the problem description to determine the appropriate criteria.) 3. Write a formula in cell J4 that can be copied down the column to determine if (TRUE or FALSE) the applicant should be automatically disqualified based on the given criteria. 4. Write a formula in cell K4 that can be copied down the column to determine if this candidate is not automatically disqualified. (Hint: Use the results determined in Step 3.)Chapter 4 Applying Logic in Decision Making 5. In cell L4, write a formula that can be copied down the column to determine if (TRUE or FALSE) the candidate should be automatically hired based on the given criteria. (Hint: For criteria between two values, test that the value is both >= the lower limit and <= the higher limit.) 6. Write a formula in cell M4 that can be copied down the column to determine if this candidate is not automatically hired. (Hint: Use the results determined in Step 5.) 7. Write a formula in cell N4 that can be copied down the column to determine if no decision is made on this applicant. Recall that no decision is made if the applicant is both not automatically disqualified (K) and not automatically hired (M). 8. Write a formula in cell 114 that can be copied across the row (through column N) to determine if all of the applicants have invalid scores. 9. Write a formula in cell 115 that can be copied across the row (through column N) to determine if any of the applicants have invalid scores. 10. To summarize the results, write a formula in cell 116 that displays the total number of applicants who have invalid scores. Copy this formula across the row (through column N). This formula should automatically update if any of the scores or criteria are later modified. 11. Apply conditional formatting to highlight the important points, as follows: a. Highlight all of the TRUE values in the Automatically Disqualified column (J4:J12) using a dark green and bold text format. |
b. Use gradient fill blue data bars to highlight the Personal Interview Rating scores of the applicants. c. Highlight the name of any applicant with an Employment Exam Score of more than 720 using a light blue background. 12. Save and close the WWC Hiring Analysis.xlsx workbook.
1 2 A Name 3 4 Anderson 5 Ryan 6 Greg 7 Jackie 8 Sandra 9 Lindsey 10 Carolyn 11 Steven 12 Max 13 B College GPA 3.7 4.0 2.0 3.2 3.0 3.9 3.8 2.6 3.8 14 All values True 15 Any values True 16 Number of True values C Major Code 2 18 15 30 20 4 16 500 19 D Refer- ences 2 3 2 2 1 3 5 2 4 E Personal Interview Rating 5 5 3 4 5 4 6 3 1 F Work Experience TRUE TRUE TRUE TRUE TRUE TRUE FALSE FALSE TRUE H | J Application for Employment G Employ- ment Exam Score 650 800 780 710 600 740 720 299 760 School Rank 12 1 5 3 26 15 8 55 6 Invalid Scores/ Major? Automatically Disqualified K Not Automatically Disqualified L Automatically Hired M Not Automatically Hired N No Decision