Reference no: EM131099350
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.
Year
|
Beta
|
2015
|
|
2014
|
|
2013
|
|
2012
|
|
2011
|
|
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
How much is the constant amount
: A college student is buying a new car, which costs $16,500 plus 8% sales tax. The title, license, and registration fees are $650. The dealer offers her a financing program that starts with a small monthly payment, and the payments will gradually incr..
|
What is the arc price elasticity of demand
: If a stock is expected to pay a dividend of $ 25 for the current year, what is the approximate present value of this stock, given at discount rate of 10 % and a dividend growth rate 2 %. If a price of corn is $ 3.00 a bushel, 5000 bushels would be de..
|
How would classify the espresso coffee market
: How would classify the espresso coffee market; are firms price takers or price makers? Explain - explain why there has been such an explosion in the number of coffee chains in Australia over the past 10 years.
|
What would the market value of your bond be
: The market interest rates for like securities rose to 5%. Would your bond sell for a premium or a discount? Why? What would the market value of your bond be? Prove your answer by showing your work.
|
Do your firms tend to be more growth or value stock and why
: 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.)
|
Prepare a schedule of collections and payments for six month
: Prepare a Schedule of Collections and Payments for the six month period beginning in March, using the template provided and the data above.
|
Describe how the issue affects you at a personal level
: Gonzalez-Padron (2015) addressed several ethical issues that have arisen in the 21st century. Using the Ashford University Library, find an article relating to a 21st century ethical issue (please do not limit yourself to the list in the textbook)..
|
Discuss in detail dual-diagnosis from your readings
: Discuss in detail dual-diagnosis from your readings, then incorporate how such coexisting disorders impact the addiction cycle discussed in Module/Week's 6 presentation. How does your biblical worldview impact the way in which you see the possibil..
|
Why is freeing up cash like important to any given company
: Why is freeing up cash like this important to any given company? Rank order the following capital project types according to level of risk, from lowest risk to highest risk.
|