Johnson,Human Resource Information Systems, 5e SAGE Publishing, 2021 Recruitment at Salesteq Corporation Assignment Purpose Attracting employees by using different recruitment sources is an important aspect of human resource management. Analyzing the effects of different recruitment sources as well as the total strategy can assist in planning future recruitment strategies. In this assignment,you will analyze recruitment planning scenarios based on the recruitment experience of the Salesteq Corporation. File Needed:HR Recruiting.xlsx Familiarize yourself with the spreadsheet before beginning your work. Background Salesteq Corporation is a successful consumer products’ company located in San Francisco. The company has been growing at a constant rate during the past three years. Productivity gains have been realized through computerization of its services. Salesteq has strongly supported its personnel staff since people are viewed as a critical asset for the continued success of the company. Employees are recruited from four sources: the web, newspapers, radio, and referrals. All recruits are interviewed and a selection decision is made “by someone” based on the interview score? Salesteq’s HR staff monitor the number of interviews they conduct by recruitment source. Additionally, recruitment data on the number of people hired, average performance of hires at the end of the first year, and total recruitment costs are collected. In this assignment, you will be calculating a number of HR metrics pertaining to different areas of recruitment. These include metrics forassessing Salesteq’s previous recruitment strategy, planned interview strategy, and actual vs. forecasted recruitment. These computations will help you to understand what assessments go into evaluating different areas of recruitment at an organization. Note As you work through the assignment, please be sure to continually save your work after each section. Save your work as a new Excel file using your first initial and last name. For example, Joe Smith would be: “jsmith_HR_Recruiting.xlsx”. Calculations Make sure you round all calculations performed in the spreadsheet. After all, you can’t have 1/3 of a person. This will require the use of the ROUNDING function. Here is how to use this function:2 Johnson,Human Resource Information Systems, 5e SAGE Publishing, 2021 =ROUND (number, num_digits) Where, ●number= number (or cell) that you are rounding. You may also use an Excel formula here. ●num_digits= the number of digits to which number should be rounded. As with any of the functions used in this assignment, Microsoft and YouTube have excellent tutorials that can assist you in using them. Part I-Results of Previous Recruitment Strategy Please refer to the data in the section of the Excel sheet titled “Recruitment Data by Source for Previous Year” to complete the calculations below. In this section, youwill be looking backwards at your interview strategy and success by calculating a number of key metrics. These are: 1. Cost Per Interview. Calculate cost per interview by dividing the total cost for recruiting for each source by the number of interviews. Calculate this for all sources. 2. Cost Per Hire.Calculate cost per hire by dividing the total cost for recruiting by source by the number of hires. Calculate this for all sources. 3. Percent Offers. Calculate percent offers by dividing the number of offers extended to candidates for each source by the number of interviews. Calculate this for all sources. 4. Yield Ratio.Calculate yield ratio by dividing the number of hires from each source by the number of interviews for each source. Calculate this for all sources. 5. Total Performance.Calculate total performance by source by multiplying the number of hires by source by the average performance. Calculate this for all sources. 6. Probability of Quitting.Calculate an estimate of the quitting probability for hires of each source by dividing the number of quits in year 1 (e.g. the anticipated number of new hires who will quit within the first year) by the number of hires. Calculate this for all sources. Part II-Results of Planned Interview Strategy Please refer to the data in the sections of the Excel sheet titled “Interview Strategy by Recruitment Source” and “Results of Planned Interview Strategy” to complete the calculations below. In this section, you will be forecasting your recruitment plans for the coming year.3 Johnson,Human Resource Information Systems, 5e SAGE Publishing, 2021 1. Number of Offers.To obtain the number of offers, multiply the number of interviews by the percent of offers received last year. Do this for each recruitment source. 2. Number of Hires.To obtain this figure, multiply the number of interviews for newspapers by the yield ratio from last year. Do this for each recruitment source. 3. Total Performance.To obtain this figure, multiply the number of hires by the average performance from last year (Row 12). Do this for each recruitment source. 4. Quits in 1 Year.To estimate the quits in one year, multiply the number of hires by the probability of quitting last year. Do this for each source. 5. Total Cost.To obtain the total cost of recruiting by newspaper, multiply the number of interviews for newspapers by the cost per interview for newspapers. Do this for each recruitment source. Question:Now that the calculations for Part III are complete, can this organization use historical data to make projections about future recruiting needs? Part III-Totals for Actual vs. Forecasted Recruitment Please refer to the data in the section of the Excel sheet titled “Totals for Actual vs. Forecasted Recruitment” to complete the calculations below. Notice that thissection of the spreadsheet presents a summary analysis so the effects of forecasted recruitment scenarios can be quickly compared with previous data. Notice also that the ACTUAL and FORECASTED columns are already completed. The ACTUAL column represents actual data from last year. The FORECASTED column represents our forecasted information from above. The ACTUAL and FORECASTED columns currently yield identical results because the # Interview figures found on the “Recruitment Data by Source for Previous Year” section and the “Interview Strategy by Recruitment Source” data are the same. This was done to ensure that you entered your formulas correctly. 1. Set all your data for all sources in the “Interview strategy by Recruitment Source” to zero. Next, increase the number of interviews from Part II for the recruitment source with the highest historical yield ratio until the FORECASTED number of hires in the FORECASTED column (e.g. cell F58) equals the ACTUAL number of hires (D58). Question:What is the interview source? How many interviews were required? Before continuing, reset your data to the original forecasted number of interviews.4 Johnson,Human Resource Information Systems, 5e SAGE Publishing, 2021 2. Further analysis indicated that 65 percent of Salesteq minority recruits are attracted by radio. Assume that a minimum of 70 interviews are needed for those who apply due to radio. Plan an interview strategy such thatcosts per hiredo not exceed ACTUAL costs per hire with as many FORECASTED outcomes as possible (number of hires, average performance, and total yield) exceeding ACTUAL outcomes. Explain your strategy for this scenario. This will require you to change the numbers across all recruitment sources, to identify an optimal mix of recruitment sources. As with the previous analysis, reset your interview numbers to the original data. 3. Next imagine that because of budget cutbacks, Salesteq will only budget $6,500 for the next year’s recruitment budget. Explain your strategy for this scenario. Question:What are the advantages and disadvantages of this recruitment planning model? Explain.5 Johnson,Human Resource Information Systems, 5e SAGE Publishing, 2021 Grading: Study Questions:You will be graded according to the following rubric: CriteriaSuperior(10-8)Sufficient (7-5)Minimal(4-2)Unacceptable(1-0) Depth ofReflection Responsedemonstrates anin-depth reflectionon, andpersonalization of,the theories,concepts, and/orstrategiespresented in thecourse materials todate. Viewpointsand interpretationsare insightful andwell supported.Clear, detailedexamples areprovided, asapplicable. All sources are cited correctly. Response demonstratesa general reflection on,and personalization of,the theories, concepts,and/or strategiespresented in the coursematerials to date.Viewpoints andinterpretations aresupported.Appropriateexamples are provided,as applicable. All sources are cited, but some errors may bemade with citations.Responsedemonstrates aminimal reflection on,and personalization of,the theories, concepts,and/or strategiespresented in thecourse materials todate. Viewpoints andinterpretations areunsupported orsupported with flawedarguments. Examples,when applicable, are not provided or are irrelevant to the assignment. Severe errors in citations. Response demonstrates a lack of reflection on, or personalization of, the theories, concepts, and/or strategies presented in the course materials to date. Viewpoints and interpretations are missing, inappropriate, and/or unsupported. Examples, when applicable, are not provided. Sources not provided or not cited. StructureWriting is clear,concise, and wellorganized withexcellentsentence/paragraph construction.Thoughts areexpressed in acoherent andlogical manner.There are no morethan three spelling,grammar, or syntax errors per page of writing. Writing is mostly clear,concise, and wellorganized with goodsentence/paragraphconstruction. Thoughtsare expressed in acoherent and logicalmanner. There are nomore than five spelling,grammar, or syntax errors per page of writing. Writing is unclearand/or disorganized.Thoughts are notexpressed in a logicalmanner. There aremore than fivespelling, grammar, orsyntax errors per page of writing. Writing is unclear and disorganized. Thoughts ramble and make little sense. There are numerous spelling, grammar, or syntax errors throughout the response. Evidenceand Practice Response showsstrong evidence ofsynthesis of ideaspresented andinsights gainedthroughout theentire course. Response showsevidence of synthesis ofideas presented andinsights gainedthroughout the entirecourse.Response shows littleevidence of synthesisof ideas presented andinsights gainedthroughout the entirecourse.Response shows no evidence of synthesis of ideas presented and insights gained throughout the entire course.