financial management practices of CVS Health Corp Assignment | Online Assignment

As an analyst who just started to follow pharmacy and drug store stocks, you are researching the corporate bond issuance, equity valuation, cost of capital, and other financial management practices of CVS Health Corp (CVS).

Part I: Data: bond prospectus, FINRA corporate bond data (FINRA1.png, FINRA2.png)[1].  You are to 1) research main features of one of the corporate bonds of CVS.  Present the main features of the bond in a clear way (see it presented in Excel in the example file).

2) build a tool using Excel or other programming languages or software to price bonds for a given yield to maturity (YTM), and a tool to calculate the YTM for a given bond price.

3) calculate the YTM of the bond at the time of issuance (offering).

Part II: Data: CVS_equity_data.xls, which includes the price and dividend history of the common stocks of CVS (ticker CVS), market index (S&P 500) price history, and the risk-free rate[2].

Using Excel or other programming languages or software, you will practice how to 1) estimate the beta of the stock and calculate the required rate of return, 2) evaluate the stock using dividend discount model (see it presented in Excel in the example file).

II.1 Estimate beta and calculate required return

• Set up the historical price data for the estimation. Use return = (end price – begin price)/begin price to calculate monthly return for the stock and for the market.  Typically, we use 5 years of monthly data for beta estimation.  The data given has 6 years (2014-2019) of data.  We can use the first 5 years to estimate beta and use 2019 data to calculate required return.
• Annualize return = monthly return *12. Note that the treasury-bill rate is already an annual rate.
• Use the formula beta = covariance(x,y)/variance(x), where x = annualized market return-risk-free rate, and y = annualized stock return – risk-free rate[3]. You can use the sample covariance and variance functions in Excel.
• Use the formula to calculate required return = risk-free rate + beta*(annualized market return – risk-free rate). We use 2019 data which is out-of-sample data from beta estimation.

II.2

• Estimate the growth rate of dividends from dividend history data. There is no specific formula for estimating g using historical data.  You can use your own model.
• Use the Constant Growth Model in the (a case of the Dividend Discount Models): Intrinsic Value = D0(1+g)/(R-g)

where D0 is the current annual dividend, and R is the required return we estimated in part I.

Part III: Data: CVS annual report (10K), CVS_health_corp.xls, which includes the basic financial data from the company’s filings for the past few years.

• Give a brief overview of the company’s business and profitability. Reading through one of the company’s quarterly or annual reports will help tremendously on understanding the company and the rest of the project.
• Give a brief description of the company’s capital structure (leverage), calculating appropriate ratios, tracking changes through the year, tax savings from interest (if any), indications of potential financial distress (if any), etc.
• Give a brief description of the company’s dividend policy.
• Using your calculations from Part I and Part II, as well as the company’s debt/equity ratio, calculate the company’s weighted average cost of capital (WACC).

Note: For a company with publicly traded debt, the cost of debt can be measured as the yield to maturity on the outstanding debt.  The coupon rate is irrelevant.  In this assignment you can use YTM of the bond you calculated in Part I as the cost of debt.

Assignments should be submitted in the format of excel files, word documents, or other formats if you use other programming languages.

[1] Prospectuses for registered corporate bond offerings can be found on the SEC’s EDGAR website. https://www.sec.gov/edgar/searchedgar/companysearch.html

• Use company name or ticker to find the company of your choice. 2) at the bar of “Filter results:”, set “Filing Type” to  424B2 then click “search” to find Prospectus.

FINRA’s market data center http://finra-markets.morningstar.com/BondCenter/Default.jsp

Use the “search” tab and Bond Type “Corporate” to find corporate bonds.  Use issuer name in the quick search to find all bond issues.  If you know the CUSIP of the bond, you can find the specific issue.

[2] Price and dividend data are from finance.yahoo.com, and treasury-bill data are from https://fred.stlouisfed.org/series/DGS1MO#0.

[3] Beta is also the regression coefficient of y regressed on x.