EXCEL FORMULAS ONLY IN EXCEL

Problem 4: Forecasting American Express closing prices are presented in the Template for Span of 5 tab. Create a moving averages forecasting model for a span of 5. Also complete the following: a. Calculate Forecasted prices, Errors, Absolute Errors, Absolute Percentage Errors, MAE, and MAPE. b. Create three graphs: i. Original observation ii. Forecasted and Original Observations iii. Forecast error Which moving average is better with a span of 4 or 5? Provide your answer within the template. Problem 4: American Express Closing Price Span of 5 Close Absolute Error Date Forecast Absolute Percentage Error Error MAE MAPE 15-Oct-03 16-Oct-03 17-Oct-03 20-Oct-03 21-Oct-03 22-Oct-03 23-Oct-03 24-Oct-03 27-Oct-03 28-Oct-03 29-Oct-03 30-Oct-03 31-Oct-03 3-Nov-03 4-Nov-03 5-Nov-03 6-Nov-03 7-Nov-03 10-Nov-03 11-Nov-03 12-Nov-03 13-Nov-03 14-Nov-03 17-Nov-03 18-Nov-03 19-Nov-03 20-Nov-03 21-Nov-03 24-Nov-03 25-Nov-03 26-Nov-03 28-Nov-03 1-Dec-03 2-Dec-03 3-Dec-03 4-Dec-03 5-Dec-03 8-Dec-03 $48.30 $48.38 $47.84 $47.92 $48.40 $47.04 $47.63 $47.62 $46.75 $47.54 $47.35 $46.52 $46.93 $47.23 $47.12 $47.31 $47.37 $46.50 $46.08 $45.48 $46.02 $45.80 $45.33 $43.93 $44.48 $44.88 $44.40 $44.90 $45.15 $45.35) $45.73 $45.71 $46.05 $45.80 $45.99 $46.07 $45.50 $46.15 9-Dec-03 10-Dec-03 11-Dec-03 12-Dec-03 15-Dec-03 16-Dec-03 17-Dec-03 18-Dec-03 19-Dec-03 22-Dec-03 23-Dec-03 24-Dec-03 26-Dec-03 29-Dec-03 30-Dec-03 31-Dec-03 2-Jan-04 5-Jan-04 6-Jan-04 7-Jan-04 8-Jan-04 9-Jan-04 12-Jan-04 13-Jan-04 14-Jan-04 15-Jan-04 16-Jan-04 20-Jan-04 21-Jan-04 22-Jan-04 23-Jan-04 26-Jan-04 27-Jan-04 28-Jan-04 29-Jan-04 30-Jan-04 2-Feb-04 3-Feb-04 4-Feb-04 5-Feb-04 6-Feb-04 9-Feb-04 10-Feb-04 11-Feb-04 $45.70 $45.19 $45.25 $45.20 $45.31 $45.27 $45.00 $46.77 $47.21 $47.44 $47.68 $47.88 $47.83 $48.50 $48.29 $48.23 $47.75 $48.17 $48.25 $48.39 $48.25 $47.89 $47.70 $47.87 $48.75 $49.68 $49.91 $50.24 $50.400 $50.00 $49.96 $50.95 $50.48 $49.75 $50.95 $51.84 $52.54 $52.51 $52.26 $52.08 $53.06 $53.11 $52.85 $53.71 3 12-Feb-04 13-Feb 04 17-Feb-04 18-Feb-04 19-Feb-04 20-Feb-04 23-Feb-04 24-Feb-04 25-Feb-04 26-Feb-04 27-Feb-04 1-Mar-04 2-Mar-04 3-Mar-04 4-Mar-04 5-Mar-04 8-Mar-04 9-Mar-04 10-Mar-04 11-Mar-04 12-Mar-04 15-Mar-04 16-Mar-04 17-Mar-04 18-Mar-04 19-Mar-04 22-Mar-04 23-Mar-04 24-Mar-04 25-Mar-04 26-Mar-04 29-Mar-04 30-Mar-04 31-Mar-04 1-Apr-04 2-Apr-04 5-Apr-04 6-Apr-04 7-Apr-04 8-Apr-04 12-Apr-04 13-Apr-04 14-Apr-04 15-Apr-04 $53.35 $53.65 $53.68 $53.40 $53.40 $53.16 $53.06 $53.18 $53.58 $53.39 $53.42 $53.65 $53.41 $53.49 $53.36 $52.77 $53.14 $53.18 $52.19 $51.53 $52.12 $50.95 $50.65 $51.00 $51.56 $51.08 $50.21 $50.21 $50.03 $49.95 $50.03 $50.86 $51.91 $51.85 $52.20 $52.43 $52.59 $52.40 $52.38 $52.14 $52.26 $51.40 $49.80 $49.75 Y 16-Apr-04 19-Apr-04 20-Apr-04 21-Apr-04 22-Apr-04 23-Apr-04 26-Apr-04 27-Apr-04 28-Apr-04 29-Apr-04 30-Apr-04 3-May-04 4-May-04 5-May-04 6-May-04 7-May-04 10-May-04 11-May-04 12-May-04 13-May-04 14-May-04 17-May-04 18-May-04 19-May-04 20-May-04 21-May-04 24-May-04 25-May-04 26-May-04 27-May-04 28-May-04 1-Jun-04 2-Jun-04 3-Jun-04 4-Jun-04 7-Jun-04 8-Jun-04 9-Jun-04 10-Jun-04 14-Jun-04 15-Jun-04 16-Jun-04 17-Jun-04 18-Jun-04 $50.58 $50.21 $49.51 $49.88 $50.78 $50.27 $50.28 $50.15 $49.04 $49.04 $48.95 $49.39 $49.31 $49.52 $49.18 $48.49 $48.251 $48.14 $48.21 $48.47 $48.86 $48.77 $48.99 $48.84 $49.33 $49.63 $49.93 $50.46 $50.25 $51.02 $50.70 $50.60 $50.92 $50.75 $50.98 $51.45 $51.67 $51.23 $51.20 $51.05 $51.22 $51.51 $51.46 $51.96 21-Jun-04 22-Jun-04 23-Jun-04 24-Jun-04 25-Jun-04 28-Jun-04 29-Jun-04 30-Jun-04 1-Jul-04 2-Jul-04 6-Jul-04 7-Jul-04 8-Jul-04 9-Jul-04 12-Jul-04 13-Jul-04 14-Jul-04 15-Jul-04 16-Jul-04 19-Jul-04 20-Jul-04 21-Jul-04 22-Jul-04 23-Jul-04 26-Jul-04 27-Jul-04 28-Jul-04 29-Jul-04 30-Jul-04 2-Aug-04 3-Aug-04 4-Aug-04 5-Aug-04 6-Aug-04 9-Aug-04 10-Aug-04 11-Aug-04 12-Aug-04 13-Aug-04 16-Aug-04 17-Aug-04 18-Aug-04 19-Aug-04 20-Aug-04 $51.50 $51.50 $51.39 $50.96 $51.35 $50.94 $51.27 $51.38 $51.33 $51.24 $50.83 $51.01 $50.27 $50.01 $49.71 $49.49 $49.25 $48.90 $48.73 $48.45 $49.12 $48.35 $48.33 $48.11 $48.90 $49.44 $49.89 $49.96 $50.25 $50.28 $49.91 $50.21 $49.55 $49.34 $49.30 $50.09 $50.13 $49.47 $49.35 $49.42 $49.73 $50.03 $49.83 $50.00 6. 23-Aug-04 $49.70 24-Aug-04 $49.75 25-Aug-04 $49.95 26-Aug-04 $50.12 27-Aug-04 $50.00 30-Aug-04 $49.63 31-Aug-04 $50.02 1-Sep-04 $50.11 2-Sep-04 $50.47 3-Sep-04 $50.26 7-Sep-04 $50.99 8-Sep-04 $50.94 9-Sep-04 $50.90 10-Sep-04 $51.33 13-Sep-04 $51.49 14-Sep-04 $51.33 15-Sep-04 $50.90 16-Sep-04 $50.90 17-Sep-04 $51.40 20-Sep-04 $51.16 21-Sep-04 $51.48 22-Sep-04 $50.87 23-Sep-04 $50.87 24-Sep-04 $51.21 27-Sep-04 $50.65 28-Sep-04 $50.83 29-Sep-04 $51.38 30-Sep-04 $51.46 1-Oct-04 $51.65 4-Oct-04 $52.63 5-Oct-04 $52.76 6-Oct-04 $53.27 7-Oct-04 $53.07 8-Oct-04 $52.25 11-Oct-04 $52.53 12-Oct-04 $52.71 13-Oct-04 $52.15 14-Oct-04 $51.71 15-Oct-04 $52.50