Financial Analysis Exercise III

Purpose

The purpose of this assignment is to apply stock valuation techniques to real financial data utilizing an online database.

Overview

Throughout this course you will work with theMergent Online database and perform elements of securities analysis involving a select group of companies.

Action Items

Note: For this and any other exercise that relies upon database information and website access, please notify your instructor should directions and links not work.

1.Review theSherwin-Williams example below.

2.SelectONEof the company

a.Part A-Fundamental Valuation:

Estimatea growth rate for your firm's Dividends per Share.

Assumea 12.5% discount rate.

Calculatean estimated value of a share of the stock using the constant-growth model (Eq. 8-6 in the textbook), also known as the Gordon growth model.

Compare and contrastyour valuation results with the current share price in the market. Provide the date of the current share price.

Respond to this question:What changes in the variables would be necessary in your valuation to best approximate the current market valuation?

b.Part B - Relative Valuation:

Estimatea growth rate for your firm's Earnings per Share (EPS).

Determinean applicable Price-Earnings (P/E) ratio for your firm in 5 years.

Calculatean estimated value of a share of the stock in 5 years using the P/E ratio model (Eq. 8-10 in the current edition of the textbook).

4.Respond to this question:Would you characterize your stock as undervalued or overvalued? Explain.

5.Respond to this question:Based on your valuations in parts A and B, would you invest in this stock? Explain.

FINA 301: SHERWIN-WILLIAMS EXAMPLE

Supporting Information for Financial Analysis Exercise III

Part A: This first part of the exercise asks you to select one of four company stocks and apply the constant- growth model in an attempt to value a share of that stock. Here is the equation for the model, Equation 8-6 in the current edition of the textbook:

P₀ ==,

where

P₀ = current price of the stock

D₀ = dividend per share at the end of the last period (last year)

D₁ = dividend per share at the end of period 1 (next year)

i = discount rate

g = constant growth rate

We need to find values for the variables in this equation in order to solve for P₀. The discount rate is assumed to be 12.5%. So we need to find values for D₀, g, and D₁. The last variable equals [D₀, (1 + g)], so we are left to find only D₀ andg.

D₀ is a historical number. We will look for this number for Sherwin Williams at Mergent Online accessed in February 2019 for this example.[1]

At the Mergent database at the University's online library we locate the information for Sherwin-William s Co. (SHW) via the "Company Search" search box by name or stock ticker symbol for public companies

At Sherwin-William's main page click on the tab Company Financials and enter the following in the drop down boxes:

• Annuals
• Income Statements
• 5 Years/Quarters

Click Refresh

Four rows from the bottom are "Dividends per Common share" for the fiscal years 12/31/17 to 12/31/13. (Note that at this date the 2018 financials are not yet available. You should use the most current information provided.)

Here are the results after downloading to an Excel file:

Sherwin-Williams Co (The) (NYS: SHW)

Report Date

12/31/2017

12/31/2016

12/31/2015

12/31/2014

12/31/2013

Currency

USD

USD

USD

USD

USD

Dividends per common share

3.44

3.36

2.68

2.20

2.00

Sherwin-Williams, like many public companies, issues dividends quarterly, so the annual dividend is the sum of the dividends issued in any fiscal year. Also, Sherwin-Williams has traditionally paid constant quarterly amounts in the fiscal year. In 2017 that quarterly amount was $0.86, so the dividends paid in all of 2017 = 4 x $0.86 = $3.44 per common share. If we were not at the end of a fiscal year, we could sum the last four quarters to get the most current annual dividend, i.e., the sum of dividends paid over the last four quarters. In this case the last four quarters are those that comprise the 2017 fiscal year.

D₀ = 0.344

We could use the annual dividends is the last 5 years to determine g, the dividend growth rate. But first here is what the textbook suggests about estimating g:

Investors use several methods to estimate a firm's growth rate...they can project the dividend trend into the future and determine the implied growth rate, compute the past growth rate, or even consider a financial analyst's growth rate predictions.

Source: Cornett, M., et al. (2018). Finance: Applications & Theory (4th ed.). New York: McGraw-Hill Irwin, p. 275.

Looking at the historical dividend payments in the recent past, we can calculate a past growth rate just as we solve for the interest rate in a time-value-of-money problem. Here we use the annual dividend payment in 2017 and 2013 to calculate the growth rate, g, over the past four years.

FV_n = PV (1 + i)ⁿ is restated as Dividends²⁰¹⁷ = Dividends²⁰¹³ (1 + g)⁴

3.44= 2.00(1 + g)⁴

Divide both sides by the PV of 2.00

3.44/2.00 = (1 + g)⁴

1.72 = (1 + g)⁴

Take the fourth root of both sides

1.72 ^1/4 = [(1 + g)⁴]^1/4

1.14520 = (1 + g)

Subtract 1 from both sides:

.14520 = g

g = .14520

Alternatively, we can average the recent annual growth rates:

Annual Dividend

Annual Growth Rate

2017: $3.44

($3.44 - $3.36)/$3.36 = 0.02381

2016 : $3.36

($3.36 - $2.68)/$2.68 = 0.25373

2015: $2.68

($2.68 - $2.20)/$2.20 = 0.21818

2014: $2.20

($2.20 - 2.00)/$2.00 = 0.10

2013: $2.00

Average Annual Growth Rate

= (0.02381 + 0.25373 + 0.21818 + = 0.10)/4

= 0.59572/4

= 0.14893

Of course, we could extend this analysis further back in time. You should note that given an assumed discount rate, i, of 12.5%, this 4-year average would result in a negative denominator in the constant-growth equation and result in a negative price for the stock, which is not a realistic result.

One such source is available at https://seekingalpha.com/At this site enter the stock's ticker symbol in the box at the upper right and click on search to open the company's page. Click on the Dividends tab. A 5-year average dividend growth rate of 11.96% is given.

Assuming a constant payout ratio, i.e., dividends are a fixed percentage of earnings, then a projected earnings growth can serve as an estimate for dividend growth. One source for such estimates is again at https://seekingalpha.com/At this site enter the stock's ticker symbol in the box at the upper right and click on search to open the company's page. Click on the Growth tab to get earnings compound annual growth rate estimate for the next 3-, 5-, and 10-years. For Sherwin-Williams these net income annual growth estimates are 29.68%, 23.88%, and 15.70%, respectively.

Such estimates of earnings growth are also available at Mergent. On the company's main page there click on the Earnings Estimates tab. Here the estimates for Sherwin-Williams' EPS Long-Term Growth is 14%.

Here are the estimates for g we have found:

Growth in the Dividend from 2013 to 2017: 0.14520

Average Annual Growth in the Dividend from 2013 to 2017: 0.14893

SeekingAlpha Five Year Average Dividend Growth Rate: 0.1196

SeekingAlpha Five Year Estimated Net Income Growth: 0.2366

Mergent EPS Long-Term Growth: 0.1400

Of these, only the third can be used for growth (g), given a 12.5% cost of equity estimate for the constant growth modelLet's use this SeekingAlpha Five Year Average Dividend Growth Rate of11.96%, the only rate less than the assumed cost of equity of 12.5%.[2]

Recall the equation for the Constant growth model:

P₀ ==

where

P₀ = Estimated Current Price = Intrinsic Value

D₀ = dividend in the current period (t = 0)

g = constant growth rate

Inputting the data collected in the Constant growth model equation to estimate the current value of Sherwin-Williams stock follows:

D₁ = D₀(1 + g) = $3.44(1.1196) = $3.85142

Since in our choice of g, k_e > g:

P_o ==== $712.96296

Now we go back to the Sherwin-Williams main page at Mergent and look at the most recent closing stock price under Pricing Summary. At the close on February 18, 2019 Sherwin-Williams stock price was $439.18. Consider the following questions:

• How does the current market price compare with our calculated intrinsic value?
• Is the stock a good buy?Does our calculation of P₀ suggest that the current market price is overvalued or undervalued?
• If you're not comfortable with your own results, you can try adjusting the growth rate given the rate of return. What does it take to get a price that's reasonably in line with the market's thinking?

Part B:The second part of the exercise asks you to estimate the relative value of your stock in five years based on its Price Earnings (P/E) ratio. This approach to valuation usesEquation 8-10 in the current edition of the textbook:

Future Price = Future P/E ratio x Future earnings per share (EPS)

Designating the future price in five years results in the following:

P₅ =E₅

=E₀ (1 + g)⁵

where

P₅ = price of the stock in five years

= price-earnings ratio in year 5

E₅ = Earnings per share in year 5

E₀ = Most recent Earnings per share

g = constant growth rate

Let's assume Sherwin-Williams' stock's P/E ratio will remain unchanged. At the Mergent main page for the company a P/E of 21.8447 is provided. Also, on the main page are current values for earnings per share: EPS Basic: 20.47 and EPS Diluted: 20.03. (Recall these numbers were found earlier in Financial Exercise I.) Let's use the latter as a bit more conservative

On growth, in Part A at Mergent we found for Sherwin-Williams an EPS long-term growth rate of 14%. This was located on the company's main page by clicking on the Earnings Estimates tab.. Also, at Mergent clicking on Company Financials and then the Analysis tab results in 3- and 5-Year Compound Annual Growth Rates for various measure of earnings per share. The numbers provided for Sherwin-Williams at 2/18/18 were:

3 Year

5 Year

EPS Continuing Basic

0.20

0.21

EPS Net Basic

0.19

0.21

EPS Continuing Diluted

0.20

0.21

EPS Net Diluted

0.19

0.21

Note: Previously, the distinction between EPS Basic and EPS Diluted was discussed,.Here are further distinctions between earnings from continuing operations and net earnings. Both earnings numbers are after-tax but net earnings includes any income from one-time events and discontinued operations. EPS Continuing excludes such income.

Let's use the current P/E, EPS Diluted, and growth rates (g) of both 14% and 21% to estimate the relative value of the stock in five years.

At 14% growth:

P₅ = E₀ (1 + g)⁵ = 21.8447 $20.03 (1.14)⁵ = 21.8447 $20.03 1.92541 = $842.45

At 21% growth:

P₅ = E₀ (1 + g)⁵ = 21.8447 $20.03 (1.21⁵ = 21.8447 $20.03 2.59374 = $1,134.89

Both relative value calculations suggest Sherwin-Williams stock is currently undervalued in the market.Please remember these are forecasts subject to the data choices made. It is, of course, possible to change the numbers used if you believe the results are unrealistic, but regardless you should always be able to justify the choices you make in arriving at your forecasts.

Financial Analysis Exercise IV

Purpose

The purpose of this assignment is to apply concepts of weighted average cost of capital fromModule 5: Capital Budgetingand capital structure and dividend policy fromModule 6: Capital Structure Issuesutilizing real financial data from an online database.

Overview

Throughout this course you will work with theMergent Online database and perform elements of securities analysis involving a select group of companies.

Action Items

Note: For this and any other exercise that relies upon database information and website access, please notify your instructor should directions and links not work.

1.SelectONEof the four companies provided by your professor for analysis.

2.Complete theFinancial Analysis Exercise IV Worksheet.

Submission Instructions

Click theSubmitbutton to upload your completed assignment bySaturday of Week 12.

Click theMeetbutton to join the online class meeting scheduled by your professor. Actively participate in class discussions.

Name ____________________________

Financial Analysis Exercise IV Worksheet

Part A: Weighted Average Cost of Capital (WACC)

Here again is the formula for WACC. For simplicity the term for preferred stock has been removed:

1.Go to http://thatswacc.com/[3] and enter the ticker symbol for the stock you selected and click on the tab entitled "Calculate WACC."

2.Complete the following tables:

Name of Company/Stock

Ticker Symbol

From the http://thatswacc.com/results for your company:

WACC

Cost of debt, r_D

Corporate tax rate, T_C

Percentage of Financing from Debt. D / V

Cost of equity, re

Percentage of Financing from Equity, E / V

Part B: Dividend Payout and Growth Ratios

Recall from Module 1 the following two ratios:

Internal growth rate = (ROA RR) / [1-(ROA RR)](Eq. 3-32)

where

RR = Retention ratio

= Addition to retained earnings / Net income available to common stockholders(Eq. 3-33)

-The internal growth rate measures the amount of growth a firm can sustain if it uses only internal financing (retained earnings) to increase assets

Sustainable growth rate = (ROE RR) / [1-(ROE RR)](Eq. 3-35)

-If the firm uses retained earnings to support asset growth, the firm's capital structure will change, i.e., the share of equity will increase relative to debt

-To maintain the same capital structure managers must use both debt and equity financing to support asset growth

-The sustainable growth rate measures the amount of growth a firm can achieve using internal equity and maintaining a constant debt ratio

1. For the firm selected for Part A, calculate its internal growth rate for the last fiscal year:

=(ROA RR) / [1-(ROA RR)]

=

2. Calculate the firm's sustainable growth rate for the last fiscal year:

= (ROE RR) / [1-(ROE RR)]

=

Part C

Note: Should the http://thatswacc.com/ not be available you can still respond to the questions below in terms of the effects on WACC of changes in the overall and relative levels of debt and/or equity in the financing of your chosen firm.

1.Consider your results for Parts A and B. If the chosen firm grows at its internal growth rate, increasing assets only with its retained earnings, how will this likely affect its WACC?

2.If the chosen firm grows at its sustainable growth rate with increases in both its retained earnings and debt, maintaining a constant debt ratio, how will this affect its WACC?

3.If the chosen firm attempts to grow faster than its sustainable growth rate with modest increases in its debt ratio, how will this likely affect its WACC? What about very large increases in its debt ratio? Explain.

[1] An alternative source for dividend information is YahooFinance. Enter your company's ticker symbol, and then sequentially click on:

Historical Prices

Downward arrow that follows: Show:Historical Prices

Dividends Only

Select Max Time Period

Apply

[2] More generally, you can always access the original financial statement filings of public companies via the U.S. Securities and Exchange Commission's Edgar database available at

https://www.sec.gov/edgar/searchedgar/companysearch.html

At this site enter the company's ticker symbol in the input box to get the listing of all filings the company has made with the SEC. Enter "10-K" in the "Form Type" box in the upper right to retrieve annual financials for the company. Enter "10-Q" to get quarterly financials. Typically, these documents are lengthy so it may take some time to locate the financial data you are seeking, but they are the source documents for financial information for public companies in the U.S.

[3] The accessibility of this site is assumed. Should it not be accessible, please advise your instructor.