YOF EWS05 A1 - Helping the University Call Center 1.1

YOF EWS05 A1 - Helping the University Call Center 1.1

Project Description: 
The University’s call center needs a spreadsheet developed that will use call data to analyze the performance of the center. It contacted your professor and she recommended you for the task. The spreadsheet will set up a variety of tools that will help assess the efficiency of the center and its staff. Your job is to develop the tools for the call center manager that will help with his assessment.

Instructions: 
For the purpose of grading the project you are required to perform the following tasks: 
Step    Instructions    Points Possible
1    Start Excel. Open the downloaded Excel file named e03ws05_grader_a1_start.xlsx. Save the file with the name e03ws05CallCenter_LastFirst replacing LastFirst with your name.    0.000
2    Click the Lists worksheet tab. Name the range A2:A6 as Department. Name the range A14:B18 as GradeScale. Name the range B9:H9 as DayofWeek. Select the range E1:I5, and create named ranges using the top row as the range values.    4.000
3    Click the Call Data worksheet tab. In cell A7, add an Excel table with headers using the current data set. In cell H7, add Issue as the field label. In cell I7, add Grade as the field label. In cell J7, add Weekday as the field label.     5.000
4    Select range A7:J22, and then name the entire data set, including the labels, as CallDataAll. Create named ranges using the top row as the range values.

In cell H8, add an INDEX function that will use a nested INDIRECT reference to the Dept named range listed in column C, and use the Reason field in column B as the row number to return for the department name in the referenced named range. Nest the function inside an IF function so that issues currently displaying as a 0 will display as a blank cell. Resize the column width as needed.    8.000
5    In cell I8, add a VLOOKUP function that will convert the Satisfaction Score to a grade found in the second column of the GradeScale named range. 

In cell J8, add an INDEX function that will convert the Call Day to the actual weekday found in row 1 of the DayofWeek named range. Resize the column widths as needed.    8.000
6    On the Call Data worksheet, create an advanced filter. In cell C2, type Public_Affairs. In cell G2, type Y. Run an advanced filter on the table data set using the criteria range A1:J2.     4.000
7    In cell D4, add a SUBTOTAL function that will count the number of call hour cells returned for the subset of records that have been filtered. 

In cell D5, add a SUBTOTAL function that will determine the average call length for the subset of records that have been filtered.    8.000
8    Click the Call Center Report worksheet. Add formulas that will summarize the issues for the department entered in cell B3. In cell B6, add an INDEX function that will use an INDIRECT function to retrieve the department issue list for the department listed in cell B3. Use an absolute reference to B3, and then use a relative cell reference to A6 as the row_num argument. Copy the formula down to cell B9.

In cell C6, add a COUNTIFS function that will count the number of departments on the Call Data worksheet. Use the Dept named range as criteria_range1, and then use an absolute reference to B3 as criteria1. Use the Reason named range as criteria_range2, and then use cell A6 as criteria2. Copy the formula down to cell C9.    9.000
9    In cell D6, add a COUNTIFS function that will count the number of calls coming from the department listed in B3. Use the Dept named range as criteria_range1, and then use an absolute reference to B3 as criteria1. Use the Reason named range as criteria_range2, and then use cell A6 as criteria2. Use the On_Hold named range as criteria_range3, and then use Y as criteria3. Copy the formula down to cell D9.

In cell E6, add an IF statement with a nested AND that will enter a status notice. If the number of calls on issue in cell C6 is greater than 3 and the number of calls on hold in cell D6 is greater than 2, then Check Hold Issue should display. Otherwise, nothing should display. Copy the formula down to cell E9.    10.000
10    In cell B12, add an AVERAGEIF function that will find the average call length for the Dept named range and the criteria specified in cell A12.

In cell C12, add a COUNTIF function that will count the number of calls for the Dept named range and the criteria specified in cell A12.

In cell D12, add a formula that sums two COUNTIFS formulas. The first COUNTIFS will count the number of calls associated with the Dept named range and criteria specified in cell A12 that received a grade F, and the second COUNTIFS will do the same for grade D.    9.000
11    In cell E12, add an IF statement using a nested OR function that will return any notes associated with the issue. If there are more than 10 issues reported with a grade below a C in cell D12, or when the number of scores less than C divided by the total calls in cell C12 is greater than 50%, then Explore Issues should display. Otherwise, nothing should display. Copy the formulas in B12:E12 down through row 16.    10.000
12    In cell H11, add a MAX function that will show the maximum call length minutes using the Call_Length named range.

In cell H12, add an INDEX function that will use the Dept named range as the array associated with a MATCH function to determine the lookup array for the longest call referenced in H11.

In cell H13, add an INDEX function that will pull the satisfaction rating associated with a MATCH function to determine the lookup array for the longest call referenced in H11.    9.000
13    In cell G19, type N. In cell J19, type Wednesday. In cell B22, add a DCOUNT function for the CallDataAll database to find the count of the satisfaction score currently listed in cell B21 using the criteria set up in A18:J19.    7.000
14    Add corresponding database functions in B23:B26 that find the DAVERAGE, DSUM, DMAX, and DMIN for the CallDataAll named range. Select range B22:B26, and then copy the formulas to column C.    9.000
15    Ensure that the worksheets are correctly named and placed in the following order in the workbook: Call Data, Lists, Call Center Report. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.    0.000
    Total Points    100.000