Excel Project The file Phone Data for Project contains one year's phone records for a bank call center. The bosses are interested in a dashboard that will help them to assess the performance of their system and service agents. The tab, "Data Dictionary" can be used to decipher what is included in the data, for example, agent names can be found in the last column of the data. The metrics that they would like to see visualized are the number of calls handled as well as the service time for those calls. Your task is to build a dashboard that can be utilized by supervisors to see how their system and/or specific agents are performing. They would like the capacity to see individual measures as well as company averages for comparison. If you find a different data set that you would like to use, you will need to get that approved by the instructor. Must be done in excel, then presented in PowerPoint. Needs to meet the following criteria: 1- At least 4 well-constructed visualizations of the data 2- At least one measure of performance should be expressed over different time intervals (i.e. per month versus per week) 3- A comparison between individual results to the overall company results 4- At least one determination of "ranks" for individuals (top performers or bottom performers for some time frame that could be used for some sort of bonus or incentive) 5- Clear and thorough explanations of the dashboard as well as answers to questions during the presentation A86 Agent - service A B D E F G H I J K L M N P Q R S Anonymous Bank Call-Center Data Documentation by llan Guedj and Avi Mandelbaum 2/9/2000 This document describes telephone data, recorded over 12 month (from 1/01/99 till 31/12/99), at the telephone call-center of "Anonymous Bank" in Israel. The data was organized by llan Guedj, who was at the time a graduate student of the Faculty of Industrial Engineering and Management at the Technion, Haifa. General Description The call center of "Anonymous Bank" provides several different services: - Information on and transactions of checking and saving, to bank-customers - Computer generated voice information (through VRU = Voice Response Unit) - Information for prospective customers - Support for the customers of "Anonymous Bank" web-site (internet customers) The call center constitutes of: - 8 agent positions - 1 shift-supervisor position - 5 agent positions for internet services (in an adjacent room) During weekdays (Sunday to Thursday), the call center is staffed from 7:00am to midnight. During weekends (Friday-Saturday), it closes at 14:00 on Friday and reopens at around 20:00 on Saturday. The automated service (VRU) operates 7 days a week, 24 hours a day. Data Structure The data archives all the calls handled by the call center, over the period of 12 months from January 1999 till December 1999. The data consists of 12 files, a file per month. Each file consists of records (lines), a record per phone call (between 20,000 to 30,000 calls per month). Each record has 17 fields, which will now be described in details. 1) vru+line - 6 digits Each entering phone-call is first routed through a VRU: There are 6 VRUs labeled AA01 to AA06. Each VRU has several lines labeled 1-16. There are a total of 65 lines. Each call is assigned a VRU number and a line number. 2) Call_id - 5 digits Each entering call is assigned a call id. Although they are different, the id's are not necessarily consecutive due to being assigned to different VRUs. 3) Customer_id - 0 to 12 digits This is the identification number of the caller, which identifies the customer uniquely; the ID is zero if the caller is not identified by the system (as is the case for prospective customers, for example). 4) Prioritv - 1 digit Data Dictionary january february march april may june july august september october november december + A1 fx vru+line \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline 2 & A & B & C & D & E & F & G & H & 1 & J & K & L & M & N & 0 & P & Q \\ \hline 1 & vru+line & call_id v & customer_id & priority & type v & date & vru_entry v & vru_exit v, & vru_time v & q_start v & q_exit & q_time v & outcome v & ser_start v & ser_exit & ser_time v & server \\ \hline 2 & AA0101 & 33116 & 9664491 & & 2 PS & 01/01/1999 & 0:00:31 & 0:00:36 & 5 & 0:00:36 & 0:03:09 & 153 & HANG & 0:00:00 & 0:00:00 & 0 & NO_SERVER \\ \hline 3 & AA0101 & 33117 & 0 & & 0PS & 01/01/1999 & 0:34:12 & 0:34:23 & 11 & 0:00:00 & 0:00:00 & 0 & HANG & 0:00:00 & 0:00:00 & 0 & NO_SERVER \\ \hline 4 & AA0101 & 33118 & 27997683 & & 2 PS & 01/01/1999 & 6:55:20 & 6:55:26 & 6 & 6:55:26 & 6:55:43 & 17 & AGENT & 6:55:43 & 6:56:37 & 54 & MICHAL \\ \hline 5 & AA0101 & 33119 & 0 & & 0 PS & 01/01/1999 & 7:41:16 & 7:41:26 & 10 & 0:00:00 & 0:00:00 & 0 & AGENT & 7:41:25 & 7:44:53 & 208 & BASCH \\ \hline 6 & AA0101 & 33120 & 0 & & 0 PS & 01/01/1999 & 8:03:14 & 8:03:24 & 10 & 0:00:00 & 0:00:00 & 0 & AGENT & 8:03:23 & 8:05:10 & 107 & MICHAL \\ \hline 7 & AA0101 & 33121 & 0 & & 0 PS & 01/01/1999 & 8:18:42 & 8:18:51 & 9 & 0:00:00 & 0:00:00 & 0 & AGENT & 8:18:50 & 8:23:25 & 275 & KAZAV \\ \hline 8 & AA0101 & 33122 & 0 & & 0 PS & 01/01/1999 & 8:28:33 & 8:28:43 & 10 & 0:00:00 & 0:00:00 & 0 & AGENT & 8:28:42 & 8:30:24 & 102 & KAZAV \\ \hline 9 & AA0101 & 33123 & 68062744 & & 2 PS & 01/01/1999 & 8:42:13 & 8:42:19 & 6 & 8:42:19 & 8:42:23 & 4 & AGENT & 8:42:23 & 8:45:30 & 187 & KAZAV \\ \hline 10 & AA0101 & 33124 & 0 & & 0 PS & 01/01/1999 & 8:52:52 & 8:53:06 & 14 & 0:00:00 & 0:00:00 & 0 & AGENT & 8:53:05 & 8:54:38 & 93 & BASCH \\ \hline 11 & AA0101 & 33125 & 1466671 & & 2PS & 01/01/1999 & 9:04:04 & 9:04:10 & 6 & 9:04:10 & 9:04:56 & 46 & AGENT & 9:04:54 & 9:06:37 & 103 & VICKY \\ \hline 12 & AA0101 & 33126 & 0 & & 0 PS & 01/01/1999 & 9:16:49 & 9:17:20 & 31 & 0:00:00 & 0:00:00 & 0 & AGENT & 9:17:19 & 9:18:27 & 68 & YITZ \\ \hline 13 & AA0101 & 33127 & 23949225 & & 2 PS & 01/01/1999 & 9:27:03 & 9:27:09 & 6 & 9:27:09 & 9:28:00 & 51 & AGENT & 9:27:58 & 9:31:13 & 195 & SHARON \\ \hline 14 & AA0101 & 33128 & 59669259 & & 1PS & 01/01/1999 & 9:41:14 & 9:41:20 & 6 & 9:41:20 & 9:41:46 & 26 & AGENT & 9:41:45 & 9:42:19 & 34 & BASCH \\ \hline 15 & AA0101 & 33129 & 49921232 & & 2 PS & 01/01/1999 & 9:47:49 & 9:47:55 & 6 & 9:47:55 & 9:49:15 & 80 & AGENT & 9:49:14 & 9:51:48 & 154 & KAZAV \\ \hline 16 & AA0101 & 33130 & 0 & & 0PS & 01/01/1999 & 10:04:47 & 10:04:56 & 9 & 0:00:00 & 0:00:00 & 0 & AGENT & 10:04:56 & 10:05:49 & 53 & MORIAH \\ \hline 17 & AA0101 & 33131 & 0 & & 0 PS & 01/01/1999 & 10:23:44 & 10:23:54 & 10 & 0:00:00 & 0:00:00 & 0 & AGENT & 10:23:53 & 10:27:47 & 234 & VICKY \\ \hline 18 & AA0101 & 33132 & 0 & & 0PS & 01/01/1999 & 10:35:08 & 10:35:28 & 20 & 0:00:00 & 0:00:00 & 0 & AGENT & 10:35:26 & 10:36:19 & 53 & BASCH \\ \hline 19 & AA0101 & 33133 & 24290983 & & 2 PS & 01/01/1999 & 10:48:44 & 10:48:49 & 5 & 10:48:49 & 10:49:17 & 28 & AGENT & 10:49:17 & 11:01:22 & 725 & BASCH \\ \hline 20 & AA0101 & 33134 & 0 & & 0 PS & 01/01/1999 & 11:07:30 & 11:07:38 & 8 & 0:00:00 & 0:00:00 & 0 & AGENT & 11:07:43 & 11:07:46 & 3 & NO_SERVER \\ \hline 21 & AA0101 & 33135 & 0 & & 0 PS & 01/01/1999 & 11:23:55 & 11:24:04 & 9 & 0:00:00 & 0:00:00 & 0 & AGENT & 11:24:03 & 11:24:55 & 52 & YITZ \\ \hline 22 & AA0101 & 33136 & 50698984 & & 2 PS & 01/01/1999 & 11:35:18 & 11:35:23 & 5 & 11:35:23 & 11:36:15 & 52 & AGENT & 11:36:15 & 11:38:14 & 119 & BASCH \\ \hline 23 & AA0101 & 33137 & 0 & & 0 PS & 01/01/1999 & 11:49:27 & 11:49:38 & 11 & 0:00:00 & 0:00:00 & 0 & AGENT & 11:49:37 & 11:49:48 & 11 & VICKY \\ \hline 24 & AA0101 & 33138 & 0 & & 0PS & 01/01/1999 & 12:09:22 & 12:09:32 & 10 & 0:00:00 & 0:00:00 & 0 & AGENT & 12:09:31 & 12:10:58 & 87 & YITZ \\ \hline 25 & AA0101 & 33139 & 0 & & 0NW & 01/01/1999 & 12:25:30 & 12:25:43 & 13 & 0:00:00 & 0:00:00 & 0 & AGENT & 12:25:42 & 12:26:08 & 26 & YITZ \\ \hline 26 & AA0101 & 33140 & 0 & & 0 PS & 01/01/1999 & 12:49:43 & 12:49:56 & 13 & 0:00:00 & 0:00:00 & 0 & AGENT & 12:49:56 & 12:56:18 & 382 & VICKY \\ \hline 27 & AA0101 & 33141 & 27347905 & & 1PS & 01/01/1999 & 13:12:08 & 13:12:13 & 5 & 13:12:13 & 13:14:05 & 112 & HANG & 0:00:00 & 0:00:00 & 0 & NO_SERVER \\ \hline 28 & AA0101 & 33142 & 0 & & 0 PS & 01/01/1999 & 13:34:28 & 13:34:38 & 10 & 0:00:00 & 0:00:00 & 0 & AGENT & 13:34:37 & 13:37:22 & 165 & ;OHARI \\ \hline 29 & AA0101 & 33143 & 0 & & 0 PS & 01/01/1999 & 13:49:47 & 13:49:57 & 10 & 0:00:00 & 0:00:00 & 0 & AGENT & 13:49:56 & 13:52:43 & 167 & VICKY \\ \hline 30 & AA0101 & 33144 & 0 & & 0PS & 01/01/1999 & 14:07:17 & 14:07:34 & 17 & 0:00:00 & 0:00:00 & 0 & HANG & 0:00:00 & 0:00:00 & 0 & NO_SERVER \\ \hline 31 & AA0101 & 33146 & 0 & & 0PE & 01/01/1999 & 14:43:10 & 14:43:10 & 0 & 0:00:00 & 0:00:00 & 0 & HANG & 0:00:00 & 0:00:00 & 0 & NO_SERVER \\ \hline 32 & AA0101 & 33147 & 0 & & 0NW & 01/02/1999 & 10:22:39 & 10:23:17 & 38 & 0:00:00 & 0:00:00 & 0 & HANG & 0:00:00 & 0:00:00 & 0 & NO_SERVER \\ \hline 33 & AA0101 & 33148 & 0 & & 0 PS & 01/02/1999 & 19:09:10 & 19:09:19 & 9 & 0:00:00 & 0:00:00 & 0 & AGENT & 19:09:18 & 19:11:47 & 149 & YITZ \\ \hline 34 & AA0101 & 33149 & 0 & & 0 PS & 01/02/1999 & 19:28:05 & 19:28:13 & 8 & 0:00:00 & 0:00:00 & 0 & AGENT & 19:28:12 & 19:32:53 & 281 & MICHAL \\ \hline 35 & AA0101 & 33150 & 0 & & 0 PS & 01/02/1999 & 19:46:58 & 19:47:07 & 9 & 0:00:00 & 0:00:00 & 0 & AGENT & 19:47:06 & 19:49:54 & 168 & MICHAL \\ \hline 36 & AA0101 & 33151 & 19889088 & & 1PE & 01/02/1999 & 20:06:12 & 20:06:17 & 5 & 20:06:17 & 20:07:15 & 58 & HANG & 0:00:00 & 0:00:00 & 0 & NO_SERVER \\ \hline 37 & AA0101 & 33152 & 0 & & 0 PS & 01/02/1999 & 20:36:05 & 20:36:12 & 7 & 0:00:00 & 0:00:00 & 0 & AGENT & 20:36:12 & 20:37:27 & 75 & AVNI \\ \hline 38 & AA0101 & 33153 & 0 & & 0 PS & 01/02/1999 & 21:07:28 & 21:07:34 & 6 & 0:00:00 & 0:00:00 & 0 & AGENT & 21:07:36 & 21:07:40 & 4 & NO_SERVER \\ \hline 39 & AA0101 & 33154 & 0 & & 0 PS & 01/02/1999 & 21:30:13 & 21:30:22 & 9 & 0:00:00 & 0:00:00 & 0 & AGENT & 21:30:21 & 21:38:00 & 459 & AVNI \\ \hline 40 & AA0101 & 33155 & 0 & & 0 PS & 01/02/1999 & 22:00:57 & 22:01:07 & 10 & 0:00:00 & 0:00:00 & 0 & AGENT & 22:01:06 & 22:08:17 & 431 & ZOHARI \\ \hline & Data Dictio & january & 1 & y march & april ma & june july & august & ber & october & novembe & & nber + & & & & \\ \hline \end{tabular}