Competitor analysis excel project | ACCT 370 – Financial Statement Analysis | Liberty University

 
Pageof 4ZOOM

ACCT 370
Page 1 of 4
EXCEL PROJECT: COMPETITOR ANALYSIS ASSIGNMENT INSTRUCTIONS
OVERVIEW
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 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: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  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.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.ACCT 370
Page 4 of 4