Extra Credit: Analyzing a Call Center
Data file needed: ExtraCreditData
Save your file as: ExtraCreditData_LastFirst
A call center needs a workbook developed that will use data to analyze the performance of the call center. The call center contacted your professor and he recommended you for the task. The workbook will set up a variety of tools that will help assess the efficiency of the center and its staff. You will develop a workbook for the call center manager that will help with the center’s data analysis.
- Open ExtraCreditData.xltx (notice that this is a template file with xltx extension) and save it as ExtraCreditDataxlsx (This is an Excel file with xlsx extension). Enable the content if necessary. On the documentation worksheet, enter today’s date and your name in cells A8 and B8.
- On the CallData worksheet, create a table with headers using range A1:J120. Name the table CallData. Create name ranges for each column using the header as the name. Apply Table Style Medium 7. Ensure all data and headings are visible after you complete the tasks.
- In Issue column, enter a retrieval function that returns the issue from the table in the IssueList worksheet. Use 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 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.
- In Grade column, use nested IF functions to specify the grade level of each call. The function will return A+ for satisfaction rating greater than or equal 9, return B for satisfaction rating greater than or equal 8, return C for satisfaction rating greater than or equal 6, and return D for satisfaction rating greater than or equal 4. Else, the function will return F.
- In Difficulty column, use proper logical functions to determine whether a call is complicated or simple. If a call length is longer than 5 and satisfaction rating is less than or equal to 6, the function returns “Complicated”. Otherwise, the function returns “Simple”.
- On the Report worksheet, add formulas that will summarize the issues for the department entered in column A:
- In column B, add a conditional function that will calculate the average of call length coming from the department listed in column A. Use the named ranges as criteria range, and then use proper reference to cells in column A as criteria.
- In column C, add a conditional function that will calculate the total of call length coming from the department listed in column A. Use the named ranges as criteria range, and then use proper reference to cells in column A as criteria.
- In column D, add a conditional function that will count the number of calls coming from the department listed in column A and have grade “F”. Use the named ranges as criteria range, and then use proper reference to cells in column A as criteria.
- In cell G1, add a proper function that will return the longest call.
- In cell G2, add proper retrieval functions that will return the department name that has the longest call. Use named range as the array and G1 in the lookup array with an exact match.
- Develop a PivotTable for further analysis of the table. Use the CallData table to create a PivotTable on PivotTableAnalysis worksheet cell A5.
- The PivotTable should have the Call Day field as the report filter, Issue as the row labels, Dept as the column labels, and Call Length in the values area.
- Change the Sum of Call Length to show the average instead. Format as Number with 0 decimals.
- In cell A6, type Issue. In cell B5, type Department.
- Apply Pivot Style Dark 7 and Banded Rows style.
- Add a slicer for the Grade field, and then choose all options except F. Position the slicer to the right of the PivotTable.
- In cell A1, type Pivot Table Analysis. Merge and center in range A1:G1, and then apply the theme cell style 60% Accent 6 to the range.
- Develop a PivotChart for further analysis of the pivot table:
- Select chart 3-D Stacked Column.
- Position the PivotChart below the PivotTable.
- Hide the field buttons, resize the chart so it covers column F, add Issues by Departments as the chart title, move the legend to the left, and add the data labels.
- On the CallCenterAnalysis worksheet, use Solver to minimize the Payroll/Week by determining the optimal number of employees to have assigned to each schedule. Remember these points as you complete the Solver Parameters dialog box.
- The number of people in the range F14:L14 must be greater than or equal to the demand (range F16:L16) so there are enough people working for that day’s needs.
- The number of employees scheduled in D6:D12 must be greater than zero.
- Solve this model, using the Simplex LP method.
- Keep the Solver solution in the model.
- Create an Answer report.
- Save the file and close Excel. Submit your excel file on canvas.