You work in the Human Resources Department (or simply HR)

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:

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 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:

An applicant is automatically hired if all of the following criteria are TRUE:

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 <= 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 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.