Acct 370 – week 4

I have attached my excel part # 1. Please refer to those numbers for this assignment.  

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:

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

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

o 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

o 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

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

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