Shelly Cashman Excel 365 | Module 6: SAM Project B Onboard Recruiting

GETTING STARTED

1. Save the file SC_EX365_6B_FirstLastName_1.xlsx as SC_EX365_6B_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_EX365_6B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Chaya Agarwal runs Onboard Recruiting, an online recruiting agency in Greenville, South Carolina that matches job candidates with employers. Chaya is analyzing completed and ongoing placements and asks for your help in using Excel tables to complete the analysis. Go to the Placements worksheet, which lists the job placements made in the current year. Create a table as follows so that Chaya can summarize and filter the data and display placements with the highest commissions:

a. Format the job placement data (range B2:H18) as a table with headers using Blue-Gray, Table Style Light 14.

b. Use Placements as the name of the table.

2. The placement commission is 10 percent of the starting salary for each employee. Chaya asks you to add this information to the Placements table. Add a calculated column to the table as follows:

a. Add a column named Salary to the right of the Commission column.

b. In cell I3, insert a formula that uses a structured reference but not a function to multiply 10 by the amount in the Commission column.

c. Copy the formula in cell I3 to the rest of the column if Excel does not fill the column automatically.

3. Filter the Placements table using a custom AutoFilter to display placements with a Commission amount greater than $5,000.

4. Go to the Candidates worksheet, which contains the Candidates table listing active job placement candidates. Chaya wants to quickly identify the candidates by their minimum salary amount. Sort the Candidates table in ascending order by Min Salary amount.

5. Chaya asks you to create a separate list of candidates that have scheduled interviews. Use an advanced filter as follows to list these candidates in a new range:

a. In cell H17, type Yes as the value to filter on in the criteria range.

b. Create an advanced filter using the Candidates table (range B2:H12) as the List range.

c. Use the range B16:H17 as the Criteria range.

d. Copy the results to another location, starting in the range B19:H19.

6. As a contrast, Chaya also wants you to list the candidates that do not have scheduled interviews. Filter the Candidates table as follows:

a. Display the filter arrows in the Candidates table.

b. Filter the table to display only candidates that do not have scheduled interviews.

7. Go to the Employers worksheet, which lists employers who have requested candidates for open job positions. Chaya suspects the Employers table has a duplicate record. Identify the duplicate as follows:

a. Clear the filter from the Employers table to display all the records.

b. Remove duplicates from all the columns in the table.

8. Add a Total Row to the Employers table, which automatically sums the starting salary amounts. Using the total row, display the count of the positions in the Position Type column.

9. Chaya asks you to identify the employers that have contracted for a job search of 90 days or more to complete, job searches of 60 days or more to complete, and job searches of less than 60 days.

a. In the Contracted Days column (range D3:D17), create a new Icon Set conditional formatting rule using the 3 Triangles icons.

b. Reverse the icon order.

c. Display the red triangle icon in cells with a Number type value greater than or equal to 90.

d. Display the yellow rectangle icon in cells with a Number type value greater than or equal to 60.

e. Display the green triangle icon in cells with a Number type value less than 60.

10. Chaya wants you to summarize the number of placements by the position type and calculate their total and average salary amounts. Calculate this information for Chaya as follows:

a. In cell J3, enter a formula using the COUNTIF function that counts the number of proposed Internship projects, using Employers[Placement Type] as the range and cell I3 as the criteria.

b. Fill the range J4:J7 with the formula in cell J3.

c. In cell K3, enter a formula using the SUMIF function that totals the salary for Internship placements, using Employers[Placement Type] as the range, cell I3 as the criteria, and Employers[Starting Salary] as the sum_range.

d. Fill the range K4:K7 with the formula in cell K3.

e. In cell L3, enter a formula using the AVERAGEIF function that averages the budget for Internship placements, using Employers[Placement Type] as the range, cell I3 as the criteria, and Employers[Starting Salary] as the average_range.

f. Fill the range L4:L7 with the formula in cell L3.

11. In the range I10:L15, Chaya asks you to insert a summary of the position openings. Insert this data as a table as follows:

a. Insert a table in the range I10:L15, specifying that the data has headers.

b. In the new table, enter the data shown in Table 1.

c. Apply Blue-Gray, Table Style Light 14 to the new table to match the formatting of the Employers table.

Table 1: Data for the New Table

| I | J | K | L 10 | Placement Type | Interviews | Offers | Commission 11 | Internship | 1 | 1 | 4,500 12 | Limited term | 2 | 0 | 6,000 13 | Permanent | 4 | 3 | 5,700 14 | Project | 1 | 1 | 4,900 15 | Temporary | 1 | 0 | 5,100

12. Go to the Commissions worksheet, which lists all the current and ongoing job placements. Chaya wants you to display the data by position type and then list the placements by employer. Sort the data in the table in ascending order first by position type and then by employer.

13. Chaya also wants you to calculate commission subtotals for each position type. (Hint: You must complete all actions of this step correctly to receive full credit.)

a. Convert the table to a range.

b. Insert a subtotal at each change in the Position Type value.

c. Use the Sum function to calculate the subtotals.

d. Add subtotals to the Commission values only.

e. Include a summary below the data.

f. Collapse the outline to display only the subtotals for each position type and the grand total.

14. Go to the Placement Lookup worksheet, which contains a table named Lookup that lists placement details, including the Placement ID code used to refer to the placements. Chaya asks you to complete the Placement Information data in the range H3:I15. Create a formula that looks up an employer based on its Placement ID as follows:

a. In cell I4, begin to enter a formula using the VLOOKUP function.

b. Use the Placement ID (cell I3) as the lookup_value.

c. Use the Lookup table as the table_array.

d. Return the value in column 2 of the table_array.

e. Specify an exact match.

15. The next formula looks up an employer based on the placement start date. To provide this information, create a formula using a function and structured references as follows:

a. In cell I7, begin to enter a formula using the XLOOKUP function.

b. Use the start date (cell I6) as the lookup value.

c. Use the Start Date column as the lookup_array.

d. Use the Employer column as the return_array.

16. The next formulas identify the number of placements that have salaries of more than $50,000 and calculate the average salary of permanent job placements. Create formulas that provide this information as follows:

a. In cell I11, create a formula using the DCOUNT function to count the number of placements with salary amounts greater than $50,000, using the Lookup table (range B2:F33) as the database, "Salary" as the field, and the range H9:H10 as the criteria.

b. In cell I15, create a formula using the DAVERAGE function to average the salary amounts for Permanent placements, using the Lookup table (range B2:F33) as the database, "Salary" as the field, and the range H13:H14 as the criteria.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Placements Worksheet

Final Figure 2: Candidates Worksheet

Final Figure 3: Employers Worksheet

Final Figure 4: Commissions Worksheet

Final Figure 5: Placement Lookup Worksheet