BMGT $301$

Excel Project $2$

Spring $2024$

Case Info:

To improve quality and reduce the number of defective ErgoBeds

produced, Karake, Inc. management has decided to hire $5$ new

quality control personnel. Remember from the previous narrative

$($Project $1)$ that the company has recently been experiencing

increased demand for its ErgoBeds Line, and was forced to

increase the number of shifts in its factories which led to a problem

in quality and increase in the number of defectives and returns.

Due to increased demand, the company plans on opening new

production facilities and will need new production managers as well

as more employees for customer support. Josh Diamond, the

Human Resources Manager, has established a formal process for

evaluating job applicants.

The process takes into consideration the applicant$$s academic

performance based on the applicant$$s GPA and letters of

recommendation from previous employer$($s$)$ and$/$or a college

professor$($s$).$ In addition, the applicant is evaluated based on his$/$her

performance during the interview process and whether or not the

applicant had prior years of experience.

Further, all applicants are given an Excel business$-$oriented exam to

determine the level of their Excel knowledge. Additionally, the HR

manager is interested in whether or not they are flexible to travel,

the number of times the applicant has transitioned between jobs

during the last $5$ years, if they have an international work experience

and number of foreign languages they speak.

Josh has asked you to evaluate the candidates based on the

following information and set of criteria:

$1.$ GPA Score $($Ranges from $2\mathrm{.}0$ to $4\mathrm{.}0)$

$2.$ Letters of recommendations $($references$)$ from previous

employers$/$professors $(>=0)$

$3.$ Interview rating score $($ranges from $1$ to $10)$

$4.$ If the applicant has previous work experience $($Binary: $0$ for NO

and $1$ for YES$)$

$5.$ Score on the Excel Test $($Ranges from $500$ to $1000)$

$6.$ Travel Flexibility $($Binary: $0$ for NO and $1$ for YES$)$

$7.$ Number of transitions between jobs during the last $5$ years

$($Ranges from $0$ to $5)$

$8.$ International Experience $($Binary: $0$ for NO and $1$ for YES$)$

$9.$ Foreign Languages $($Ranges from $0$ to $3).$

Karake, Inc.

HR Department has established the following criteria to determine if

the applicant should be eliminated from further consideration or if

the applicant is to undergo another round of interviews.

An applicant is eliminated from further evaluation if ANY of the

following is true:

$$ GPA is less than $2\mathrm{.}7$

$$ The applicant provided less than $3$ references

$$ The applicant scores less than $705$ on the Excel Test AND the

applicant has an interview rating of $7$ or less

$$ The applicant is not flexible for travel. $($Recorded on Excel as $1$ for

YES and $0$ for NO$)$

$$ The applicant changed jobs more than $2$ times during the last $5$

years.

An applicant is automatically scheduled for another round of

interviews if ALL of the following criteria are met:

$$ GPA is $3\mathrm{.}3$ or above

$$ The applicant provided at least $3$ references

$$ The applicant scored $850$ or above on the Excel Test OR the

applicant has an interview rating of at least $8$

$$ The applicant has worked internationally

$$ The applicant knows at least $1$ foreign language in addition to

English.

If an applicant is neither eliminated nor automatically scheduled for a

second round of interviews, the applicant is qualified as POSSIBLE.

Questions:

Open the Excel File with Project$\_2\_$Data and save it on your system

as Project$2\_$YourFirstName$\_$YourLastName $($Example

$$Project$2\_$Paul$\_$Shapiro.xlsx$)$

$1.$ Rename Sheet $1$ as $$Applications$(0\mathrm{.}3$ points$)$

$2.$ In Column K$,$ create a formula using the criteria listed above to

determine those applicants who will be automatically eliminated.

$(0\mathrm{.}5$ points$)$

$3.$ In Column L$,$ create a formula using the criteria listed above to

determine those applicants who will be automatically invited to a

second interview. $(0\mathrm{.}6$ points$)$

$4.$ In Column M$,$ create a formula that will label the applicant as

$$POSSIBLE$$ if s$/$he were neither automatically eliminated nor

automatically invited for a second round of interviews. $(0\mathrm{.}8$ points$)$

$5.$ In column N create a formula which will send the appropriate

email$/$letter to the applicant based on the HR assessment above

$(0\mathrm{.}8$ points$)$:

a$.$ If the applicant is automatically eliminated the system will return

$$send a letter of rejection$$;

b$.$ If the applicant is invited for a second round of interviews the

system will return $$send a letter of invitation$$;

c$.$ If the applicant is labeled as POSSIBLE, the system will return

$$may be considered for other jobs in the future$.$

PART B:

Click on the Transaction sheet.

Use the data in the Transaction sheet to answer the following

questions:

$1.$ During the ordering process of the Ergobeds, some customers

have entered in their addresses with minor typos. Their most

common errors are duplicate spaces and incorrect capitalization.

Use the appropriate Text function$($s$)$ in column G to revise the

addresses. $(\mathrm{.}5$ point