Shelly Cashman Excel 365 | Module 6: End of Module Project 2 Pros on Call

GETTING STARTED

1. Save the file SC_EX365_EOM6-2_FirstLastName_1.xlsx as SC_EX365_EOM6-2_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_EOM6-2_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. Kai Tang is a financial analyst for Pros on Call, an employment agency in San Diego, California, that provides personnel services to match organizations with temporary and part-time employees. Kai is reviewing recent job postings and asks for your help in using Excel tables to analyze the data. Go to the May Postings worksheet, which lists the jobs posted in May 2029. Format the May postings data (range B2:I28) as a table with headers using Aqua, Table Style Medium 5 so that Kai can summarize and filter the data. Use May as the name of the table.

2. Add a column to the right end of the table and use Job Total as the column heading. In cell J3, enter a formula without using a function that uses structured references to multiply the number of weeks (cell G3) by the hours per week (cell H3) and by the hourly rate (cell I3) to find the total for the job. Fill the range J4:J28 with the formula in cell J3 if Excel does not do so automatically. (Hint: To avoid a #SPILL error, enable implicit intersection by placing the This Row specifier (@ character) in front of field names.)

3. Filter the table using a custom AutoFilter to display jobs with a Rate greater than $35.00.

4. Go to the Job Lookup worksheet, which contains a table named JobLookup listing jobs posted in April and May that have not been filled yet. Kai wants you to look up job data and insert it in the Job Information area. In cell I4, enter a formula using the XLOOKUP function. Look up the value in cell H4, use the Job ID column in the JobLookup table (JobLookup[Job ID]) as the lookup_array, and use the Posted Date column (JobLookup[Posted Date]) as the return_array to find the posted date for Job ID M-40688.

5. Next, identify the number of jobs with total payments greater than $20,000. In cell I7, create a formula using the DCOUNT function to count the number of jobs with job totals greater than $20,000, using a structured reference to all the columns in the JobLookup table (range B1:F37) as the database, "Job Total" as the field, and the range H6:H7 as the criteria.

6. Finally, calculate the average job total for Remote jobs. In cell I10, create a formula using the DAVERAGE function to average the job totals for Remote jobs, using a structured reference to all the columns in the JobLookup table (range B2:F37) as the database, "Job Total" as the field, and the range H9:H10 as the criteria.

7. Kai asks you to illustrate the job totals by date for each job type. Sort the JobLookup table in alphabetic order by Job Type. Create a treemap chart based on the range D2:F37. Resize and reposition the chart so that its upper-left corner is in cell H14 and its lower-right corner is in cell M37. Use Job Types and Totals by Date as the chart title.

8. Go to the Bonus Tracker worksheet, which includes the Bonuses table listing jobs, number of days posted, and the fee Pros on Call received in May. Kai suspects that the Bonuses table has a duplicate record. Clear the filter from the Bonuses table to display all the records. Use the Remove Duplicates tool to identify duplicates in the Job ID column and remove the duplicate record.

9. The Bonuses table is missing information for one job. Add a record to the end of the table (row 19) containing the data shown in Table 1. Right-align the value in cell G19 to match the alignment of the column.

Table 1: New Record for the Bonuses Table

Job ID | Job Title | Industry | Job Type | Days Posted | Fee | Bonus? M-40688 | Registered Nurse | Medical | Contract | 7.0 | $900 | [leave blank]

10. A Pros on Call account manager receives a bonus if a job is posted for 10 days or less and the company receives a fee of more than $1,000. In cell H3, enter a formula using the AND function that includes structured references to display TRUE if the [@[Days Posted]] of a job is =10 and the [@[Fee]] is 1000. Fill the range H4:H19 with the formula in cell H3 if Excel does not do so automatically.

11. Kai also wants you to summarize the number of jobs by industry and calculate their total and average fees. In cell K3, enter a formula using the COUNTIF function that counts the number of Academic jobs, using Bonuses[Industry] as the range and cell J3 as the criteria. Fill the range K4:K8 with the formula in cell K3.

12. In cell L3, enter a formula using the SUMIF function that totals the fees paid for Academic jobs, using Bonuses[Industry] as the range, cell J3 as the criteria, and Bonuses[Fee] as the sum_range. Fill the range L4:L8 with the formula in cell L3.

13. In cell M3, enter a formula using the AVERAGEIF function that averages the fees paid for Academic jobs, using Bonuses[Industry] as the range, cell J3 as the criteria, and Bonuses[Fee] as the average_range. Fill the range M4:M8 with the formula in cell M3.

14. Go to the Promos worksheet, which lists all the company promotional spots released in May. Sort the data in the table in ascending order first by promo type and then by release date.

15. Kai wants you to calculate subtotals for each promo type. Convert the table to a range. Insert a subtotal at each change in the Promo Type value, using the Sum function to calculate the subtotals. Add subtotals to the Promo Amt values only, and include a summary below the data. Collapse the outline to display only the subtotals for each promo type and the grand total.

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: May Postings Worksheet

Final Figure 2: Job Lookup Worksheet

Final Figure 3: Bonus Tracker Worksheet

Final Figure 4: Promos Worksheet