In this assignment you will
Perform a detailed stock price analysis on your companys stock, and
Calculate and analyze the companys Weighted Average Cost of Capital (WACC)
1.Stock Price Analysis:
a.Monthly Stock Price Analysis: In Yahoo! Finance go to the Historical Prices page and import into Excel the last 5 years of monthly stock prices (use the ending date as of the end of the previous month). For the below activities use the adjusting closed price (Adj Close)
What is the current stock price? What were the highest and lowest stock price values during this period?
For the remaining items in this section you will analyze the Year-Over-Year returns. In your spreadsheet, calculate the Year-Over-Year (YoY) return for each month. (Please submit this worksheet in Excel with your written paper.) The calculation for this is:
(month same month prior year) / (same month prior year)
What were the highest and lowest YoY returns during this period?
Calculate the mean or average of these YoY returns from your sample.
Calculate the standard deviation of the YoY returns. (In Excel, use the STDEV function.)b.Graphical View of Variability: In Yahoo! Finance go to the Basic Chart for your company. Select the 5 year (5Y) Range of the chart. In the Compare line select the S&P 500 and then press the Compare button.
Copy and paste this chart into this section of your paper.
Interpret your observations. Does the general movement of your companys returns follow that of the S&P 500? Is the companys stock price today higher than the companys stock price at the beginning of this 5 year period?2.Analysis of WACC
a.Calculation of WACC for your company
You will need to identify each component of the WACC formula and calculate the overall WACC.
The WACC formula for a company that uses debt and equity is as follows:
WACC = % Debt * Cost of Debt * (1 Tax Rate) + % Equity * Cost of Equity
You should use Yahoo! Finance and / or the companys most recent 10K report to identify all financial statement inputs. You can use the following guide for the inputs. The specific financial statement data are found on the relevant financial statement. Debt = Long-term Debt + Short-term Debt (on Yahoo! this is called, Short/Current Long Term Debt)
Equity = Market Cap. (This is on the Key Statistics page in Yahoo! Finance)
% Debt = Debt / (Debt + Equity)
% Equity = 1 % Debt
Cost of Debt = Interest Expense / Debt
Tax Rate = Income Tax Expense / Income Before Tax
Cost of Equity: Use the CAPM equation to calculate this
Cost of Equity = Risk free rate + Beta * (Market Risk Premium)
Risk free rate: look up the yield on 10 year US Treasury bonds
Beta: This is on the Key Statistics page in Yahoo! Finance
Market Risk Premium: Assume 11% minus the risk-free rateb.Interpretation of WACC for Your Company
Indicate what the WACC value you derived means for your company. What role does the WACC play for company managers when they are evaluating new projects to undertake? How would company managers and investors use the WACC for an overall company valuation analysis?3.Paper Mechanics
Please complete your work in Excel. For the calculation section of the assignment, each parameter, its data and the corresponding calculations should be clearly indicated and easy to follow. Your spreadsheet should be professional in its presentation with appropriate formatting and organization. Points will be taken off the final grade for these errors and omissions at your instructors discretion.