g. 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). h. 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. i. 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. j. 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. k 8 Points: Using StarTools 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 Al of the StatToolsMA Worksheet. 2. Create the MA(5) forecast incorporating only Forecast Overlay and Forecast Errors Charts. Anchor the output in cell GI of the StatToolsMA Worksheet. 3. Confirm that your StatTools results match the results you obtained in earlier segments of this Question. 1. 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 124. UESTION 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 Bl. 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. the Leave it blank. 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 Al 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 GI 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
