QUESTION

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.

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

Exercise 4-1: Level 1 - Evaluating Job Applications for the BBB Company You work in the Human Resources Department (or simply HR) for the accounting firm BBB. 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 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. AutoSave OFF 4-Hiring-BBB Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Insert ab Calibri (Body) ' 11 General 5 Conditional Formatting Format as Table Cell Styles DX Delete Paste BIU v a. Av $ % ) EE 40 00 Editing Format Analyze Data Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? Yes No Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. Check for Updates 14 B D E F G H 1 K L M N o Application for Employment r 1 2 Name College GPA Major Code Refer- ences Personal Interview Rating Work Experience Employ. ment Exam Score School Rank Invalid Scores/ Major? Automatically Disqualified Not Automatically Disqualified Automatically Hired Not Automatically Hired No Decision 3 4 Anderson 3.7 2 2 5 TRUE 650 12 5 Carolyn 3.8 16 5 6 FALSE 720 8 6 Greg 2.0 15 2 3 TRUE 780 5 7 Jackie 3.2 30 2 4 TRUE 710 3 8 Lindsey 3.9 4 3 4 TRUE 740 15 9 Max 3.8 19 4 1 TRUE 760 6 10 Ryan 4.0 18 3 5 TRUE 800 1 1 11 Sandra 3.0 20 1 5 TRUE 600 26 12 Steven 2.6 500 2 3 FALSE 299 55 13 All values True 25 Any values True Number of True values 16 17 18 19 20 21