NT 5160, Fall 2015 Semester Individual Case Assignment: Cutting Edge This case was adapted from Hiller, Frederick S. & Mark S. Hillier (2014). Introduction to Management Science: A Modeling and Case Studies Approach with Spreadsheets, 5th ed., McGraw-Hill/Irwin, pp 429-432. Mark Lawrence has been pursuing a vision for more than two years. This pursuit began when he became frustrated in his role as director of Human Resources at Cutting Edge, a large company manufacturing computers and computer peripherals. At that time the Human Resources Department under his direction provided records and benefits administration to the 60,000 Cutting Edge employees throughout the United States, and 35 separate records and benefits administration centers existed across the country. Employees contact these records and benefits centers to obtain information about dental plans and stock options, change tax forms and personal information, and process leaves of absence and retirements. The decentralization of these administration centers caused numerous headaches for Mark. He had to deal with employee complaints often since each center interpreted company policies differently - communicating inconsistent and sometimes inaccurate answers to employees. His department also suffered high operating costs since operating 35 separate centers created inefficiency. His vision? To centralize records and benefits administration by establishing one administration center. This centralized records and benefits administration center would perform two distinct functions: data management and customer service. The data management function would include updating employee records after performance reviews and maintaining the human resource management system. The customer service function would include establishing a call center to answer employee questions concerning records and benefits and to process records and benefits changes over the phone. One year after proposing his vision to management, Mark received the go-ahead from Cutting Edge corporate headquarters. He prepared his \"to do\" list - specifying computer and phone systems requirements, installing hardware and software, integrating data from the 35 separate administration centers, standardizing record-keeping and response procedures, and staffing the administration center. Mark delegated the systems requirements, installation, and integration jobs to a competent group of technology specialists. He took on the responsibility of standardizing procedures and staffing the administration center. Mark had spent many years in human resources and therefore had little problem with standardizing record-keeping and response procedures. He encountered trouble in determining the number of 1 representatives needed to staff the center, however. He was particularly worried about staffing the call center since the representatives answering phones interact directly with customers - the 60,000 Cutting Edge employees. The customer service representatives would receive extensive training so that they would know the records and benefits policies backwards and forwards - enabling them to answer questions accurately and process changes efficiently. Overstaffing would cause Mark to suffer the high costs of training unneeded representatives and paying the surplus representatives the high salaries that go along with such an intense job. Understaffing would cause Mark to continue to suffer the headaches from customer complaints - something he definitely wanted to avoid. The number of customer service representatives Mark needed to hire depended on the number of calls that the records and benefits call center would receive. Mark therefore needed to forecast the number of calls that the new centralized center would receive. He approached the forecasting problem by using judgmental forecasting. He studied data from one of the 35 decentralized administration centers and learned that the decentralized center had serviced 15,000 customers and had received 2,000 calls per month. He concluded that since the new centralized center would service four times the number of customers - 60,000 customers - it would receive four times the number of calls - 8,000 calls per month. Mark slowly checked off the items on his \"to do\" list, and the centralized records and benefits center opened one year after Mark had received the go-ahead from corporate headquarters. Now, after operating the new center for 13 weeks, Mark's call center forecasts are proving to be terribly inaccurate. The number of calls the center receives is roughly three times as large as the 8,000 calls per month that Mark had forecasted. Because of demand overload, the call center is slowly going to hell in a handbasket. Customers calling the center must wait an average of five minutes before speaking to a representative, and Mark is receiving numerous complaints. At the same time, the customer service representatives are unhappy and on the verge of quitting because of the stress created by the demand overload. Even corporate headquarters has become aware of the staff and service inadequacies, and executives have been breathing down Mark's neck demanding improvements. Answer Questions 1a through 1c below: Question 1a: Define a problem statement which reflects the challenge facing Mark as he planned for the opening of the new center. 2 Question 1b: Why was Mark's initial forecast of call volume so far off? What could have been the reasons for this? Question 1c: What could Mark have done differently to improve his initial forecast? 3 Mark needed help, and he approached Harry, a corporate analyst, to forecast demand for the call center more accurately. Luckily, when Mark first established the call center, he realized the importance of keeping operational data, and he provided Harry with the number of calls received on each day of the week over the last 13 weeks. The data (refer to Cutting Edge Student File No. 1) begins in week 44 of the last year (2012) and continues to week 5 of the current year (2013). Mark indicates that the days where no calls were received were holidays. As a start, Harry used the data from the past 13 weeks and applied five different time-series forecasting methods in preparing a trial forecast of the call volume for each day of the upcoming week (Week 6). He provided a different forecast for each day of the week by treating the forecast for a single day as being the actual call volume on that day. From plotting the data, Harry could see that demand follows \"seasonal\" patterns within the week. For example, more employees call at the beginning of the week when they are fresh and productive than at the end of the week when they are planning for the weekend. Therefore, Mark prepared and used seasonally adjusted call volumes for the past 13 weeks. After Week 6 ended, Harry compared the five forecasts with the actual volumes and calculated the Mean Absolute Deviation (MAD) values for each method. The result of Harry's work is summarized below: Answer Questions 2a through 2e below: 4 Question 2: Describe the details of each forecasting method used by Harry and explain its accuracy (MAD value) in comparison with the accuracy of the other methods. (Hint: In answering this question, it is helpful to review a time-series plot of the 13 weeks of data.) 2a) Last Value 2b) Averaging 2c) Moving Average (5 days) 2d) Exponential Smoothing (alpha = 0.1) 2e) Exponential Smoothing (alpha = 0.5) 5 After many months of work and with Harry's help, Mark has been able to stabilize the call center operation. Mark now has a better handle on how to forecast the daily call demand and he is able to prepare effective weekly staffing schedules for handling the daily variation in volume. However, Mark is still experiencing difficulty in forecasting the volume from month to month. Cutting Edge has been very active in acquiring new companies while, at the same time, selling off portions of their existing business. Mark believes that this activity is causing fluctuations in call volume because it is affecting the employee head count of Cutting Edge. Mark has assembled monthly data for call volume and head count for the past 18 months (refer to Cutting Edge Student File No. 2). Mark also suspects that there are other factors which may be affecting the call volume, and he has noted these factors on the attached spreadsheet. Based on the upcoming acquisition of Cutter Corp on 7/1/2015, the forecast of head count for July 2015 is 77,000. Answer Questions 3a through 3d below: Question 3a: Prepare a forecast of call volume for July 2015 by applying Exponential Smoothing (with alpha = 0.5) to the prior 18 months of data. Use the appropriate Excel template from the Hillier text to prepare your forecast and assume that initial call volume is 24,000. Show your forecast below and attach the completed template. Call Volume Forecast for July 2015 (Exponential Smoothing, alpha=0.5): _________________ Question 3b: Apply Linear Regression to predict call volume from head count using the appropriate Excel template. Show your forecast below and attach the completed Excel template. Call Volume Forecast for July 2015 (Causal Forecasting based on head count): _________________ Question 3c: Calculate the Mean absolute deviation value of the Exponential Smoothing model (Question 3a) and the Average Estimation Error of the Linear Regression model (Question 3b). Explain the difference between these two values. Mean absolute deviation of Exponential Smoothing model, alpha=0.5: ______________________ Average Estimation Error for Causal Forecasting model based on headcount: __________________ Explanation of the difference in values: 6 Question 3d: Considering your answers to Questions 3a, 3b and 3c and all the factors that have been described above, prepare your best forecast for July 2015. Show your forecast value below and explain and justify how you came up with this forecast. Call Volume Forecast for July 2015 (My forecast): _________________ Explanation and Justification of Your Method: 7 Cutting Edge Individual Case Assignment, QNT 5160, Fall 2015 Semester File No. 1 (Daily data for 2012 and 2013) Week 44 44 44 44 44 45 45 45 45 45 46 46 46 46 46 47 47 47 47 47 48 48 48 48 48 49 49 49 49 49 50 50 50 50 50 51 51 51 51 51 52/1 52/1 52/1 52/1 52/1 2 2 2 2 2 3 3 3 3 3 4 4 4 4 4 5 5 5 5 5 6 6 6 6 6 Day Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri Actual Call Volume 1,130 750 920 854 698 1,085 1,012 689 920 755 1,403 1,121 1,050 1,113 1,005 2,652 2,825 1,841 2,012 1,345 954 1,022 1,084 1,321 1,056 941 760 695 1,012 833 922 810 784 789 401 429 1,209 789 1,132 890 1,362 1,210 980 950 834 1,012 954 1,346 904 758 923 878 798 1,012 643 945 689 723 754 798 723 698 534 578 487 Cutting Edge Individual Case Assignment, QNT 5160, Fall 2015 Semester File No. 2 (Monthly Data for 2014 and 2015) Year 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2014 2015 2015 2015 2015 2015 2015 Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Actual Call Volume 24,015 25,203 23,589 27,454 28,120 28,321 29,021 26,954 26,456 27,120 26,954 27,321 26,456 27,450 31,435 33,124 32,432 31,901 Employee Head Count Notes 62,120 62,152 62,138 Centex corporation acquired 4/1/2014 68,343 68,120 Dental insurance plan changed effective 7/1/2014 67,987 67,956 Printer division sold to Arconet Corporation 8/1/2014 65,342 65,380 Major tax law changes signed into law by U.S. President 65,432 65,423 Year-end bonuses announced on 12/10/2014 65,650 65,620 65,610 Paxton Enterprises acquired 3/15/2015 75,231 75,201 74,978 New employee insurance deductions in effect starting 7/1/2015 75,012 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 B C D E F G H I J K L M Template for Exponential Smoothing Forecasting Method Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 True Value 24,015 25,203 23,589 27,454 28,120 28,321 29,021 26,954 26,456 27,120 26,954 27,321 26,456 27,450 31,435 33,124 32,432 31,901 Exponential Smoothing Forecast 7,500 15,758 20,480 22,035 24,744 26,432 27,377 28,199 27,576 27,016 27,068 27,011 27,166 26,811 27,131 29,283 31,203 31,818 31,859 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Forecast for July 2015 = 31,859 Forecasting Error 16,515 9,446 3,109 5,419 3,376 1,889 1,644 1,245 1,120 104 114 310 710 639 4,304 3,841 1,229 83 Smoothing Constant a = Initial Estimate Average = 0.5 7,500 Mean Absolute Deviation MAD = 3,061 Range Name Alpha Forecast ForecastingError InitialEstimate MAD MSE TrueValue Cells H6 D6:D35 E6:E35 H9 H12 H15 C6:C35 Mean Square Error MSE = 25,405,620 35,000 30,000 25,000 20,000 Value True Value 15,000 Forecast 10,000 5,000 0 Time Period N A 1 B C D E F G H I J K L M N O Template for Linear Regression 2 Forecast for July 2015 = 33,446 3 Average Estimator Error = 525 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Independent Variable 62,120 62,152 62,138 68,343 68,120 67,987 67,956 65,342 65,380 65,432 65,423 65,650 65,620 65,610 75,231 75,201 74,978 75,012 Dependent Variable 24,015 25,203 23,589 27,454 28,120 28,321 29,021 26,954 26,456 27,120 26,954 27,321 26,456 27,450 31,435 33,124 32,432 31,901 Estimate 24,720 24,738 24,730 28,369 28,238 28,160 28,142 26,609 26,631 26,662 26,657 26,790 26,772 26,766 32,409 32,391 32,260 32,280 Estimation Error 704.70 464.53 1141.26 915.12 118.35 160.65 878.83 344.79 175.50 458.01 297.29 531.16 316.24 683.62 973.53 733.07 171.84 379.10 Average = Square of Error 496,602 215,792 1,302,464 837,448 14,006 25,809 772,343 118,878 30,799 209,771 88,379 282,135 100,010 467,338 947,756 537,385 29,530 143,715 Linear Regression Line y = a + bx a= -11,710.02 b= 0.59 524.87 Estimator If x = 77,000 then y= Range Name a b DependentVariable Estimate EstimationError IndependentVariable SquareOfError x y 33,445.94 Cells J5 J6 D5:D34 E5:E34 F5:F34 C5:C34 G5:G34 J10 J12 35,000 30,000 f(x) = 0.5864410105x - 11710.015904676 Dependent Variable 25,000 20,000 15,000 10,000 5,000 0 60,000 Independent Variable 62,000 64,000 66,000 68,000 70,000 72,000 74,000 76,000 P 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 6,000 40 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 B C D E F G H I J K L M Template for Exponential Smoothing Forecasting Method Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 True Value 24,015 25,203 23,589 27,454 28,120 28,321 29,021 26,954 26,456 27,120 26,954 27,321 26,456 27,450 31,435 33,124 32,432 31,901 Exponential Smoothing Forecast 7,500 15,758 20,480 22,035 24,744 26,432 27,377 28,199 27,576 27,016 27,068 27,011 27,166 26,811 27,131 29,283 31,203 31,818 31,859 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Forecast for July 2015 = 31,859 Forecasting Error 16,515 9,446 3,109 5,419 3,376 1,889 1,644 1,245 1,120 104 114 310 710 639 4,304 3,841 1,229 83 Smoothing Constant a = Initial Estimate Average = 0.5 7,500 Mean Absolute Deviation MAD = 3,061 Range Name Alpha Forecast ForecastingError InitialEstimate MAD MSE TrueValue Cells H6 D6:D35 E6:E35 H9 H12 H15 C6:C35 Mean Square Error MSE = 25,405,620 35,000 30,000 25,000 20,000 Value True Value 15,000 Forecast 10,000 5,000 0 Time Period N A 1 B C D E F G H I J K L M N O Template for Linear Regression 2 Forecast for July 2015 = 33,446 3 Average Estimator Error = 525 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Time Period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Independent Variable 62,120 62,152 62,138 68,343 68,120 67,987 67,956 65,342 65,380 65,432 65,423 65,650 65,620 65,610 75,231 75,201 74,978 75,012 Dependent Variable 24,015 25,203 23,589 27,454 28,120 28,321 29,021 26,954 26,456 27,120 26,954 27,321 26,456 27,450 31,435 33,124 32,432 31,901 Estimate 24,720 24,738 24,730 28,369 28,238 28,160 28,142 26,609 26,631 26,662 26,657 26,790 26,772 26,766 32,409 32,391 32,260 32,280 Estimation Error 704.70 464.53 1141.26 915.12 118.35 160.65 878.83 344.79 175.50 458.01 297.29 531.16 316.24 683.62 973.53 733.07 171.84 379.10 Average = Square of Error 496,602 215,792 1,302,464 837,448 14,006 25,809 772,343 118,878 30,799 209,771 88,379 282,135 100,010 467,338 947,756 537,385 29,530 143,715 Linear Regression Line y = a + bx a= -11,710.02 b= 0.59 524.87 Estimator If x = 77,000 then y= Range Name a b DependentVariable Estimate EstimationError IndependentVariable SquareOfError x y 33,445.94 Cells J5 J6 D5:D34 E5:E34 F5:F34 C5:C34 G5:G34 J10 J12 35,000 30,000 f(x) = 0.5864410105x - 11710.015904676 Dependent Variable 25,000 20,000 15,000 10,000 5,000 0 60,000 Independent Variable 62,000 64,000 66,000 68,000 70,000 72,000 74,000 76,000 P 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 6,000 40