CVS

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.

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