Finance Project: Stock Valuation and Evaluation

Instructions

1. 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.

2. Make sure to save any Excel file as "Excel Macro-Enabled Workbook."

3. 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.

4. I have a list of companies below. Pick anytwo.

Google, Amazon, McDonald's, Apple, JPMorgan, Bestbuy, Consolidated Ed, Amazon, Microsoft, Valeant, Gilead, AT&T, Exxon Mobile

5. 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.

Comment on your observations (1-2 sentences).

6. 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.

7. 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

8. Answer the following questions and specifically use your calculated ratios to back up your statements. Please review pg.451-462.

a. Do your firms tend to be more growth or value stock? Why? If their status as growth or value changes over time, please also explain. (3-4 sentences.)

b. Please search for any news articles that may substantiate your analysis.

i. Was there any unusual event for your company in the time period of interest? Ex) if the p/e ratio was unusually high in, say, 2012, look up what was going on around 2011-2013.

ii. Articles may discuss debates on whether your company is a growth or value stock.

c. First, try to verify the Dupont decomposition using your calculated ratios. Then, analyze ROE in terms of the DuPont decomposition in 100-150 words. Please see pg.457 and 464-465 for ideas.

9. Suppose you want to form a portfolio of the two stocks that you chose. Using weights ranging from 0% to 100% by increments of 5%, calculate your portfolio's mean, variance, standard deviation, and CAPM alpha using the most recent 5-year monthly returns. Compare the portfolio's mean and s.d. of returns with individual stocks' mean and s.d. of returns. Plot your results in the risk-return plot (mean return on the y-axis and standard deviation on the x-axis). Comment on your observation.

10. Suppose you want to combine your two-stock portfolio with the riskfree assets. Suppose that the mean return on 30-day T-bill (assume completely risk-free) for the most recent 5 years is 1%. Using weights ranging from 0% to 100% by increments of 5%, calculate your new portfolio's mean, variance, standard deviation, and CAPM alpha using the most recent 5-year monthly returns. Plot your results in the risk-return plot (mean return on the y-axis and standard deviation on the x-axis). Comment on your observation.

Attachment:- compf.xlsx