IN3310: INTERMEDIATE FINANCIAL ANALYSIS SPREADSHEET ASSIGNMENT: Chapter 3 1. Read sections 3.5a through 3.5d in the textbook. 2. 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 Fuhd 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. I 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 I Regression. Click OK and enter the appropriate cell ranges for Colgate Palmolive Return and Market Return using the CAPM: Excess returns model (subtracts Er 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 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 1 Data Analysis Regression. Click OK and enter the appropriate cell ranges for Growth Fund of America Return and Market Return using the market model1. 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 IN3310: INTERMEDIATE FINANCIAL ANALYSIS SPREADSHEET ASSIGNMENT: Chapter 3 1. Read sections 3.5a through 3.5d in the textbook. 2. 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 Fuhd 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. I 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 I Regression. Click OK and enter the appropriate cell ranges for Colgate Palmolive Return and Market Return using the CAPM: Excess returns model (subtracts Er 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 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 1 Data Analysis Regression. Click OK and enter the appropriate cell ranges for Growth Fund of America Return and Market Return using the market model1. 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
