Excel Assignment

BSIS 105

Assignment 6

Fall 2014

This is an assignment again dealing with intermediate level Excel. This assignment deals with data from a software call center that handles Microsoft Office products. In this assignment we will use an Excel file (i.e. flat file). You will answer questions concerning this data using pivot tables.

In this assignment, you will be analyzing data for a call center (i.e. a help desk) that handles problems with the Microsoft Office products. Each time a customer calls the center, the consultant responding to the call logs the data about the call in a database (the one provided for this problem). The data is held in an Excel file that was downloaded from a star-schema data warehouse.

We are interested in using the following data:

CallID – Unique phone call identifier

AppName – Name of MS Office application experiencing problems

CustomerName – Name of customer company needing help

Compexity – Difficulty level of the problem

Date – The date the call was serviced

DOW – Day of the week, where Sunday=1, Monday=2, etc.

Year – Year of the call

Month – Month of the year of the call where 1 = January, 2 = February, etc.

Day – Day of the month of the call

Hour – Hour of the day the call was serviced, Midnight to 1am = 1, 1am to 2am = 2, etc.

StaffLevel – The number of consultants working in that hour

OnHoldMins – Number of minutes the customer was on phone hold waiting for the call to be answered

ServiceTimeMins – amount of time for consultant to solve problem

The instructions that were given to the consultants for entering data into the database are:

1. When you answer the call, issue the next sequential CallID to that call.

2. Enter the MS Office application name. The possible values are ACCESS, EXCEL, POWERPOINT or WORD

3. Enter the name of the customer company involved in the call

4. Enter the complexity of the problem. Possible values are EASY, MEDIUM or DIFFICULT

5. Enter the date of the call.

6. Enter the day of the week where Sunday=1, Monday=2, etc.

7. Log the hour of the day as 1, 2, 3, etc. where 1 = Midnight to 1am, 2 = 1am to 2am, etc.

8. Enter the amount of time the customer was on hold before you answered the call.

9. Enter the amount of time it took you to resolve the customer’s problem.

Data Analysis

The data that you will use for this assignment is in an Excel spreadsheet. The Excel data for this assignment has been reduced to a single month’s worth of data. You will use the data in the spreadsheet to answer the following questions. You should employ the pivot table functionality of Excel to do your analysis.

Following are a series of questions that you are to answer based on the data provided.

Questions:

For the following questions you should use the

Help_Desk_Spreadsheet_December_Only.xls

Excel file that I have placed on Blackboard. You will create pivot tables using this data to answer the following questions:

1. How many separate customer companies did our help center service? How many calls were made by each of these customers?

2. For all of the calls, for each customer, how many calls were made for each application and what was the average service time for each of the customers for each application? Is there anything interesting that you can see in these results? That is, are there any customers or applications that look different from the others?

3. Next you need to look at a combination of the different applications, the complexity of the problem and the different customers as characteristics and the number of calls and the average service time as key figures. Use this pivot table to answer the following questions:

a. Looking at all companies at once (that is, don’t separate out each company), analyze the complexity of the calls and the number of calls and the average service time. Are there any conclusions you can come to?

b. Again, looking at all of the companies at once (i.e. don’t separate them), analyze the complexity of the calls for each of the applications and analyze the number of calls and average service time. Are there any conclusions you can come to?

c. Now do the analysis in a and b above for each of the companies. That is for each company answer parts a and b above. Are there any conclusions you can come to? How useful is the data shown in part 3c-b?

The answers to these questions should be placed in separate pivot tables with question 3 having 4 separate pivot tables. Each pivot table should be placed on a separate Excel page with the appropriate title and the tab appropriately labeled. Your analysis can either be text entered on the appropriate page of each spreadsheet or as a separate Word document.

Assignment Submission

Submit your workbook electronically by submitting it in Blackboard. If you created a separate MSWord document, also submit that electronically. You must also bring a printed copy of your assignment to class on Thursday, November 6, 2014. I will be collecting them at the beginning of the class. Remember! Late submissions are not accepted for grading.