1 Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Forecasts MA(3) = MA(5) = MAE MA(3) MA(5) Part Grader b c Orders MA(3) MA(5) Delivered Forecast Forecast 120 90 100 75 110 50 75 130 110 90 Value Value Part d e Grader Part h i Grader f g MA(3) Error MA(5) Error Part Grader Is the time pattern stationary? (Yes or No) StatTools MA(3) MA(5) Confirm Best? Best? Part j a Grader Part k.1. k.2. k.3. Part l Grader Grader alpha = Question 2 Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Forecast = 0.20 Part a c Grader Orders ES Delivered Forecast ES Error 120 90 100 75 110 50 75 130 110 90 Value Part b Grader Value Part d Grader Part e.1. e.2. Grader Part f Grader MAPE StatTools ES(alpha=0.2) Optimal ES Metric alpha MAPE Question 3 Time 1 2 3 4 5 6 7 8 9 10 11 12 Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec StatTools ES (Simple) ES (Holt's) Analysis Demand 37 40 41 37 45 50 43 47 56 52 55 54 Part b c d Grader ANALYSIS Part a Grader Is the time pattern stationary? (Yes or No) 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 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 True Value 1,130 851 859 828 726 1,085 1,042 892 840 799 1,303 1,121 1,003 1,113 1,005 2,652 2,825 1,841 0 0 1,949 1,507 989 990 1,084 1,260 1,134 941 847 714 1,002 847 922 842 784 823 0 0 401 429 1,209 830 0 1,082 841 1,362 1,174 967 930 853 924 Seasonally Adjusted Call Volume Part b Part a Day Mon Tue Wed Thur Fri Estimate for Seasonal Factor 3 3 3 3 4 4 4 4 4 5 5 5 5 5 Tue Wed Thur Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri 954 1,346 904 758 886 878 802 945 610 910 754 705 729 772 Part c 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 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 True Value 1,130 851 859 828 726 1,085 1,042 892 840 799 1,303 1,121 1,003 1,113 1,005 2,652 2,825 1,841 0 0 1,949 1,507 989 990 1,084 1,260 1,134 941 847 714 1,002 847 922 842 784 823 0 0 401 429 1,209 830 0 1,082 841 1,362 1,174 967 930 853 924 954 1,346 904 Seasonally Adjusted Call Volume Seasonally Adjusted Forecast MA(5) Final Forecast w/ Seasonal Factor 3 4 4 4 4 4 5 5 5 5 5 Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri 758 886 878 802 945 610 910 754 705 729 772 Forecasting Error Mean Absolute Deviation MAD = Mean Square Error MSE = Estimate for Seasonal Factor Day Mon Tue Wed Thur Fri Part d 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 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 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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 True Value 1,130 851 859 828 726 1,085 1,042 892 840 799 1,303 1,121 1,003 1,113 1,005 2,652 2,825 1,841 1,113 1,005 1,949 1,507 989 990 1,084 1,260 1,134 941 847 714 1,002 847 922 842 784 823 847 922 401 429 1,209 830 922 1,082 841 1,362 1,174 967 930 853 924 954 1,346 904 Seasonally Adjusted Call Volume Seasonally Adjusted Forecast Expo Smoothing 3 4 4 4 4 4 5 5 5 5 5 Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri 55 56 57 58 59 60 61 62 63 64 65 758 886 878 802 945 610 910 754 705 729 772 Final Forecast w/ Seasonal Factor Forecasting Error alpha 0.9 Mean Absolute Deviation MAD = Mean Square Error MSE = Estimate for Seasonal Factor Day Mon Tue Wed Thur Fri Part e Parts f and g 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 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 True Value 1,130 851 859 828 726 1,085 1,042 892 840 799 1,303 1,121 1,003 1,113 1,005 2,652 2,825 1,841 0 0 1,949 1,507 989 990 1,084 1,260 1,134 941 847 714 1,002 847 922 842 784 823 0 0 401 429 1,209 830 0 1,082 841 1,362 1,174 967 930 853 924 954 1,346 904 Seasonally Adjusted Call Volume Seasonally Adjusted Forecast Your Model 3 4 4 4 4 4 5 5 5 5 5 Fri Mon Tue Wed Thur Fri Mon Tue Wed Thur Fri 758 886 878 802 945 610 910 754 705 729 772 Final Forecast w/ Seasonal Factor Forecasting Error Mean Absolute Deviation MAD = Mean Square Error MSE = Estimate for Seasonal Factor Day Mon Tue Wed Thur Fri Describe your model here: QUESTION 3 (15 points) PM Computer Services assembles customized personal computers from generic parts. The company was formed and is operated by two part-time university students, Paul and Bryan. The company has experienced steady growth since started. The computer parts are bought using volume discounts when good deals can be found. As such it is important that they develop a good model to forecast demand for their computers so that they will know how many computer components parts to purchase and stock. The company has accumulated computer demand data over a 12-month period. The data are shown in the worksheet named PM. a. 3 Points: Using the Excel Insert Tab Line Graph and follow-up Chart Tools Tabs, construct an appropriate line chart displaying the time pattern of computer demand data. Please create a professional appearing chart with labeling and titles. A legend is probably not needed. Move the chart so that it starts in cell J4 and fits within the red shaded area. Is the time pattern stationary? Yes or No in cell M3. b. 4 Points: Using StatTools, create the Optimal Parameters Exponential Smoothing (Simple) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell A1 of the StatTools PM Worksheet. c. 4 Points: Using StatTools, create the Optimal Parameters Exponential Smoothing (Holt's) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell G1 of the StatTools PM Worksheet. d. 4 Points: In the blue area designated Analysis, provide a managerial analysis for Paul and Bryan incorporating any patterns you have identified, recommending a forecasting method (with reasons), and providing your best forecast for the upcoming month. QUESTION 4 (36 points) 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. 35 separate records and benefits administration centers existed across the country. Employees contacted 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" listspecifying 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 4 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 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 headache, 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 administration 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. 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. Mark needs help, and he approaches your team 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 provides you with the number of calls received on each day of the week over the last 13 weeks. The data (worksheet Staffing) begins in week 44 of the last year and continues thru week 5 of the current year. From working at the records and benefits administration center, you know that demand follows \"seasonal\" patterns within the week. More employees seem to call at the beginning of the week than at the end. Mark indicates that the days where no calls were received were holidays. a. 6 points: Mark first asks your team to calculate a seasonal factor for Monday, Tuesday, Wednesday, Thursday, and Friday. Place your answers in Cells G4:G8. b. 4 6 points: Provide seasonally adjusted call volume for the past 13 weeks in Column D. c. 10 12 points: Using the seasonally adjusted call volume, forecast the daily demand with MA(5) model and then apply seasonal factors to obtain the final forecast. Compute MAD and MSE. Place your answers in worksheet StaffingMA(5). d. 10 12 points: Using the seasonally adjusted call volume, forecast the daily demand with simple exponential smoothing model (alpha = 0.9) and then apply seasonal factors to obtain the final forecast. Compute MAD and MSE. Place your answers in worksheet StaffingExpoSmoothing.5 e. 6 points: Apply Winter's model directly to the source data. Place your model in worksheet StaffingWinter. This question is omitted and points are redistributed. StatTools doesn't support Winters' model with daily seasonality. This actually shows the importance of understanding the underlying model instead of relying solely on Software. f. 5 bonus points: Can you find a forecasting model that will result in a smaller MAD or MSE value than those reported in parts c and d and e? If so, place your model in worksheet StaffingYourModel. g. 10 bonus points: The team has the smallest MAD or MSE value from part f earns an additional 10 bonus points