# Stock Valuation and Evaluation

Instructions
1. This assignment is due on 24 by midnight.
2. Please do all the computation in Excel and then write a report (in the form of MS Word). The report should include not only the reasoning and arguments but also all the results you get in Excel to support your project. Please write your full name as the name of the report and all other supplementary files.
3. Make sure to save any Excel file as “Excel Macro-Enabled Workbook.”
4. Please use the discussion board to exchange ideas with others. Before asking any questions regarding the Excel technicalities, do your research and google first as there are plenty of resources available on the internet.
5. You must use Getformula() to show your formulas in Excel. Please read the pdf file named “Getformula” for directions. You have to show all formulas for any Excel computations used in the project. 5 points will be deducted if not.
6. a list of companies below. Pick anytwo.
7. (30 points) Calculate the two stocks’ CAPM betas over time. Go to “Yahoo Finance” and download their monthly price data from 2007-2015. Calculate their monthly returns (make sure the dates are in the ascending order). Also, download the monthly prices of the S&P500 and calculate returns. Use the slope() function to calculate the stocks’ betas. To get a stock’s beta in 2015, use returns 2011-2015. To get a stock’s beta in 2014, use returns 2010-2014, etc… Get betas over the five-year period. Below is an example of how you can present your results.
Year Beta
2015
2014
2013
2012
2011
Comment on your observations (1-2 sentences).

8. The Excel file “compf” has the companies annual financial data from S&P’s Compustat data. S&P’s Compustat contains fundamental and market information on publicly held companies from 1950 to present. In the Excel file, find your company’s ticker and use the most recent data of five years. All variables are in thousands.
9. (60 points) Calculate the following financial ratios for those five years (set to missing (‘.’) if any of the data needed to calculate the ratio is missing). Please create one table for each company that contains all the ratios over time.
a. P/E (hint: price is per share.)
b. Market-to-Book equity ratio
c. ROE
d. ROA
e. Current ratio
f. Profit margin or return on sales
g. Leverage Ratio
h. Tax Burden
i. Total asset turnover
j. Interest Burden
k. Interest Coverage