Description

Explain the Net present value (NPV) rule and provide an example. How is the IRR related to the NPV analysis and why is the Internal Rate of Return (IRR) a popular alternative to the NPV? Select one of the other capital budgeting tools discussed this week and explain how it provides additional helpful information that the NPV or IRR tools might not adequately capture.

In this assignment you will

 

Longer Writing Assignment  – 2 Page APA Paper, for the calculation section of the assignment complete work in Excel.  Details bel

1.     Stock Price Analysis:

a.     Monthly Stock Price Analysis:

§  In Yahoo! Finance go to the Historical Pricespage https://finance.yahoo.com/quote/MSFT/history?ltr=1  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 (YoY) 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 theS&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 company’s returns follow that of the S&P 500? Is the company’s stock price today higher than the company’s 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 company’s 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 rate

b.     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 instructor’s discretion.

Get Homework Help Now