EXCEL PROJECT: COMPETITOR ANALYSIS ASSIGNMENT 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)

Instructions

  • Continue in the same Excel workbook prepared in Module 2: Week 2, adding the following tabs:
    • The 5th tab and 8th 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 6th and 9th 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 7th and 10th 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 11th 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.

 

Note: Your assignment will be checked for originality via the Turnitin plagiarism tool.

"Get Help With Your Essay
. If you need assistance with writing your essay, our professional essay writing service is here to help!

Order Now