In year 2002 there was considerable concern within the mutual savings banks because monthly changes in deposits were getting smaller and monthly changes in withdrawals were getting bigger. Thus it was of interest to develop a short-term forecasting model to forecast the changes in end-of-month (DEOM) balance over the next few months. Table 1 shows 53 monthly observations (February 2002 to June 2006) of the changes in end-of-month balance. Also presented in the table are the composite AAA bond rates (AAA) and the rates on U.S. Government 3-4 year bonds (3-4), and the changes in the 3-4 year bonds (D3-4). It was hypothesized that these three variables had an influence on the DEOM balance figures in the bank. Using the data in Table 1, perform the following tasks. Prepare and print out the Excel outputs and the results of the analyses and bring them to the in-class exam. During the in-class exam, you will be asked questions related to these analyses. 1.1. Develop a correlation matrix among all four variables (DEOM, AAA, 3-4, and D3-4). Comment on the results 1.2. Develop a regression model to forecast the DEOM figures based on the three independent variables (AAA, 3-4, and D3-4). Evaluate the model and comment on the strength of the model Perform the Durbin-Watson test using =0.01 to check if autocorrelation exists. Show the details of your test and comment on the results. 1.3. Create dummy (indicator) variables to allow monthly seasonal components to be included in the regression model. Develop a new regression model, which includes these dummy variables and the three independent variables. Evaluate the model and comment on the strength of the model compared to the one in part 2. Problem 2: Gap Sales Table 2 presents the Gap's quarterly sales data for 1985 Q1 through 2006 Q4. Analyze the data and answer the following questions: 2.1. Create a time-series plot of the Gap Sales and describe the pattern(s) you see in the graph. 2.2 Calculate the seasonal factors (SI) and then the seasonal indices (S) based on the 1985 Q1-2006 Q4 data. Are they consistent with your expectations? Explain. \begin{tabular}{|c|c|c|c|c|} \hline Date & DEOM & AAA & D3-4 & 34 \\ \hline Feb-02 & 1.146 & 5.94 & 0.29 & 5.3 \\ \hline Mar-02 & -2.443 & 6 & -0.11 & 2.0 \\ \hline Apr-02 & 1.497 & 6.08 & 0.31 & 5.49 \\ \hline May-02 & -0.132 & 6.17 & -0.19 & 5.8 \\ \hline Jun-02 & 2.025 & 6.14 & -0.33 & 5.61 \\ \hline Jul-02 & 0.737 & 6.09 & -0.09 & 5.28 \\ \hline Aug-02 & -1.023 & 5.87 & -0.01 & 5.19 \\ \hline Sep-02 & -0.956 & 5.84 & 0.12 & 5.18 \\ \hline Oct-02 & 0.385 & 5.99 & -0.07 & 5.3 \\ \hline Nov-02 & 0.983 & 6.12 & 0.41 & 5.23 \\ \hline Dec-02 & 5.092 & 6.42 & -0.02 & 5.64 \\ \hline Jan03 & 3.649 & 6.48 & 0.05 & 5.62 \\ \hline Feb-03 & 2.703 & 6.52 & 0.16 & 5.67 \\ \hline Mar-03 & -0.271 & 6.64 & -0.3 & 5.83 \\ \hline Apr-03 & 2.055 & 6.75 & 0.23 & 5.53 \\ \hline May-03 & -0.714 & 6.73 & 0.33 & 5.76 \\ \hline Jun-03 & 0.653 & 6.89 & 0.43 & 6.09 \\ \hline Jul-03 & -0.034 & 6.98 & 0.16 & 6.52 \\ \hline Aug-03 & -1.058 & 6.98 & 0.39 & 6.68 \\ \hline Sep-03 & -2.051 & 7.1 & 0.05 & 7.07 \\ \hline Oct-03 & 1.451 & 7.19 & 0.13 & 7.12 \\ \hline Nov-03 & -0.989 & 7.29 & 0.6 & 7.25 \\ \hline Dec-03 & 1.358 & 7.65 & 0.17 & 7.85 \\ \hline Jan04 & 0.746 & 7.75 & -0.15 & 8.02 \\ \hline Feb-04 & 1.855 & 7.72 & -0.73 & 7.87 \\ \hline Mar-04 & -1.894 & 7.67 & 0.06 & 7.14 \\ \hline Apr-04 & 0.781 & 7.66 & 0.39 & 7.2 \\ \hline May-04 & -0.161 & 7.89 & 0.15 & 7.59 \\ \hline Jun-04 & 2.233 & 8.14 & -0.23 & 7.74 \\ \hline Jul-04 & 2.425 & 8.21 & -0.05 & 7.51 \\ \hline Aug-04 & 2.169 & 8.05 & -0.37 & 7.46 \\ \hline Sep-04 & 0.982 & 7.94 & -0.27 & 7.09 \\ \hline Oct-04 & 4.708 & 7.88 & -0.6 & 6.82 \\ \hline Nov-04 & 6.063 & 7.79 & -0.61 & 6.22 \\ \hline Dec-04 & 9.382 & 7.41 & -0.13 & 5.61 \\ \hline Jan05 & 9.304 & 7.18 & -0.7 & 5.48 \\ \hline Feb-05 & 10.69 & 7.15 & -0.64 & 4.78 \\ \hline Mar-05 & 6.531 & 7.27 & 0.5 & 4.14 \\ \hline Apr-05 & 7.873 & 7.37 & 0.88 & 4.64 \\ \hline May-05 & 3.882 & 7.54 & 0.43 & 5.52 \\ \hline Jun-05 & 4.96 & 75 & 0.25 & 5.95 \\ \hline Jul-05 & 1.301 & 7.62 & -0.17 & 6.2 \\ \hline Aug-05 & 1.154 & 7.58 & -0.43 & 6.03 \\ \hline Sep-05 & 0.116 & 7.48 & -0.34 & 5.6 \\ \hline Oct-05 & 4.928 & 7.35 & -0.3 & 5.26 \\ \hline Nov-05 & 2.53 & 7.19 & 0.32 & 4.96 \\ \hline Dec-05 & 8.425 & 7.19 & 0.09 & 5.28 \\ \hline Jan06 & 5.291 & 7.11 & 0.16 & 5.37 \\ \hline Feb-06 & 5.192 & 7.16 & 0.19 & 5.53 \\ \hline Mar-06 & 0.257 & 7.22 & 0.32 & 5.72 \\ \hline Apr-06 & 4.402 & 7.36 & -0.38 & 6.04 \\ \hline May-06 & 3.173 & 7.34 & 0.09 & 5.66 \\ \hline Jun-06 & 5.104 & 7.3 & 0.07 & 5.75 \\ \hline \end{tabular}