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 Major Code.

The applicant has a GPA of 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 I4 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.)

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

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 I14 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 I15 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 I16 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.

 

Name Anderson Ryan Greg Jackie Sandra Lindsey Carolyn Steven Max College Major Refer- GPA Code ences 3.7 4.0 2.0 3.2 3.0 3.9 3.8 2.6 3.8 All values True Any values True Number of True values 2 18 15 30 20 4 16 500 19 1535 NNW N 2 3 2 2 1 2 4 Personal Interview Rating 5 5 325 4 54631 Employ- Work ment Experience Exam Score TRUE TRUE TRUE TRUE TRUE TRUE FALSE FALSE TRUE 650 800 780 710 600 740 720 299 760 School Rank 12 5 3 26 15 8 55 6 Invalid Scores/ Major?