Hello, I'm a bit at lost with this individual assignment, if you can please assist with the excel sheet calculations as to answer some of this questions I may be able to visualize with the formulas where the answer are coming from. I would appreciate any help. Thanks for including the excel file attached for my records to future studies.

Thanks again.

Instructions

In order to complete the assignment, first read the case write-up for the "Cutting Edge" case.Then, answer the questions listed below for each part of the case.The Part 1 questions refer to the 2 years leading up to the opening of the new call center.Part 2 questions refer to the first 13 weeks of operation after opening the call center.Part 3 questions refer to the first 18 months of operating the call center.

Conduct necessary calculations and visualizations to answer the questions.Submit your Excel spreadsheet(s) with calculations/ visualizations to the assignment drop box before the posted deadline.

Prepare a report, which includes your answers to the assignment questions.Your answers must be entered directly into this Word document below each question.Insert each answer below each question on this document and use as much space as needed.

Part 1 (15 points):

Question 1a (5 points): Define a problem statement which reflects the challenge facing Mark as he planned for the opening of the new center.

Question 1b (5 points): Why was Mark's initial forecast of call volume so far off?What could have been the reasons for this?

Question 1c (5 points): What could Mark have done differently to improve his initial forecast?

Part 2 (25 points):

In answering the Part 2 questions, you should download and refer to Student Data File No. 1 which contains the historical data that was used in preparing the forecast results that are reported in Part 2 of the case write-up document.Note that you do not have to prepare any forecasts in answering this question.Hint: it will be helpful for you to review a time-series plot of the 13 weeks of data contained on Student Data File No. 1.

Question 2a (4 points): Describe the details of the Last Value method used by Harry and explain its accuracy (MAD value) in comparison with the accuracy of the other methods.

Question 2b (4 points): Describe the details of the Averaging method used by Harry and explain its accuracy (MAD value) in comparison with the accuracy of the other methods.

Question 2c (4points): Describe the details of the Moving Average (5 days) method used by Harry and explain its accuracy (MAD value) in comparison with the accuracy of the other methods.

Question 2d (4points): Describe the details of the Exponential Smoothing (alpha = 0.1) method used by Harry and explain its accuracy (MAD value) in comparison with the accuracy of the other methods.

Question 2e (4 points): Describe the details of the Exponential Smoothing (alpha = 0.7) method used by Harry and explain its accuracy (MAD value) in comparison with the accuracy of the other methods.

Question 2f (5 points): Based on the analysis above, provide your recommendations to Mark on daily call volume forecasting to improve the scheduling of the call enter staff. You must recommend a forecasting model as a part of your answer.

Part 3 (50 points):

In answering the Part 3 questions, you should download and refer to Cutting Edge Student Data File No. 2 which contains the historical data and forecasting model templates that you will need to answer the questions.

Question 3a (10 points):

Prepare 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 (Exponential_Smoothing (without seasonality. xlsx)to make your forecast and assume that initial call volume is 24,000.Show your forecast below and attach the completed Excel template.

Call Volume Forecast for July 2015 (Exponential Smoothing, alpha=0.5): _________________

Question 3b (10 points):

Apply Linear Regression to predict call volume from head count using the appropriate Excel template (Linerar_Regression Model.xlsx). Based on the upcoming acquisition of Cutter Corp on 7/1/2015, the forecast of head count for July 2015 is 77,000 (Estimator x value). Show your forecast below and attach the completed Excel template.

Call Volume Forecast for July 2015 (Causal Forecasting based on head count): _________________

Question 3c (10 points):

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).You will need to employ the Excel AVERAGE function for the Linear Regression Average Estimation Error (Cell: F36). Explain the difference between the MAD and Agv Est Error 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:

Question 3d (20 points):

Considering your answers to Questions 3a, 3b and 3c and all the factors that have been described above, make your best forecast for July 2015.Show your forecast value below and explain and justify how you came up with this forecast.Finally, provide your recommendations to Mark on how to modify forecasting process and improve its accuracy.

Call Volume Forecast for July 2015 (My forecast): _________________

Explanation and Justification of Your Method:

Your Recommendations:

Cutting Edge Case Study Additional Data:

Cutting Edge

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 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.

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:

Cutting Edge

Week 6 Forecast vs. Actual Daily Call Volume

\f\f\f\f\f\f\f\f\f\f