# Valuation Spreadsheet for Johnson and Johnson

Anonymous
timer Asked: Jul 5th, 2018
account_balance_wallet \$30

Question description

Please submit the valuation Excel spreadsheet for Johnson and Johnson. The attached valuation spreadsheet (ValuationModels.xls) has yellow highlighted cells on two worksheets (FCFF Valuation and Earnings Growth Models) which need to be filled with the Johnson and Johnson data.

Attached is a sample valuation worksheet (ValuationModels.xls), document on how to compute the Weighted Average Cost of Capital (WACC.pdf) and the class textbook for reference (businessanalysis-valuation3rd-ed-62402.pdf).

Computing the Weighted Average Cost of Capital WACC is the weighted average cost of capital i.e., a weighted average of the cost of debt (rd) and the cost of equity (re) given a firms current financing mix (i.e., the value of its debt [vd] and the value of its equity [ve]) . The WACC is thus equal to the sum, of the product of the cost of debt and the proportion of capital that comes from debt, and the product of the cost of equity and the proportion of capital that comes from equity: WACC = rd * vd / (vd + ve) + re * ve / (vd + ve) Total equity financing is the sum of the values of debt capital (vd) and equity capital (ve). Since debt capital is put on the balance sheet at its present value, we need only find the company’s longterm debt (plus any current portion of long term debt) to determine V d. The value of equity is more problematic, if we use book-value (equity from the balance sheet were understating the value of equity substantially. If we use market capitalization (the product of the price per-share of common stock and the number of shares of common stock outstanding), were including price in a model were using to determine price. Unfortunately, that’s the best we can do, so market cap is our proxy for ve. The cost of debt (rd) is also determinable in a straight-forward fashion. The firm’s notes will list the various debt owed, and the cost (interest rates) of each. Often the company will distill these numbers into a weighted average for you. If it doesn’t, you can compute it yourself. e.g., lets say Zulu Company has the following debt issues outstanding: Amount Rate \$2,000,000,000 8% bonds \$8,000,000,000 9% bonds The total LT debt is \$10 billion, 20% at 5% and 80% at 6%. The weighted average of these interest rates are .2*.08 + .8*.09 = .016+.072 = .088 or 8.8% = rd If, however, the company operates in an environment where interest expense is deductible for tax purposes, then we must also adjust the cost of debt for the tax shield on interest to determine rd. For example, if the company has an effective tax rate (tax expense/earnings before taxes) of 40%, then the real interest rate (the percentage we want to use as rd in our calculations) is .088 x (1- tax rate) = .088 x (1-.4) = .088 x .6 = .0528 or 5.28% Okay, weve got rd, vd, and ve. All we need is re, or the cost of equity capital. Well get re from the capital asset pricing model, where: re = rf + β(rm-rf), and rf is the risk free rate, b is beta or systematic risk, and rm is the average return to the equity markets. Our proxies for these variables are as follows: rf = rate of interest paid on 30 year US treasury securities; β is the regression coefficient derived when changes in a particular company’s common stock price are regressed on a measure of changes in market prices (e.g., the S&P 500 index). Usually, β is computed over 1 year of trading days (240 observations), and it can be found (rather than computed) in various published sources; rm is the market rate of return, so the difference between rm and rf is often referred to as the risk premium. Historically, the risk premium has averaged between 7% and 8%. We can now approximate re using the CAPM, by setting re = 30year US Treasury Bond rate + β(.075). If, for example, the rate currently paid on 30-year US Treasury Bonds is 5.4% and the companys beta is 1.2, the estimate of re would be: re = .054 + 1.2(.075) = .054 + .09 = .144 or 14.4% Now we have all the parts necessary to compute the WACC!