Project summary: You will conduct a relatively thorough financial analysis of a firm. Students will obtain relevant data, and perform valuation of the firms statement analysis, risk, common stock, and cost of capital and determine if they are financially stable. Submission: Only Electronic copies will be accepted and handed in via Moodle. Dropbox will be set up for the project. Deadline: SUNDAY NOVEMBER 22nd. Grading Please show all work, assumptions and identify all sources used. This project will be graded on: – (1/2) Use of financial theory, critical thinking, and data analysis, – (1/2) Use of Excel: Is the model developed efficiently using input fields, etc., are the worksheets formatted neatly and effectively, and are models accurate? Sources Below are some suggested reference sources for your project. However, feel free to use other resources as required: Your companys annual report Security Exchange Commission http://finra-markets.morningstar.com/MarketData/CompanyInfo/default.jsp http://finance.yahoo.com/ 1 GUIDELINES You should select a firm! Make certain that you have access to all financial information of the firm. The firm must have outstanding common stock, outstanding bonds, and you have access to all their financial information with relatively little transactions cost. This will be incumbent upon you to find this out immediately and have it be a basis upon firm selection. Failure to do this will result in lower project grade. Tasks: Task 1: Financial Statement Analysis Task 2: Risk and Return Analysis Task 3: Stock Valuation Analysis Task 4: Determining Cost of Capital Deliverables Financial Analysis (Excel file) Part I in the project (80 points): Presentation (power point file) Part II in the project (20 points) Part I: Excel File Do the following for the firm selected, in an EXCEL file: a) Introductory Sheet (Financial Statements): Obtain the last 4 years of financial statements: balance sheet, income statement, and statement of owners equity. Import these items into an Excel workbook. Remember that this data will be recalled later in this project for analysis. The intent is not for you to manually enter these data, but to import, then format them. b) Sheet 1 (Chapter 7-Financial Statement Analysis): The goal in this sheet is to evaluate your firms financial situation, as follows: Use actual and pro forma statements to construct financial ratios for your firm. Use financial ratios to determine the firms liquidity, asset management, debt management, profitability and market value. Analyze the ratios of the firm over a three-year horizon and compare them with that of the industry averages. You are to calculate the ratios and compare them to the published ratios. Most of these industry ratios can be found on the FINRA.org website. Comment on the firms prognosis. Once that is done, interpret the ratios to describe in words the state of the firm relative to the five areas of performance identified above. 2 c) Sheet 2 (Chapter 2 Risk and Return): Find the firms BETA using BETA formula (Equation 2-14) in Chapter 2 Use the following link to get the risk-free rate ????: http://pages.stern.nyu.edu/~adamodar/ A good approach is to the most current rate for the 10-year U.S. Treasury bond yield. For the market risk premium (?????? ? ????), use 4.42%. The risk premium is the expected 10-year S&P 500 return relative to a 10-year U.S. Treasury bond yield. A typical ?????? is an annual average return of S&P 500 index based on a sample period like 10 years. Obtain the firms BETA from the latest (2018) VLIS (Value Line Investment Survey) reports for your firm choice from the VLIS website: https://research.valueline.com/research?_ga=2.51649774.673777525.1550770910- 1640214787.1550770899#list=dow30&sec=list Find the required rate of return using CAPM formula (Equation 2-15) in Chapter 2 d) Sheet 3 (Chapter 8 – Common Stock Valuation): Using the free cash flow model of stock valuation, compute the price per share of the firms common stock. (Use chapter 8 as a guide). Find the firms growth rate using the following formula: g = constant dividend growth rate = ROE x Earnings retention ratio Estimate the intrinsic value per share using Gordon growth model (Equation 9-3) in Chapter 9 How do the calculated intrinsic values compare to the actual current price of your firm? Use an IF statement to display whether the stock is undervalued, overvalued, or fairly valued. a) Sheet 4 (Chapter 11 – Cost of Capital): Estimate your firms WACC: (Information comes from various chapters of your text) ? Estimate your firms beta coefficient using 5 years of quarterly data for your firm and the S&P 500 index, found on Yahoo! Finance. ? Use the last 10 years of the annual closings of the S&P 500 (Use the GSPC ticker). Use the trend function to forecast one year ahead return. This is expected return for the market. ? Using the most current estimate of the 20-year T-Bond as the proxy for the risk-free interest and the expected market return from 2.b, estimate the firms cost of common equity. ? Use FINRA.org to find the firms cost of debt. Obtain the firms marginal tax rate and use it to find the firms after-tax cost of debt. If you have multiple bonds outstanding, use the weighted average YTM of each bond. ? If applicable, obtain the price of preferred stock and use the dividend to calculate the firms cost of preferred stock. ? Use all of the above information and the firms market capital structure to determine the firms WACC. 3 Part III: Final Report PPT Presentation /// Slide 1 (Cover Slide): Final Report of your firms Financial Analysis Your Report Title Your Name FIN 5100, Corporate Finance, /// Slide 2: Companys Overview: ///Slide 3: Firms Financial Statement Analysis /// Slide 4: Firms Risk and Return Analysis /// Slide 5: Firms Stock Valuation Analysis /// Slide 6: Firms Cost of Capital Analysis /// Slide 7: Summary and Conclusions /// Slide 8: Key References