FIN 620 Catholic University of America Corporate Finance Stock Valuation Capstone See the instruction in the first attached
file.It is required to write 3 pages paper. in addition, there is a part of the question
that requires EXCEL sheet.Note: in the secound attached file, there are steps
that should be followed for doing the excel sheet. Stock Valuation Project
The project is an exercise in valuing stocks. You are expected to answer each of
the questions in details and address each of the issues mentioned, and submit a
Project Report (word/pdf) and Calculation (excel).
Pick 3 (three) prominent corporate firms preferably from the same
industry/sector. The chosen firms must be publicly-traded who have been paying
an increasing amount of dividends for the last few years. Use the Constant
Growth Model to find the intrinsic values of these 3 stocks.
In calculating the ‘growth rate’ for the company, use sustainable growth rate
method. For estimating the ‘required return’, use the capital asset pricing model
(CAPM). As an estimate of the ‘risk free rate’, use the current yield to maturity of
a 10 year T-bill. For ‘market rate of return’, use 2019 average annual return for
S&P 500 index. Use firm beta from secondary sources.
Compare your calculated intrinsic values to the actual stock prices on April 24,
2020 (Friday). Does the market undervalue or overvalue your three stocks on
April 24, 2020? Based on your analysis, do you plan to undertake any transactions
in the stocks (buy? sell what you already own? just hold onto those? buy more?)?
Why or why not? Which stock looks most attractive (has more potential) for
• The Project Report should not be more than 3 pages.
• Use theory and applications from your textbook Chapters 6 and 10.
• Show all your works/calculation in an excel file. The required
financial statements of the 3 firms (Income Statements and Balance
Sheets) must be presented in excel; calculate all the required values
from those data using excel formula. You must attach the excel file
with the project report.
• Use reliable web sources (preferably Yahoo Finance) for financial
statements and other financial data such as stock prices.
• The grade for the project depends on the quality of work in terms of
accuracy, analyses, completeness, organization, writing, and
Steps for Stock Valuation Project
FIN 620: Corporate Finance
1. Pick an industry and select 3 publicly-traded dividend-paying firms from that industry.
2. Import 2019 ‘Balance Sheets’ and ‘Income Statements’ for the selected firms to your excel file
from Yahoo Finance’s Financials tab. Copy the statements in Yahoo Finance by selecting the
statements and paste them in your Excel file. While pasting, to keep the format, use ‘Paste>Paste
Special>Text>OK’. Increase the column sizes and format as required to easily use the cell
3. Import Dividend data from yahoo finance’s Historical Data tab (Show: ‘Dividends Only’) and by
using excel formula, calculate D0.
4. Import data for Number of Shares Outstanding from yahoo finance’s Statistics tab.
5. Import Beta data from Yahoo Finance’s Summary page.
6. Search for publicly available data for ‘Risk Free Rate’ and ‘Market Rate of Return’ following
the project’s instructions.
7. By using excel formula, calculate Required Return (R) [Risk Free Rate+ (Market Rate of
Return- Risk Free Rate)*Beta]
8. By using excel formula, calculate Growth Rate (G) [ROE/Retention Rate = (Net Income/Total
Equity) / (1-Payout Rate) where Payout Rate= EPS/DPS]
9. By using excel formula, calculate your P0 for each of the 3 firms by using Constant Growth
Formula, P0 = D0 /(R-G)
10. Import Price data from Yahoo Finance’s Historical Data tab to compare with your calculated
Purchase answer to see full