Income Statement Excel Sheet

Description

 For this project, 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. 

–  This project should include the following 5 worksheets (in one excel file) for the firm selected:

a)    Sheet 1 (Chapter 2-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 2 (Chapter 3-Financial Ratios): Evaluate your firm’s financial situation. Use actual and pro forma statements to construct financial ratios for your firm. Compare these to the current industry averages. Most of these industry ratios can be found on the FINRA.org website. Comment on the firm’s prognosis. 

c) Sheet 3 (Chapter 6 – Forecasting Sales): Use actual sales (revenue) data from previous 4 years to estimate sales using the linear trend function. 

d) Sheet 4 (Chapter 9 – 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). Compare the actual market price to the intrinsic value that you have calculated above. 

e) Sheet 5 (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.