IN3310: INTERMEDIATE FINANCIAL ANALYSIS SPREADSHEET ASSIGNMENT: Chapter 3 1. Read sections 3.5a through 3.5d in the textbook. Retrieve the file HWDataChapter3a. The data in the file refer to a single stock (Colgate Palmolive), a broad measure of the stock market (Standard & Poor's 500 Stock Index), a stock portfolio mutual fund (Growth Fund of America), and 3-month Treasury bills. All data begin in row 2 and end in row 49. 3. In Excel, select Data | Data Analysis I Regression. Click OK and enter the appropriate cell ranges for Colgate Palmolive Return and Market Return using the market model. These are the Input Y Range and Input X Range, respectively. Important note: The Analysis Toolpak is required for regression analysis in Excel. 4. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation. 5. In Excel, select Data I Data Analysis Regression. Click oK and enter the appropriate cell ranges for Colgate Palmolive Return and Market Return using the CAPM: Excess returns model (subtracts Rr from both the stock returns and market returns). These are the Input Y Range and Input X Range, respectively (Reminder: The original columns of data are available in Sheet 1) 6. Change the column width to 12 for each column of Summary output. Print using a landscape orientation. (Note: The results for parts 4 and 6 should be similar.) 7. In Excel, select Data I Data Analysis I Regression. Click OK and enter the appropriate cell ranges for Growth Fund of America Return and Market Return using the market model. These are the Input Y Range and Input x Range, respectively. 8. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation. 9. In Excel, select Data Data Analysis I Regression. Click OK 7. In Excel, select Data Data Analysis Regression. Click OK and enter the appropriate cell ranges for Growth Fund of America Return and Market Return using the market mode1. These are the Input Y Range and Input X Range, respectively 8. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation. 9. In Excel, select Data I Data Analysis I Regression. Click oK and enter the appropriate cel1 ranges for Growth Fund of America Return and Market Return using the CAPM: Excess returns Model (subtracts rr from both the mutual fund portfolio returns and Market returns). These are the Input Y Range and Input x Range, respectively 10. Change the column width to 12 for each column of Summary output. Print using a landscape orientation. (Note: The results for parts 8 and 10 should be similar.) 11. Retrieve the file HWDatachapter3a+. Stand-alone standard deviations of CL and AGTHX are computed in Cell D52 and Cell D53, respectively. Suppose a portfolio is created by investing 15% in CL and 85% in AGTHX. The weighted average standard deviation is calculated in cell D54. Column C computes rates of return for this portfolio. Calculate the standard deviation of this portfolio in Cell D56 and enter either Increased or Decreased in Cell H58. v/ YM, Market Return (S&P 500 Index) aowth Fund of er, Risk-Free MarketColgateAmerica Rate (Monthly Excess stock Excess Return (S&P Palmolive(CL) (AGTHX Return on 3 Excess marketreturn portfolio 500 Index) .2 Chapter 4 Flash EXPERT O&A E Chegg Study TEXTBOOK SOLUTIONS 8.7% 0.01% 0.01% 6.5% -0.9% -0.9% 7.1% 0.7% -2.8% -3.6% 2.0% 2A% "47% 7.1% 0.7% 8.1% 10 -3.0% 59% ,37% -17% 18% 57% -27% -5.5% 5,7% -17% 3.7% -4.9% 4.3% 3.0% 0.01% 0.01% 7A% 7.4% 21 -11.8% 0.02% 0.02% -11.8% 47% 9.7% 23 7.5% 4.5% 5.4% 3.7% 16.2% 0.9% 24% 7.5% 25 26 -11.0% -11.0% -75% 17.5% -75% -17.0% 175 11.9 29 41% 3.5% 0.14% 0.14% 0.16% 0.14% 0.11% 0.11% 0.18% 1.1% 32 7.1% -5.2% 8.8% -2,4% 05% 1.4% 2.6% 5.0% -75% 70% 3.0% 1.1% 41% 09% 46% -Q7% -17% 35 87% 37 41% 40 -47% 78% 2.7% -0.5% -2.3% 3.2% 1.1% -2.0% 0.9% 1.3% 1.3% -12% 15% 0.1% 09% -27% 0.7% 47 -14% 1.4% 0.39% 041% 041% r. Colgate. Palmolive (CL) Retura Growth Fund Pertfolle- ef America 115%C1+ AGTHX 85% (ACTX) Return -63% 0.5% -4.2% -9.0% 44% 4.2% 4.5% % -0.7% -53% -61% 0.5% -17% -U% 47% -54% 3.7% 8.0% -0.5% -54% 0.7% 6.5% -0.9% 71% 0.7% -2.8% -3 6% 2.0% 24% % 4,0% -4.9% -0.3% -3.0% -73% 11.8% .55% 23% AA% -2.0% -64% 0.2% -6.6% -9.7% -57% 1.4% -4.5% -1.7% -5.9% -0.9% -7.4% -8.4% 2.0% 75% -5.4% -14% -12% 16.2% 0.9% -2 A% 8.1% 74% 52% 175% 12.0% 0.0% 35% 65% -3.0% 17.3% 10.3% -0.4% 1.8% 66% -3.3% -2,4% 0.5% 1.4% 0.9% 55% 0A% 46% -2.4% 0.1% 08% si% 07% 3.2% -3.2% 26% -5.0% -75% -75% -0.5% -2.3% 32% -1,2% 15% 0.1% -1.1% 09% 06% 10.2% 0.9% -2.4% 17596 12.0% 0.0% 3.5% 65% -3.0% -5.1% 1.4% 0.9% 5.8% 0.4% 4.6% -2.4% -4,0% 17.3% 10.3% -0.4% 1.8% 66% -3.5% -3.0% 0.8% 0.8% 5,1% 0.7% 3.5% -3.2% -5.2% 28 29 30 31 32 34 35 36 37 38 39 40 41 42 43 71% -5,2% 8.8% -2.4% 0.5% 1.4% 2.6% -5.0% -75% .75% ,05% -23% 32% 1.1% -20% 0.9% 1.3% 0.1% -3.8% -1.9% 0.0% -3.1% -3.6% 45 46 47 48 1.4% 9.6% 1.4% 8.0% 50 52 53 54 Weighted average of CL +AGTHX CL AGTHX 5.1% 5.8% 57% 56 Portfolio of 15% CL +8S% AGTHX 57 58 Was portfolio risk Increased or Decreased by adding CL to AGTHX? Sheet1 Sheet2Sheet3+ Ready Show transcribed image text Expert Answer o IN3310: INTERMEDIATE FINANCIAL ANALYSIS SPREADSHEET ASSIGNMENT: Chapter 3 1. Read sections 3.5a through 3.5d in the textbook. Retrieve the file HWDataChapter3a. The data in the file refer to a single stock (Colgate Palmolive), a broad measure of the stock market (Standard & Poor's 500 Stock Index), a stock portfolio mutual fund (Growth Fund of America), and 3-month Treasury bills. All data begin in row 2 and end in row 49. 3. In Excel, select Data | Data Analysis I Regression. Click OK and enter the appropriate cell ranges for Colgate Palmolive Return and Market Return using the market model. These are the Input Y Range and Input X Range, respectively. Important note: The Analysis Toolpak is required for regression analysis in Excel. 4. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation. 5. In Excel, select Data I Data Analysis Regression. Click oK and enter the appropriate cell ranges for Colgate Palmolive Return and Market Return using the CAPM: Excess returns model (subtracts Rr from both the stock returns and market returns). These are the Input Y Range and Input X Range, respectively (Reminder: The original columns of data are available in Sheet 1) 6. Change the column width to 12 for each column of Summary output. Print using a landscape orientation. (Note: The results for parts 4 and 6 should be similar.) 7. In Excel, select Data I Data Analysis I Regression. Click OK and enter the appropriate cell ranges for Growth Fund of America Return and Market Return using the market model. These are the Input Y Range and Input x Range, respectively. 8. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation. 9. In Excel, select Data Data Analysis I Regression. Click OK 7. In Excel, select Data Data Analysis Regression. Click OK and enter the appropriate cell ranges for Growth Fund of America Return and Market Return using the market mode1. These are the Input Y Range and Input X Range, respectively 8. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation. 9. In Excel, select Data I Data Analysis I Regression. Click oK and enter the appropriate cel1 ranges for Growth Fund of America Return and Market Return using the CAPM: Excess returns Model (subtracts rr from both the mutual fund portfolio returns and Market returns). These are the Input Y Range and Input x Range, respectively 10. Change the column width to 12 for each column of Summary output. Print using a landscape orientation. (Note: The results for parts 8 and 10 should be similar.) 11. Retrieve the file HWDatachapter3a+. Stand-alone standard deviations of CL and AGTHX are computed in Cell D52 and Cell D53, respectively. Suppose a portfolio is created by investing 15% in CL and 85% in AGTHX. The weighted average standard deviation is calculated in cell D54. Column C computes rates of return for this portfolio. Calculate the standard deviation of this portfolio in Cell D56 and enter either Increased or Decreased in Cell H58. v/ YM, Market Return (S&P 500 Index) aowth Fund of er, Risk-Free MarketColgateAmerica Rate (Monthly Excess stock Excess Return (S&P Palmolive(CL) (AGTHX Return on 3 Excess marketreturn portfolio 500 Index) .2 Chapter 4 Flash EXPERT O&A E Chegg Study TEXTBOOK SOLUTIONS 8.7% 0.01% 0.01% 6.5% -0.9% -0.9% 7.1% 0.7% -2.8% -3.6% 2.0% 2A% "47% 7.1% 0.7% 8.1% 10 -3.0% 59% ,37% -17% 18% 57% -27% -5.5% 5,7% -17% 3.7% -4.9% 4.3% 3.0% 0.01% 0.01% 7A% 7.4% 21 -11.8% 0.02% 0.02% -11.8% 47% 9.7% 23 7.5% 4.5% 5.4% 3.7% 16.2% 0.9% 24% 7.5% 25 26 -11.0% -11.0% -75% 17.5% -75% -17.0% 175 11.9 29 41% 3.5% 0.14% 0.14% 0.16% 0.14% 0.11% 0.11% 0.18% 1.1% 32 7.1% -5.2% 8.8% -2,4% 05% 1.4% 2.6% 5.0% -75% 70% 3.0% 1.1% 41% 09% 46% -Q7% -17% 35 87% 37 41% 40 -47% 78% 2.7% -0.5% -2.3% 3.2% 1.1% -2.0% 0.9% 1.3% 1.3% -12% 15% 0.1% 09% -27% 0.7% 47 -14% 1.4% 0.39% 041% 041% r. Colgate. Palmolive (CL) Retura Growth Fund Pertfolle- ef America 115%C1+ AGTHX 85% (ACTX) Return -63% 0.5% -4.2% -9.0% 44% 4.2% 4.5% % -0.7% -53% -61% 0.5% -17% -U% 47% -54% 3.7% 8.0% -0.5% -54% 0.7% 6.5% -0.9% 71% 0.7% -2.8% -3 6% 2.0% 24% % 4,0% -4.9% -0.3% -3.0% -73% 11.8% .55% 23% AA% -2.0% -64% 0.2% -6.6% -9.7% -57% 1.4% -4.5% -1.7% -5.9% -0.9% -7.4% -8.4% 2.0% 75% -5.4% -14% -12% 16.2% 0.9% -2 A% 8.1% 74% 52% 175% 12.0% 0.0% 35% 65% -3.0% 17.3% 10.3% -0.4% 1.8% 66% -3.3% -2,4% 0.5% 1.4% 0.9% 55% 0A% 46% -2.4% 0.1% 08% si% 07% 3.2% -3.2% 26% -5.0% -75% -75% -0.5% -2.3% 32% -1,2% 15% 0.1% -1.1% 09% 06% 10.2% 0.9% -2.4% 17596 12.0% 0.0% 3.5% 65% -3.0% -5.1% 1.4% 0.9% 5.8% 0.4% 4.6% -2.4% -4,0% 17.3% 10.3% -0.4% 1.8% 66% -3.5% -3.0% 0.8% 0.8% 5,1% 0.7% 3.5% -3.2% -5.2% 28 29 30 31 32 34 35 36 37 38 39 40 41 42 43 71% -5,2% 8.8% -2.4% 0.5% 1.4% 2.6% -5.0% -75% .75% ,05% -23% 32% 1.1% -20% 0.9% 1.3% 0.1% -3.8% -1.9% 0.0% -3.1% -3.6% 45 46 47 48 1.4% 9.6% 1.4% 8.0% 50 52 53 54 Weighted average of CL +AGTHX CL AGTHX 5.1% 5.8% 57% 56 Portfolio of 15% CL +8S% AGTHX 57 58 Was portfolio risk Increased or Decreased by adding CL to AGTHX? Sheet1 Sheet2Sheet3+ Ready Show transcribed image text Expert Answer o