EXCEL PROJECT: COMPETITOR ANALYSIS ASSIGNMENT INSTRUCTIONS

OVERVIEW

COMPANY: Johnson and Johnson

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:

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

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

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

ACCT $370$

Page $2$ of $4$

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

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.

ACCT $370$

Page $3$ of $4$

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

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