Description

Excel Project: Part II Instructions

Competitor Income Statements, Balance Sheets, and Cash Flows (Tabs 5-7), and

Historical and Competitor Ratios (Tab 8)

·         Continue in the same Excel workbook prepared in Week 1, adding the following tabs:

o   The fifth tab in your Financial Statement Analysis Template must be labeled Competitor Income Statements

§  Include the latest income statement for the company selected for the Excel project along with two or three major competitors.  (Only one year of data is shown for each company – the latest year available.)

o   The sixth tab in your Financial Statement Analysis Template must be labeled Competitor Balance Sheets

§  Include the latest Balance Sheet for the company selected for the Excel project along with two or three major competitors.  (Only one year of data is shown for each company – the latest year available.)

o   The seventh tab in your Financial Statement Analysis Template must be labeled Competitor Statements of Cash Flows

§  Include the Statement of Cash Flows for the company selected for the Excel project along with two or three major competitors.

§  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

o   The eighth 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

o   Current Ratio

o   Quick Ratio

·         Leverage Ratios

o   Debt to Total Assets Ratio

o   Debt Equity Ratio

o   Long-Term Debt to Equity

o   Times Interest Earned Ratio

·         Activity Ratios

o   Inventory Turnover

o   Fixed Assets Turnover

o   Total Assets Turnover

o   Accounts Receivable Turnover

o   Average Collection Period

·         Profitability Ratios

o   Gross Profit Margin

o   Operating Profit Margin

o   Net Profit Margin

o   Return on Total Assets (ROA)

o   Return on Stockholders’ Equity (ROE)

o   Earnings Per Share (EPS)

o   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-7 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.

o   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 should be calculated using formulas.  For an example of which cells may need formulas, please see Exhibit B.

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