Use data from the Standard & Poor's Market Insight Database at www.mhhe.com/edumarketinsight to answer the following questions.

1. Use data from Market Insight to plot the characteristic lines for Alcoa and Sharper Image. First locate the Market Insight page for Alcoa by clicking on the "Company" tab. (If you don't know the stock symbol, use the "Lookup" feature to find it.) Find the 1-month total returns of Alcoa and the S&P 500 in the Monthly Adjusted Prices Report in the Excel Analytics, Market Data section. Download the data into Excel, and then plot the Alcoa Returns vs. the S&P 500 returns. Use an XY Scatter Plot chart type, with no line joining the points. Select one of the data points, then right-click your mouse to get a shortcut menu which allows you to add a trend line. This is the characteristic line for Alcoa. Repeat the process for Sharper Image. What conclusions can you draw about Alcoa and Sharper Image based on their characteristic lines?
2. Use data from Market Insight to calculate the beta of Staples, Inc. Start by finding the monthly price changes of Staples and the S&P 500 in Monthly Adjusted Prices Report in the Excel Analytics, Market Data section. Copy the data into Excel and confirm the monthly rates of return (based on closing prices) for each series. Using the entire period for which data are available, estimate a regression with Staples' return as the dependent (Y) variable and the S&P 500 return as the independent (X) variable. Now repeat the procedure using only the most recent two years of data. Estimate a third regression using only the earliest two years of data. How stable is the beta estimate? Finally, compare your three results to the beta listed in Staples' S&P Stock Report (in the S&P Stock Reports section). Do any of your results match the S&P Report's beta? What factors might explain any differences?
3. The S&P Report gives information about the company's operations and opinions about its expected performance. Enter the symbol for Gap, Inc. and follow the link to S&P's Stock Report on the company. What companies does Gap operate? What is its weight in the S&P 500? What is the trend in Gap's earnings? What is the trend in its dividend payout ratio? Use the current price listed to calculate the holding period return on the stock assuming that you purchased it at the 52-week low price and that you received the specified dividends for the year. Repeat the calculation using the 52-week high price.
4. In the Excel Analytics section, find the monthly returns in the Monthly Adjusted Prices report for the following firms: Genzyme Corporation, Fujitsu LTD, Cardinal Health, Inc., Black and Decker Corporation, and Kellogg Company. Copy the returns from these five firms into a single Excel workbook, with the returns for each company properly aligned. Use the full range of available data. Then do the following:
1. Using the Excel functions for average (AVERAGE) and sample standard deviation (STDEV), calculate the average and the standard deviation of the returns for each of the firms.
2. Using Excel's correlation function (CORREL), construct the correlation matrix for the five stocks based on their monthly returns for the entire period. What are the lowest and the highest individual pairs of correlation coefficients? [Alternative: You may use Excel's Data Analysis Tool to generate the correlation matrix.]