Level 1 - Evaluating Job Applicants for Winston, Winston & Coombs You work in the Human Resources Department (or simply

[answerhappygod]

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 hasrecently increased its client base and hired several universitygraduates for entry-level positions. The HR manager has establisheda formal process for evaluating job applicants. This process takesinto account the applicant's academic performance and workexperience, as well as the impression made during the personalinterview. In addition, all applicants are given a skills-based to determine their proficiency in spreadsheet and databaseapplications. Because some applicants are not business majors butmight be otherwise qualified for a position, the also covers somebasic business concepts in accounting, finance, and mAketing. Youhave been asked to evaluate the information on the candidates beingconsidered. Each job application provides the followinginformation: .
*College GPA (valid scores range from 15 o 40) .
*Major Code, universal standardized system that indicates theapplicant's undergraduate major; for example, Engineering-1,Business 2. Fconomics 3, Physical Science 4, and so on (valid codesfor majors are 1I through 100) .
*The total number of references submitted by the applicant
. *A Personal Interview Rating .
*If the applicant has previous work experienee (TRUE orFALSE)
The Employment score (valid scores are between 300 and800) . The undergraduate school ranking (compared with all collegesacross the country) The HR manager has established criteria todetermine if an applicant will be automatically disqualified orautomatically hired, or if no decision is made. The criteria, whichare applied in order, are described in the following list. Anapplicant is automaticaly disqualifed if any of the followingcriteria are TRUE: : The applicant has submitted an invalid GPAscore, Employment score, or Major Code, . The applicant has aGPA less than 3.0. . The applicant provided fewer than two referene. The applicant has an Employment score below 600 . The applicanthas a Personal Interview Rating of less than 3 RUE: An applicant isautomatically hired if all of the following criteria are T - Theapplicant has not been automatically disqualified. . The applicanthas a GPA score over 3.8 . The applicant has a Major Code between 1and 25 (inclusive) . The applicant graduated from one of the top 50schools (ranking of 50 or less). The applicant has an Employment score above 720. . The applicant has a personal interviewrating of 4 or higher . The applicant has prior workexperience.
Complete the following: 1. Open the workbook named Hiring.xlsxlocated in the Chapter 4 folder, and then save the file as WWCHiring Analysis.xlsx. 2. Write a formula in cell 14 that can becopied down the column to determine if (TRUE or FALSE) any of thefollowing scores or codes listed for this applicant are invalid:GPA, Major Code, Employment . (Hint: Use the information providedin the problem description to determine the appropriate criteria.)3. Write a formula in cell J4 that can be copied dove the column todetermine if (TRUE or FALSE) the applicant should be automaticallydisqualified based on the given criteria. 4. Write a formula incell K4 that can be copied down the column to determine if thiscandidate is not automatically disqualified. (Hint: Use the resultsdetermined in Step 3.)
5. In cell L4, write a formula that can be copied down thecolumn to determine if (TRUE or FALSE) the candidate should beautomatically hired based on the given criteria. (Hint: Forcriteria between two values, test that the value is both >- thelower limit and <- the higher limit.) 6. Write a formula in cellM4 that can be copied down the column to determine if thiscandidate is not automatically hired. (Hint: Use the resultsdetermined in Step 5.) 7. Write a formula in cell N4 that can becopied down the column to determine if no decision is made on thisapplicant. Recall that no decision is made if the applicant is 8.Write a formula in cell I14 that can be copied across the row(through column N) to 9. Write a formula in cell 115 that can becopied across the row (through column N) to 10. To summarize theresults, write a formula in cell 116 that displays the total numberboth not automatically disqualified (K) and not automatically hired(M). determine if all of the applicants have invalid scoresdetermine if any of the applicants have invalid scores. ofapplicants who have invalid scores. Copy this formula across therow (through column N). This formula should automatically update ifany of the scores or criteria are later modificd 11. Applyconditional formatting to highlight the important points, asfollows: Highlight all of the TRUE values in the AutomaticallyDisqualified column 04:J12) using a dark green and bold textformat. a. b. Use gradient fill blue data bars to highlight thePersonal Interview Rating scores of the applicants Highlight thename of any applicant with an Employment Score of more than 720using a light blus hukwo c.
Highlight the name of any applicant with an Employment Score ofmore than 720 using a light blue background. c. Level 2- EstimatingPainting Job Costs for NT Painting & Sons For the past year,you have been working with a medium-sized painting contractor, NTPainting & Sons, doing everything from running errands tocutting the weekly paychecks $ and filing the appropriate quarterlyemployment withholding forms with the IRS. Given Finance yourknowledge of spreadsheets, your boss has asked you to create anExcel worksheet for estimating the price of individual paintingjobs. Your boss wants the workshcet to contain some basic inputinformation and automatically calculate an estimated price so thata customer can quickly know the cost of the proposed work. Thevariables to be considered are as follows: . The dimensions of cachroom length, width, and height . The condition of the wallsurfaces, where I represents excellent, 2 represents reasonable buthas some peeling and/or old paint, and 3 represents poor conditionwith major
Application for Employment
Name
College
GPA
Major Code
Refer-ences
Personal Interview Rating
Work Experience
Employment
Score
School Rank
Invalid
Scores/
Major?
Automatically Disqualified
Not Automatically Disqualified
Automatically Hired
Not Automatically Hired
No Decision
Anderson
3.7
2
2
5
TRUE
650
12
Ryan
4.0
18
3
5
TRUE
800
1
Greg
2.0
15
2
3
TRUE
780
5
Jackie
3.2
30
2
4
TRUE
710
3
Sandra
3.0
20
1
5
TRUE
600
26
Lindsey
3.9
4
3
4
TRUE
740
15
Carolyn
3.8
16
5
6
FALSE
720
8
Steven
2.6
500
2
3
FALSE
299
55
Max
3.8
19
4
1
TRUE
760
6
All values True
Any values True
Number of True values