• Project summary: You will conduct a relatively thorough financial analysis of a firm. Students will obtain relevant data, and perform valuation of the firm’s 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 company’s 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 owner’s 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 firm’s financial situation, as follows: Use actual and pro forma statements to construct financial ratios for your firm. Use financial ratios to determine the firm’s 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 firm’s 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 firm’s 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 firm’s 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 firm’s common stock. (Use chapter 8 as a guide). Find the firm’s 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 firm’s WACC: (Information comes from various chapters of your text) ▪  Estimate your firm’s 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 firm’s cost of common equity. ▪  Use FINRA.org to find the firm’s cost of debt. Obtain the firm’s marginal tax rate and use it to find the firm’s 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 firm’s cost of preferred stock. ▪  Use all of the above information and the firm’s market capital structure to determine the firm’s WACC.   3 Part III: Final Report PPT Presentation /// Slide 1 (Cover Slide): Final Report of your firm’s Financial Analysis “Your Report Title” “Your Name” FIN 5100, Corporate Finance, /// Slide 2: Company’s Overview: ///Slide 3: Firm’s Financial Statement Analysis /// Slide 4: Firm’s Risk and Return Analysis /// Slide 5: Firm’s Stock Valuation Analysis /// Slide 6: Firm’s Cost of Capital Analysis /// Slide 7: Summary and Conclusions /// Slide 8: Key References