Financial Statement Analysis

Excel Project: Part II Instructions

Competitor #1 Income Statement (Tab 5), Competitor #1 Balance Sheet (Tab 6), Competitor #1 Cash Flows (Tab 7), Competitor #2 Income Statement (Tab 8), Competitor #2 Balance Sheet (Tab 9), Competitor #2 Cash Flows (Tab 10), Historical and Competitor Ratios (Tab 11)

• Continue in the same Excel workbook prepared in Week 2, adding the following tabs:
  • The 5^th tab and 8^th tabs in your Financial Statement Analysis Template must be labeled (Competitor Company Name) Income Statements.  (Replace “Competitor Company Name” with the name of the competitor.)
    • Include the latest three years of income statements for your competitor based on SEC filings.
    • Ensure that the worksheet includes a header with:
      • Company Name
      • Financial Statement Name (Income Statements)
      • Financial Statement Dates
      • Denomination level ($s in millions or thousands, etc.)
  • The 6^th and 9^th tabs in your Financial Statement Analysis Template must be labeled (Competitor Company Name) Balance Sheets.  (Replace “Competitor Company Name” with the name of the competitor.)
    • Include the latest three years of balance sheets for your first competitor based on SEC (10-K) filings.  (Note: Some 10-Ks only provided 2 years of balance sheet data.  You may need to download the second most recent 10-K for your company in order to obtain 3 years of balance sheet data.)
    • Ensure that the worksheet includes a header with:
      • Company Name
      • Financial Statement Name
      • Financial Statement Dates
      • Denomination level ($s in millions or thousands, etc.)
  • The 7^th and 10^th tabs in your Financial Statement Analysis Template must be labeled (Competitor Company Name) Cash Flows.  (Replace “Competitor Company Name” with the name of the competitor.)
    • Include the latest three years based on SEC (10-K) filings.
    • Make sure that the Income Statement, Balance Sheet, and Cash Flows Statement tie in as follows:
      • Net Income from Income statement agrees with Net Income on the Statement of Cash Flows
      • Ending Cash Balance from Statement of Cash Flows agrees with Cash in the Balance Sheet
  • The 11^th tab in your Financial Statement Analysis Template must be labeled Historical and Competitor Ratios
    • The list of ratios to be included are as follows.  They should be grouped under the headings, as shown:
      • Liquidity Ratios
        • Current Ratio
        • Quick Ratio
      • Leverage Ratios
        • Debt to Total Assets Ratio
        • Debt Equity Ratio
        • Long-Term Debt to Equity
        • Times Interest Earned Ratio
      • Activity Ratios
        • Inventory Turnover
        • Fixed Assets Turnover
        • Total Assets Turnover
        • Accounts Receivable Turnover
        • Average Collection Period
      • Profitability Ratios
        • Gross Profit Margin
        • Operating Profit Margin
        • Net Profit Margin
        • Return on Total Assets (ROA)
        • Return on Stockholders’ Equity (ROE)
        • Earnings Per Share (EPS)
        • Price Earnings Ratio
    • Calculate the ratios for your chosen company for the last three years, linking the ratio calculations back to the historical financial statement tabs (Tabs 2-4) using formulas.
    • Calculate the ratios for each of your company’s competitors (most recent year only), linking the ratio calculations to the competitor financial statements shown in Tabs 5-10 using formulas.
    • Using the internet, locate the industry averages for the ratios listed above, and include them in the final column of the ratio tab.  Include a footnote on the tab to identify the source of the industry averages.
  • Other instructions:
    • The Excel workbook should be professionally formatted.  Professional formatting includes:
      • All numbers are formatted similarly.  The use of decimals, commas for thousands, and other formatting is consistent.
      • Dollar signs are included in the first and last numbers of each column.
      • Subtotals and totals are preceded by a single underline.  Totals are followed by a double underline.
      • Line descriptions are indented/bolded in a manner that makes the spreadsheet easy to read. 
    • All totals and subtotals in the competitor financial statements as well as the ratio calculations should be calculated using formulas. 

This assignment is due by 11:59 p.m. (ET) on Monday of Module/Week 4.

Exhibit A: Example Historical and Competitor Ratios (Tab 11)