Question 1a (5 points): Define a problem statement which reflects the challenge facing Mark as he planned for the opening of the new center.
Answer:
(i) With the consolidation of 35 separate Records & Benefits Administration Centers into one Administrative Center, Mark Lawrence is having trouble in determining the appropriate number of representatives needed to staff the center to service the 60,000 employees at Cutting Edge. If Mark employs too many representatives, he will suffer high costs of training and salaries for the unnecessary representatives. If Mark employs too few representatives, he will have to deal with customer complaints.
(ii) With the consolidation of 35 separate Records & Benefits Administration Centers into one Administrative Center, Mark Lawrence is experiencing a number of issues due to understaffing the Center with representatives to service the 60,000 employees at Cutting Edge. Unexpectedly high call volume has customers waiting an average of five minutes before speaking with a representative, leading to numerous complaints among customers, disgruntled representatives having to deal with the frustrated customers, and unhappy executives from headquarters who are demanding improvements from Mark.
(iii) Mark needs to adequately staff one administration center responsible for performing two separate functions, data management and customer service. The customer service function should be capable of servicing 60,000 Cutting Edge employees. Overstaffing would result in unwanted high costs while understaffing would result in customer complaints.
Question 1b (5 points): Why was Mark’s initial approach to forecasting call volume so far off? What could have been the reasons for this?
Answer:
(i) Mark’s initial approach to forecasting call volume may have been so far off because he used a single call center to base his forecast on. The issue with this approach being that the call center he used very likely could have been an outlier in call volume and inefficiency. The issue facing Mark is that he is using 1 of 35 call centers which is 2.85 percent of the available data that could have been collected and analyzed if he had taken all 35 into account.
(ii) Mark relied primarily on judgmental forecasting. He sampled only one out of the thirty-five decentralized administrative centers to form a conclusion. His reason for doing this could most likely be attributed to his overconfidence based on his role as Director of Human Resources along with his years of experience in human resources.
Question 1c (5 points): What could Mark have done differently to improve his initial forecast?
Answer:
Mark could have used a larger sample size instead of just looking at only one of the 35 decentralized Administration Centers, as well as used different methods of forecasting instead of his form of simple judgemental forecasting. In hindsight, he should have taken more time in collecting the right amount of data, spent a larger time in the Model Selection process, and then based his decision holistically from the forecasted results.

Part 2 (60 points)

In answering Part 2 questions, download and reference “Cutting Edge Student File No 1.xlsx” which contains the data and models to be used in preparing the forecast results for your answers. You will need to review the time-series plot of the 13 weeks of data in the DailyData tab. For each model, copy the data for all 14 weeks into the appropriate method column. Each method will auto-calculate giving you information to analyze and interpret. Note, in answering these questions you will need to reference lecture charts for method descriptions and utilized the Excel models forecasts for MAD values.

Question 2a (10 points): Calculate the Last Value method forecast using the appropriate Excel model. In your own words, describe the details of the Last Value method and explain its accuracy (MAD value) in comparison with the accuracy of the other methods.
Method Description Answer:
The Last Value method uses the last value period to forecast for future periods. This method essentially ignores all previous time periods that have been recorded, except for the last time period in the time series. In comparison with other methods, statisticians typically find this method to be simple-minded to only use one sample size to project future time periods, and often call this the naive method. The problem with using this method is the limitations of historical data and randomness it introduces into the problem.
MAD Comparison Answer: 172

Question 2b (10 points): Calculate the Averaging method forecast using the appropriate Excel model. In your own words, describe the details of the Averaging method and explain its accuracy (MAD value) in comparison with the accuracy of the other methods.
Method Description Answer:
The Averaging method adds all the value points and divides by the number of value points. This method is good when conditions are stable, but it can be slow due to changing conditions. The older value points in the series could be less of a representation of the overall data points as a whole, but it does place the same weight on the data as a whole.
MAD Comparison Answer: 294
Question 2c (10 points): Calculate the Moving Average method forecast using the appropriate Excel model. In your own words, describe the details of the Moving Average (5 days) method and explain its accuracy (MAD value) in comparison with the accuracy of the other methods.
Method Description Answer:
The Moving Average method averages only the data values for the most recent time periods. This method sits between the Last Value and Averaging methods. The Moving Average method is good to use when conditions don’t change much over a time period, but like the Averaging method, the Moving Average method is also slow to respond to changing conditions.
MAD Comparison Answer: 225

Question 2d (15 points): Calculate the Exponential method forecast using the appropriate Excel model. In your own words, describe the details of the Exponential Smoothing (alpha = 0.1) method and explain its accuracy (MAD value) in comparison with the accuracy of the other methods.
Method Description Answer:
The Exponential Smoothing method is a step up from the Moving Average method, because it provides the greatest weight to the most recent value, but smaller weights to older data values. This method also uses weighted averages from previous time values to forecast the new time values. According to betterprogramming.pub, Exponential Smoothing Methods typically combine the error, trend,and seasonal components into the calculation, better known as ETS framework.
Encora. (2019, August 28). Exponential smoothing methods for time series forecasting. Medium. Retrieved October 6, 2022, from https://betterprogramming.pub/exponential-smoothing-methods-for-time-series-forecasting-d571005cdf80
MAD Comparison Answer: 250

Question 2e (15 points): Calculate the Exponential method forecast using the appropriate Excel model. Describe the Exponential Smoothing (alpha = 0.7) difference in accuracy (MAD value) in comparison with the accuracy of the answer in 2d.
Method Description Answer: The Exponential Smoothing method of alpha = 0.7’s MAD answer is different from the MAD answer of alpha = 0.3. The difference in MAD answers is due to the difference in alpha values. A small value suggests conditions are relatively stable, whereas a larger value is used if significant changes occur frequently. The alpha value is selected by trial and error, but is based on the stability of the data via the forecasting accuracy.
MAD Comparison Answer: 167

Part 3 (25 points)

In addressing Part 3a reference “Cutting Edge Student File No. 1.xlsx” which contains the data and completed models that were used in part 2 in preparing the forecast results and model accuracy. You will need to analyze and interpret the Excel forecasts from part 2 – no additional calculations are needed.

Question 3a (25 points): Based on the answers above, complete the forecast vs. actual daily call volume table found in the Excel file Summary Values tab and insert the table below. Then, provide your final week 14 forecast and recommendations to the company on daily call volume forecasting to improve the scheduling of the call center staff. You must recommend and justify a forecasting model as a part of your answer.
Methods Summary Values Table (3 points):
***********Still needs to be completed, I just copied the table over to make it easier
Cutting Edge Forecast vs. Actual Daily Call Volume

Day Actual Call Volume Last Value Averaging Moving Average Exponential Smoothing (Alpha =0.1) Exponential Smoothing (Alpha =0.7)
Mon 723 1,135 1,269 957 1,066 1,082
Tue 677 726
Wed 521 623
Thur 571 606
Fri 498 553
Mean Absolute Deviation (MAD) N/A 172 294 225 250 167

Model Selected and Justification (5 points):
The model selected is the Exponential Smoothing method. This method is the most accurate for the call center that Mark is looking to hire professionals for, because of the everchanging call volumes incurred. Mark started off using judgemental forecasting, which essentially was him taking the average number of calls from a decentralized center, and doubling that number to forecast future call volumes. With the Exponential Smoothing method, it is based on the data via the forecasting accuracy, and changes due to the weighted averages of previous values.

Final Selected Forecast Answer for Each Day (2 points):

Your recommendations for the company to include at least one original graphic (15 points):