Instruction is in the file. You need to do it in excel. I need the answers in 2 hours.

Section 2 Data Part A Figuring out how to get data to run regressions. A friend tells you that you should invest in the Goldman Sachs Mid Cap Value Fund A shares, which has the ticker GCMAX. Your friend is a smart guy and you trust him, but it would disgrace your finance professor if you didn't analyze the fund yourself. First you need to get GCMAX historical returns, and historical returns from the Fama French Factors. Do this with the following steps. 1. Download the historical prices of GCMAX from Yahoo finance. You want monthly historical prices from inception (August 1997) to the present. Click 'Download to Spreadsheet', and save the prices. NOTE - YOU WANT TO USE ADJUSTED PRICES TO CALCULATE RETURNS! These are prices that correct for stock splits, add dividends back in etc. If you use the raw closing prices, you will need to calculate these adjustments yourself (red hot tip, you don't want to do that.) 2. Use these adjusted prices to calculate returns (in per cent) using the formula from lecture 1 : Ret(t) = 100* [ P(t) - P(t1) ] / P(t1) 3. Download the data on the risk free rate, excess market returns, and factors (SMB, HML and momentum). Search for 'Ken French', and locate his website. Click on the 'data library' section. Download the 'Fama French Factors' and the 'Momentum Factor'. These are in separate files. 4. Open up the two files in Excel. Use the 'Text to Columns' function to make each return in a separate column. 5. Making sure you have the dates lined up, put the GCMAX returns, the Fama French Factors, and the Momentum factors all in a single spreadsheet. NOTE: Ken French uses a date convention where each month refers to the end of the month. So 'March 1997' means returns FOR THE MONTH OF MARCH (i.e. st Returns as of March 31 1997'). For Yahoo finance, when you choose monthly data and download it to a spreadsheet, the data is given as the first of each month, but the adjusted close prices cover the whole month, not that day. So be sure you adjust the dates so that GCMAX March returns (from February Close to March Close) match up with March factor returns. IF YOU RUN REGRESSIONS AND FIND THAT YOU ARE GETTING VERY LOW R2 values (e.g. 0.05 or so) RECHECK THAT THE DATES LINE UP! 6. Make sure that the returns are either all decimals or all percentages. Then subtract off the risk free rate from GCMAX to get its excess returns. IF YOU ARE STILL GETTING VERY LOW R2, MAKE SURE YOU HAVE THE RIGHT UNITS! Now calculate the following: 4. What is the CAPM alpha of GCMAX? Calculate this by running a regression with GXMAX Excess returns as the dependent (Y) variable, and Market Excess Returns (Mkt - Rf) as the Independent (X) variable. Regression is found in Excel by clicking 'Data Analysis', then 'Regression'. a) b) c) What is the CAPM Alpha of GCMAX? Is the alpha of GCMAX statistically significant? How does this make GCMAX look as a fund? 5. 5. Calculate the 3 factor and 4 factor alpha of GCMAX. Do this by performing another regression with GCMAX Excess Returns as the Y variable, and either MktRf, SMB and HML (for the 3 factor ) or MktRf, SMB , HML and Mom (for the 4 factor). a) What are the three and four factor alphas of GCMAX? Are they statistically significant? b) What are the loadings of GCMAX on SMB and HML? Are they statistically significant? c) What do these loadings tell you about GCMAX as a fund? d) Why might the CAPM alpha be different from the 3 and 4 factor alphas? 5. 6. What is the timing ability of GCMAX? To do this, we want to calculate a new variable that equals to the market excess return when the excess return is positive, and zero otherwise. You can either do this by hand, or do it in Excel as follows: a) First create a variable that equals 1 if MktRf is positive, and zero otherwise. Use the 'IF' statement in excel (use excel help to find out how to do this) to make a variable that equals 1 if MktRf is greater than zero, and zero otherwise. b) Then multiple this by MktRf to get the new variable c) Regress GCMAX excess returns on MktRf and the new variable (call it MktRfPos). d) What is the coefficient on MktRfPos? Is it statistically significant? Based on this, does GCMAX appear to have timing ability? Part B - Identifying Stocks For Stock Pitches Suppose that you've got an asset management interview coming up, and you know that you'll be asked to discuss some of the stocks you think look like good potential purchases. Since you took Solomon's class, you don't just want to pick a random stock based on a bogus tip from your friend. You instead want to at least narrow the search based on factors that actually seem to predict returns - market capitalization, the bookto market ratio (the total book value of a company, divided by its market capitalization), and momentum (the cumulative stock return from 2 months ago to 12 months ago. You can get a reasonable approximation of these things using the Google Finance Stock Screener ( https://www.google.com/finance/stockscreener ). Your task is to identify three possible stocks that could be described as small, value, high momentum stocks. NOTE: Google Finance does not have exact versions of all of the three variables above, so you will need to think about what concepts they are trying to capture and come up with reasonable approximations of the variables in question. Explain what variables you sorted on to decide your list of stocks