教授出了一道让我完全无法理解的题，求解他的问题不是这么长的题目，而是问为什么不再出这个题目作为考题了。我不知道如何下手啊？求大神们给点建议，我好方便下手
----------------------------------------------------------------------------------------------------------------------------------------------------------

Below is thelast question I asked in the spring 2010 version of this course. Do not answer the questions at the end ofthe test. Instead, tell me why I’m not assigning it or something similarfor this test. Limit your answer to one page, single-spaced, double-spacedbetween paragraphs, typed 12 point Times New Roman font.

Estimation of Systematic and Factor Risks

This part of the test requires you to perform aseries of exercises involving the estimation of systematic risk (beta),factor risk, and residual risk for: Google Inc (GOOG), Koninklijke PhilipsElectronics NV (PHG), and Pfizer (PFE). In addition to computing average weeklyreturns, betas, factor exposure, and residual standard deviations for thereturns on these stocks, you are required to examine the stability of theseparameter estimates across two non-overlapping 1-year periods. Adetailed description of the specific (step-by-step) requirements is included atthe end of this document. The remainder of this note provides a brief tutorialon using Excel to complete the assignment.

Therequired data are available on my p-drive. The file is named Factors10.xls. Thespreadsheet file includes two worksheets. The worksheet “Prices” stores weeklystock prices (adjusted for stock splits/dividends) for 2006-2007 downloadedfrom the “Yahoo!Finance” web site. These prices were used to compute the weekly returns storedin the worksheet “Factor and Stock Returns”. This second worksheet includeseight columns with two years of weekly returns data. Columns F through Hrespectively contain two years of weekly returns for GOOG, PHG, and PFE.

ColumnB contains the weekly excess returns ( RM - RF ) for avalue-weighted portfolio including all NYSE, AMEX, and Nasdaq firms. Columns Cand D respectively contain the weekly returns for the Fama/French firm size andbook- to-market factors, while column E contains the weekly return for a shortmaturity U.S. Treasury bill. The weekly return to the firm size factor reflectsthe difference in the average weekly returns for three value-weightedportfolios including firms having below-median market capitalization and forthree value-weighted portfolios including firms having above-median marketcapitalization, where firms are sorted into the respective portfolios accordingto their ranking based on market equity capitalization. The book-to-marketfactor reflects the difference between the average weekly returns for theone-third of firms having the highest ratio of book value to market value andthe one-third of firms having the lowest ratio of book value to market value.These factor returns were obtained from Ken French's web site. You are required to compute and comparesummary statistics for the weekly returns on the market return, with the summarystatistics for the weekly returns to the firm size and book-to-market factors.In addition, you are required to estimate the systematic (beta) risk, factorrisk (firm size and book-to-market), and the residual risk for each stock.

ComputingSummary Statistics for the Data:

To analyze the return data in your EXCELspreadsheet, you will need to select the Data Analysis functionunder the Tools menu in your spreadsheet. If Data Analysis is unavailable,select“Add-Ins” under the Tools menu. Then “check” the box next to the“Analysis Tool Pack”. This should give your spreadsheet access to DataAnalysis, which includes the Descriptive Statistics andRegression Functions that you need to complete this exercise.

To compute average monthly returns and standarddeviations for each return series, select “Data Analysis” (or AnalysisTools for earlier versions of Excel) under the “Tools” menu. When the menu ofTools for Data Analysis appear on the screen, select “DescriptiveStatistics”.

A screen then appears to prompt you for theinformation Excel needs to compute summary statistics for your data. In the“Input Section” of the box, input the range of values for which you wish tocompute summary statistics and check the “Summary statistics” options in theOutput Options section of the window. For example, to compute the averageweekly returns and standard deviations for the firm size and book-to-marketfactors stored in columns C and D input the range C2:D106 to indicatethat there are 105 observations for each weekly return series, running fromcellC2 through cell D106 (you could compute summary statistics for allseven weekly return series by entering the range B2:H106) . You shouldalso click on columns, to let Excel know that the monthly returns foreach return series are included within a single column, as well as the Summarystatistics box, to let Excel know that you want a report on the summarystatistics for the data.

EstimatingSystematic Risk:

The output section on your screen controls wherethe regression output is stored. Select either Output Range and enterthe desired position for the “top left cell” of a vacant range of cellswhereExcel can deposit your output, or else select New Worksheet Ply to tellExcel that you want your output to be placed in a separate worksheet. You mustalso check the box next to Summary Statistics so that Excel willknow exactly which descriptive statistics you need. Once you havemadeyour selections, click on OK and Excel will compute the descriptivestatistics that you have requested. Remember that the first cell of the InputRange should be the first data point that you wish to include in your summarystatistics. It should not be a blank cell or a cell containing text.

The CAPM-based approach to estimating betasassumes the relation between a stock’s return and the return on thevalue-weighted market portfolio (the “market” proxy) stored in column B is

RStock - RF    =   a   +   b [ RMarket - RF  ]  +   ε

The regression coefficient b can be used asan estimate for the stock's beta or systematic risk. Note that although theCapital Asset Pricing Model implies that beta should be estimated by regressing“excess stock returns” (i.e., the return on the market net of the risk-freerate) on the excess returns for a market proxy as shown above, in practice themore traditional Market Model, which involves regressing rawreturns for the stock on the raw returns for the market

RStock =    a  +   b RMarket    +   ε

givesvirtually identical estimates of beta (usually to the 3rdor 4th decimal point).

To estimate the beta for a given stock, firstselect Data Analysis under the Tools Menu. Then select Regressionfrom the menu of Analysis Tools.

Excelthen prompts you to input the cell ranges for the dependent and independentvariables for the regression. Whenever we use a regression package (like Excel)to estimate the systematic risk (beta) for a stock, we are essentiallyattempting to explain the variation in the periodic returns on the stock usingthe variation in the returns for a market index (or proxy for the “market”),subject of course to the constraint that the relation between the two returnseries be linear. Therefore, the dependent variable for your regressions willbe the weekly returns for one of the three stocks stored in columns F throughH. Input the range of values for the dependent variable in the box titled “InputY Range”.

Wecan illustrate this Excel application by estimating the beta for GOOG over thefirst 1-year sample period. The range of cells entered in the Input YRange box should be “F2:F53” telling EXCEL to use the 52 weekly returns storedin the cells ranging from F2 through F53.

Inthe “Input X Range” box, input the range for the independent variable (weeklyexcess returns for the value-weighted market portfolio proxy). The first datapoint (weekly return) for this market proxy is stored in cell B2. To estimatethe market beta for GOOG during the first year in the sample, enter B2:B53 inthe Input X Range box. Remember that the number of observations includedin the Input Y Range and Input X Range boxes must be equal. Then select thecells or worksheet in which to store your output, click OK, and Excelwill estimate the beta for GOOG. The Regression output reports the betaestimate as the regression coefficient for “X Variable 1”.

Noteif you also select the box labeled ‘Residuals’, Excel will save the regressionresiduals, which can then be used to estimate the properties of GOOG’sfirm-specific returns.

You will also be required to estimate betas forboth of the 1-year periods included in the sample. For example, toestimate GOOG’s beta for the second 1-year period in sample, enterF54:F106 in the Input Y Range box and B54:B106 in the Input X Range box. Notethat the first cell in the Y range must be the first data point that you wishto use in your regression. It should not be a blank cell or a cell containingtext.

EstimationAssignment:

You are required to provide a summary of yourstatistical analysis for each of the following questions.

1.      Computethe means and standard deviations for the weekly excess returns for the marketproxy, the firm size factor and the book-to-market factor. Annualize thesestatistics by multiplying the average weekly returns by 52 and multiplying theweekly standard deviation by (52)½.Compare the annualized mean returns and standard deviations for these factorswith one another and the risk premium for the market reported in your textbook.What do you find?

2.      Usethe 52 weekly returns included in the second 1-year sub period (the data inrows 54-105) to estimate the beta, the weekly firm-specific returns for eachstock and the annualized residual standard deviation for the stock. (i.e.,regress the weekly returns foreach of the three stocks on theweekly excess returns for the market proxy stored in column B). Check/selectthe box labeled ‘Residuals’ located in the regression control box and Excelwill automatically save the regression residuals, which can then be used as aproxy for each stock’s specific or residual returns.

[Hint:The weekly residual standard deviation is the “Standard Error” from the RegressionStatistics section of your regression summary output.]

3.      Estimatethe betas for the three stocks in the data set during the first 1-yearsub-period included in the sample (the data in rows 2 through 53). Comparethese estimates of beta and residual standard deviation with the betas that youestimated for the second 1-year sub-period in problem 2. Discuss anydifferences between the estimated betas and residual risk for the first half ofthe sample period relative to your estimates for the second half.

4.      Compareyour estimates of beta from problem 2 with the estimates of beta reported at quote.yahoo.com.[Hint: Go to the Yahoo Finance web site, input the ticker symbol andretrievea stock price quote, then click on the profile link under thestock price quote to find Yahoo's estimate of the stock's beta.]

5.      Pastethe firm-specific residual returns that you estimated in problem 2 into three adjacentcolumns in your spreadsheet (recall that these estimates of residual returnsare a byproduct of estimating the beta’s for the three stocks in the sample).Then use the Correlation function available in the Data AnalysisTools to compute the sample correlations for your estimates of residual return.Carefully explain whether the estimated correlations are consistent with theCapital Asset Pricing Model assumption that residual returns are uncorrelated.If you find that the residual correlations are not consistent with the CapitalAsset Pricing Model, try to provide an explanation as to why the residualreturns for these stocks might have non-zero correlations.