Financial Analysis Exercise III
The purpose of this assignment is to apply stock valuation techniques to real financial data utilizing an online database.
Throughout this course you will work with the Mergent Online database and perform elements of securities analysis involving a select group of companies.
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 the Sherwin-Williams example below.
2. Select ONE of the four companies provided by your professor for analysis.
3. Write a 1-2 page (approximately 500 words) paper on the following:
a. Part A-Fundamental Valuation:
· Estimate a growth rate for your firm’s Dividends per Share.
· Assume a 12.5% discount rate.
· Calculate an 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 contrast your 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:
· Estimate a growth rate for your firm’s Earnings per Share (EPS).
· Determine an applicable Price-Earnings (P/E) ratio for your firm in 5 years.
· Calculate an 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.
6. Submit your paper to www.turnitin.com and make any necessary changes to the paper as a result of this submission.
· Submit your final paper to www.turnitin.com.
· Click the Submit button to upload your completed assignment by Sunday of this week.
· Click the Meet button to join the online class meeting scheduled by your professor. Actively participate in class discussions.
· Accurate process or model used to complete the exercise: 0 – 20 points
· Accurate and/or complete answers: 0 – 40 points
· Demonstration of critical thinking skills: 0 – 10 points
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:
P0 = current price of the stock
D0 = dividend per share at the end of the last period (last year)
D1 = 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 P0. The discount rate is assumed to be 12.5%. So we need to find values for D0, g, and D1. The last variable equals [D0,∙ (1 + g)], so we are left to find only D0 and g.
D0 is a historical number. We will look for this number for Sherwin Williams at Mergent Online accessed in February 2019 for this example.
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:
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)
Dividends per common share
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.
D0 = 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.
FVn = PV (1 + i)n is restated as Dividends2017 = Dividends2013 (1 + g)4
3.44 = 2.00(1 + g)4
Divide both sides by the PV of 2.00
3.44/2.00 = (1 + g)4
1.72 = (1 + g)4
Take the fourth root of both sides
1.72 1/4 = [(1 + g)4]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 Growth Rate
($3.44 – $3.36)/$3.36 = 0.02381
2016 : $3.36
($3.36 – $2.68)/$2.68 = 0.25373
($2.68 – $2.20)/$2.20 = 0.21818
($2.20 – 2.00)/$2.00 = 0.10
Average Annual Growth Rate
= (0.02381 + 0.25373 + 0.21818 + = 0.10)/4
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 model Let’s use this SeekingAlpha Five Year Average Dividend Growth Rate of 11.96%, the only rate less than the assumed cost of equity of 12.5%.
Recall the equation for the Constant growth model:
P0 = Estimated Current Price = Intrinsic Value
D0 = 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:
D1 = D0(1 + g) = $3.44(1.1196) = $3.85142
Since in our choice of g, ke > g:
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 P0 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 uses Equation 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:
∙ E0 ∙ (1 + g)5
P5 = price of the stock in five years
= price-earnings ratio in year 5
E5 = Earnings per share in year 5
E0 = 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:
EPS Continuing Basic
EPS Net Basic
EPS Continuing Diluted
EPS Net Diluted
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:
∙ E0 ∙ (1 + g)5 = 21.8447 ∙ $20.03 ∙ (1.14)5 = 21.8447 ∙ $20.03 ∙ 1.92541 = $842.45
At 21% growth:
∙ E0 ∙ (1 + g)5 = 21.8447 ∙ $20.03 ∙ (1.215 = 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.
 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
 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
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.
Why Work with Us
Top Quality and Well-Researched Papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
Professional and Experienced Academic Writers
We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.
Free Unlimited Revisions
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.
Prompt Delivery and 100% Money-Back-Guarantee
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.
Original & Confidential
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.