The images below are the data in the excel workbook

WW $ Bob retreated to his office and closed the door. As he sat in his chair, he thought about the many meetings he had participated in where managers promised great customer service but could not deliver it. Upon further reflection, he came to the conclusion that to promise great customer service you first must know how to analyze resource capacity and develop good schedules. He pulled out his old college operations management textbook and began reading. He also did a Google search on the topic and found several articles to read. He planned on building an electronic spreadsheet analysis of the situation. Small and mid-sized airlines outsourced a variety of peripheral services to GCCS, such as billing and credit card management, baggage and customer flight claims management, reservations, loyalty programs, and call center management. HA accounted for 9 percent of GCCS revenues. HA customers had no idea the HA frequent flyer program was outsourced to GCCS. GCCS managed separate call centers for several airlines, each with its own dedicated staff and office space. GCCS customer service representatives (CSRs) who worked in the HA call center were trained by both GCCS and individual airlines such as HA. GCCS provided all CSRs with service management training and mentors, and CSR performance was electronically monitored. HA trained the CSRs by including airline tours and free flights so the CSRs would know the airline and its culture, and especially its frequent flyer program. HA CSRs were trained to handle 20 service upsets most likely to be described by incoming customer calls. HA and GCCS categorized incoming calls into four categories as follows: 1. Redeem calls: The customer wants to redeem frequent flyer points for future airline flights. 2. Problem resolution: The customer wants to correct point debits and credits, flights, personal information, and so on. 3. Manage accounts: The customer wants to split, combine, transfer, delete, rename, and/or update the frequent flyer account(s). por Call Calls 125 63% 26% 170 4. Travel advice: The customer asks for travel advice. GCCS and HA provide "limited travel consulting service and advice. GCCS CSRs are trained to be nice yet tell the customer they do not provide full travel service, and refer customers to other travel agencies. Average standard times for HA's call mix is shown below. Standard Timo Porcentage of Type of Call Total Number of (seconds) Redeem Problem Resolution Manage Account Travel Advice Bob wanted to get a standard service rate in the same units of measure used for other airlines, and that was calls per CSR per 30 minutes. Average HA-GCCS call center demand rounded to the nearest integer for the last 10 Mondays is shown in this Workbook. Bob thought a planned (target) CSR labor utilization of 90 percent provided adequate safety capacity Full-time employee (FTE) policies require at least a 7-hour workday plus one hour for lunch or dinner and at least one 15-minute break per workday. Any workday less than FTE is considered part-time employment (PTE). 240 7% 170 4% Mr. Barthrow has retained your services to prepare a consulting report answering the questions below: 1. What's labor utilization for each 30-minute period given the current staffing plan? 2. Is the main problem lack of staff capacity or poor scheduling, or both? 3. How many CSRs are required to meet demand during a regular work day? 4. What are your recommended next steps? CONSULTING Include the Excel file showing all calculations. This short consulting report (no more than three pages in length) must briefly (i) describe the situation faced by Mr. Barthrow, (ii) list key assumptions underlying the analysis, and (ii) provide answers to the questions above, along with (iv) your proressional recommendations. Note that this is not an academic paper but rather a business document to be sidsread by Mr. Barthrow and his team. The use of exhibits (graphs, tables, etc.) to illustrate points is recommended. 2009 Call Duration Current Staffing Staffing Plan Template Type of Call Standard Time per Call (seconds) Percentage of Total Number of Calls Redeem Problem Resolution Manage Account Travel Advice 125 170 240 170 63% 26% 7% 4% Call Duration Current Staffing Staffing Plan Template 7:00 7:30 8:00 8:30 9:00 9:30 10:00 11:30 12:00 12:30 PM 1:00 1:30 2:00 Service Rep 1 2 3 6:30 X- X 10:30 (Lunch) 11:00 (Lunch) (Lunch) (Lunch) (Lunch) (Lunch) (Lunch) 4 5 6 7 8 (Lunch) (Lunch) (Lunch) (Lunch) (Lunch) (Lunch) (--------- --------- (Lunch) (Lunch) (Lunch) (Lunch) (Lunch) (--------- 9 10 11 12 13 Avail CSR (Min) Current # CSRS 60 150 180 210 180 180 180 180 60 2 90 3 150 5 150 5 150 5 210 7 150 5 135 4.5 2 5 6 7 6 6 6 6 Call Duration Current Staffing Staffing Plan Template 00 1:30 2:00 2:30 3:00 3:30 4:00 4:30 5:00 5:30 6:00 6:30 7:00 7:30 8:00 8:30 9:00 Totals (-------- nch) nch) nch) (Lunch) (Lunch) (Lunch) (Lunch) (Lunch) (Dinner) (Dinner) (Dinner) (Dinner) --------- --------- ----------- (Dinner) (Dinner) 150 5 (Dinner) (Dinner) 120 4 180 150 90 120 50 5 120 ----------- 135 4.5 135 4.5 315 10.5 330 11 300 10 210 7 210 7 120 4 120 4 4,905.00 164 6 5 3 4 4