M4 Group Assignment Instructions Complete the Assignment, name it as GroupXX_Assign4.xls (where XX is your Group Name), and upload and submit to the instructor through Dropbox. Do not enter anything in the spreadsheet cells that are black, labeled \"Grader\". You must complete this assignment without the assistance of persons other than the members of your Group. You may use any other resources you deem necessary. Answer the questions below by placing the appropriate graph and/or answers in the designated cells of the spreadsheet. DO NOT CHANGE THE APPEARANCE OR FUNCTIONALITY OF THE SPREADSHEET UNLESS INSTRUCTED TO DO SO. Information for Questions 1-2: The Instant paper Clip Company sells and delivers office supplies to various companies, schools, and agencies within a 30-mile radius of its warehouse. The office supply business is extremely competitive, and the ability to deliver orders promptly is an important factor in customer relations. The manager of the company wants to be certain that enough drivers and delivery vehicles are available so that orders can be delivered promptly. Therefore, the manager wants to be able to forecast the number of orders that will occur during the next month (i.e., to forecast the demand for deliveries). From records of delivery orders the manager has accumulated data for the past 10 months. These data are shown in the worksheets named MA and ES. The manager wants your help in fitting different models (Moving Average and Exponential Smoothing) to help forecast demand for deliveries. QUESTION 1: Moving Average Models - Worksheet MA a. 6 Points: Using the Excel Insert Tab Line Graph and follow-up Chart Tools Tabs, construct an appropriate line chart displaying the time pattern of order deliveries. 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 H4 and fits within the red shaded area. Is the time pattern stationary? Yes or No in cell K3. b. 4 Points: In column C (starting in the appropriate cell), write a formula using a built-in Excel function to forecast demand through October using an MA(3) model. c. 4 Points: In column D (starting in the appropriate cell), write a formula using a built-in Excel function to forecast demand through October using an MA(5) model. d. 2 Points: In cell B17, write a formula using a built-in Excel function to forecast demand for November using the MA(3) model you constructed. e. 2 Points: In cell B18, write a formula using a built-in Excel function to forecast demand for November using the MA(5) model you constructed. f. 4 Points: In column E, under the label \"MA(3) Error,\" write a formula in the appropriate cells calculating the error terms necessary to calculate the MAE for the MA(3) model. Do not construct a column of errors, then another column of absolute, squared, or absolute relative errors. Construct only one set of errors using the appropriate formula to make the errors absolute, squared, or absolute relative (whichever is appropriate for MAE). 1 g. h. i. j. k. l. 4 Point: In column F, under the label \"MA(5) Error,\" write a formula in the appropriate cells calculating the error terms necessary to calculate the MAE for the MA(5) model. Do not construct a column of errors, then another column of absolute, squared, or absolute relative errors. Construct only one set of errors using the appropriate formula to make the errors absolute, squared, or absolute relative (whichever is appropriate for MAE). 2 Points: In cell B22, write a formula using a built-In Excel function referencing the appropriate cells to determine the MAE for the MA(3) model. 2 Points: In cell B23, write a formula using a built-In Excel function referencing the appropriate cells to determine the MAE for the MA(5) model. 2 Points: In cell B26, write the model that best fits the data according to the MAE calculations. That is, write either \"MA(3)\" or \"MA(5)\" in cell B26. 8 Points: Using StatTools replicate the MA(3) and MA(5) forecasting processes following the guidelines below: 1. Create the MA(3) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell A1 of the StatToolsMA Worksheet. 2. Create the MA(5) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell G1 of the StatToolsMA Worksheet. 3. Confirm that your StatTools results match the results you obtained in earlier segments of this Question. 2 points: Would your choice of model that best fits the data using MAE be the same if the criteria were MAPE? Place your answer (Yes or No) in cell I24. QUESTION 2: Exponential Smoothing Models - Worksheet ES a. 6 Points: In column C (starting in the appropriate cell), write a formula to forecast demand through October using an ES model with smoothing constant alpha given in cell B1. Be sure to reference the cells containing the weights in the formula. b. 2 Points: In cell B20, write a formula to forecast demand for November using the ES model you constructed. c. 5 Points: In column D, under the label \"ES Error,\" write a formula in the appropriate cells calculating the error terms necessary to calculate the MAPE for the ES model. Do not construct a column of errors, then another column of absolute, squared, or absolute relative errors. Construct only one set of errors using the appropriate formula to make the errors absolute, squared, or absolute relative (whichever is appropriate for MAPE). d. 2 Points: In cell B24, write a formula using a built-In Excel function referencing the appropriate cells to determine the MAPE for the ES model. e. Leave it blank. f. Leave it blank. g. 10 Points: Using StatTools replicate the ES forecasting processes following the guidelines below: 1. Create the ES(alpha =0.2) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell A1 of the StatToolsES Worksheet. 2. Follow the guidelines in our text to find the optimal alpha (smoothing constant) for the ES model of our data. Incorporate in you process only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell G1 of the StatToolsES Worksheet. h. 5 Points: Which Metric does StatTools use for optimization? [Your textbook has the answer.] Place your answer in cell L29. What value does StatTools give as the optimal alpha? Place your answer in cell L30. What is the value of the MAPE at this alpha value? Place your answer in cell L31. 2 QUESTION 3 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. b. c. d. 8 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. 6 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. 6 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. 8 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. 3 Question 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 g MA(3) Error MA(5) Error Part Grader Is the time pattern stationary? (Yes or No) Grader StatTools MA(3) MA(5) Confirm Best? Best? a Grader Part h i f Part j Grader Part k.1. k.2. k.3. Grader Part l 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 Grader MAPE LEAVE IT BLANK. Solver Results alpha = MAPE = Nov Forecast = Values f StatTools ES(alpha=0.2) Optimal ES Part g.1. g.2. Grader Metric alpha MAPE Part l Grader 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 Part a Grader Is the time pattern stationary? (Yes or No) Demand 37 40 41 37 45 50 43 47 56 52 55 54 Part b c d Grader ANALYSIS