Financial Accounting ACG2022 Excel Final Project

The following tabs contain the financials of two real companies that operate in the same industry. I have tweaked the financials a little bit to make parts of them easier to read. Please click on each tab below to see each section. (You can move the tabs around to group financials together, if this is easier for you). I will reveal the companies at the end of the project.

You must complete the following for this case:

1. 1 Looking at the financials, determine if these companies are in the service industry, retail industry or manufacturing industry.
   What account(s) did you use to make your determination?
2. 2 Before you run any calculations, review the earnings and cash for each company and state which one you think is performing better.
   What is the drawback of only evaluating each company based on its reported net earnings a nd cash amounts.
3. 3 a. On what two financial statements do you find the ending “Cash and Cash Equivalents” balance? b. On what two financial statements do you find the earnings for the period?
4. 4 Prepare a horizontal trend analysis for the THREE most recent years for each company (I have included the information for FOUR years because you need the information from year four to determine the change for year 3.
5. 5 What does your trend analysis indicate about the performance of the company? Are each company’s assets, liabilities, equity and expenses increasing or decreasing from year to year? Write a brief explanation (about 3-5 sentences) about your findings.

1. 6 Prepare a vertical analysis for each company for the first three years of financials and determine:
   1. After Cost of Sales, what is each company’s largest expense? Did this expense increase/decrease relative to sales each year?
   2. What percentage of each company’s assets are financed by debt? (To determine this,divide the total liabilities by total assets).
      Which stakeholder(s) would be concerned about a company’s debt levels? Why? (Your answermust be 1-2 sentences long)

   c. What was each company’s trend in current assets to total assets over the three years? Did itincrease/ decrease, vary or remain the same?
   d. Look at each company’s Cost of Sales relative to its Sales. Is the proportion of COGS remainingstable relative to Sales? If not, what could be the cause for the increase/ decrease? Provide at least two possible explanations for your answer.

2. 7 Calculate the following ratios for the first THREE years for each company. You MUST show all calculations. You will not receive credit for answers submitted without calculations. State your ratios to two decimal points. Your turnover ratios only need to be stated to one decimal point.(These ratios all come from chapter 13 of your textbook). Note: For ratios that require an average, you must take the beginning and ending balance for each year and divide by 2. You do not addtogether all three years and divide by 3).
   1. Current ratio
   2. Inventory turnover
   3. Days in Inventory
   4. Accounts Receivable turnover (Company A only)
   5. Average Collection Period

1. Debt-to-Assets Ratio (you can just reuse your calculations from 5a above for this)
2. Times Interest Earned
3. Free cash flow

1. i Return on common stockholders’ equity
2. j Return on asset
3. k Profit Margin
4. l Asset turnover

j Gross profit rate

1. 8 Define the terms a) liquidity, b) solvency and c) profitability.
   Using the ratio analysis above, determine which company you believe is more a) liquid, b) solvent and c) profitable.You MUST state which ratio(s) you used in determining your answer.
2. 9 Look at the industry average ratios. Based on your analysis and the industry ratio information, which company do you think would be the better investment?Your answer should be at least 3-5 sentences long.Note: The financials dated 2020 show the performance for year 2019, the financials dated 2019 show the performance for year 2018, etc….)

10 Look at the “Notes to the Financials” for each company and answer the following:

a. What method does each company use to value its inventories?
b. What depreciation method does each company use for its Property, Plant and Equipment? c. How does Company B manage its receivables? How does this benefit the company?
d. When, specifically, does each company test its Goodwill for impairment. (You must state the

specific quarter).

e. Look at the Notes to the Financials for either company? What recently-adopted pronouncement required leases to now be included on the Balance Sheet?