Please see the attached instructions and excel data file. Need by April 11th @ 5pm EST.
Excel – Ratios intessive.
Please see details of assignment to be completed.
Thanks!
Tesla
| Income Statement | All numbers in millions | ||
| year ending 12/31/2022 | year ending 12/31/2021 | ||
| Total Revenue | 81,462 | 53,823 | |
| Cost of Revenue | 60,609 | 40,217 | |
| Gross Profit | 20,853 | 13,606 | |
| Research and Development | 3,075 | 2,593 | |
| Selling, Marketing & Admin | 3,946 | 4,517 | |
| Income from Operations | 13,832 | 6,496 | |
| Other Income or Expense | -219 | 162 | |
| Pretax Income | 13,719 | 6,343 | |
| Tax Provision | 1,132 | 699 | |
| Net Income | 12,583 | 5,519 | |
| Balance Sheet | All numbers in millions | ||
| 12/31/22 | 12/31/21 | 12/31/20 | |
| Cash And Cash Equivalents | 16,253 | 17,576 | 19,384 |
| Short Term Investments | 5,932 | 131 | – 0 |
| Net Receivables | 2,952 | 1,913 | 1,886 |
| Inventory | 12,839 | 5,757 | 4,101 |
| Other Current Assets | 2,941 | – 0 | – 0 |
| Total Current Assets | 40,917 | 27,100 | 26,717 |
| Property Plant and Equipment, net | 36,635 | 31,176 | 23,375 |
| Goodwill | 593 | 1,717 | 520 |
| Other Assets | 4,193 | 2,138 | 1,536 |
| Total Assets | 82,338 | 62,131 | 52,148 |
| Accounts Payable | 21,288 | 14,817 | 8,962 |
| Other Current Liabilities | 5,421 | 4,888 | 5,286 |
| Total Current Liabilities | 26,709 | 19,705 | 14,248 |
| Long Term Debt | 9,731 | 10,843 | 14,221 |
| Other Liabilities | – 0 | – 0 | – 0 |
| Total Liabilities | 36,440 | 30,548 | 28,469 |
| Common Stock | 33,013 | 31,252 | 29,078 |
| Retained Earnings | 12,885 | 331 | (5,399) |
| Other Stockholder Equity | – 0 | – 0 | – 0 |
| Total Stockholder Equity | 45,898 | 31,583 | 23,679 |
| Total Liabilities & Stockholder Equity | 82,338 | 62,131 | 52,148 |
| Liquidity ratios | year ending 12/31/22 | year ending 12/31/21 | |
| current ratio | |||
| quick ratio | |||
| accounts receivable turnover | |||
| average days to collect receivables | |||
| inventory turnover | |||
| average days to sell inventory | |||
| Solvency ratios | |||
| debt to assets ratio | |||
| debt to equity ratio | |||
| Profitability ratios | |||
| net margin | |||
| return on investment | |||
| return on equity | |||
| Stock market ratio | |||
| P/E ratio |
Sheet1
,
Tesla Financial Statement Analysis Case Study
Due date of your case study is 11:59pm, Friday, April 12, 2024. Please submit your case study report on Canvas under “Assignments” then “Case Study” then submit Assignment as a file upload. The submission should include all the required analyses in Excel as well as a written report in Microsoft Word or PDF file format. The case study will comprise 15% of your final grade for the semester. The report will be checked by the built-in software for plagiarism and AI generated content. Quote or cite appropriately. If the text similarity report shows a percentage greater than 10%, your paper will not be accepted for grading (note: Similarity score already excludes quoted content).
Students will:
Use the annual Financial Statements (Income Statement and Balance Sheet) of Tesla from Canvas under “Assignments” then “Case Study” for the two most recent years. Some balance sheet items for 2020 are also needed for calculating averages for ratio analysis;
Calculate trend analysis (horizontal analysis) by comparing 2022 to 2021 for each of the income statement and balance sheet items of Tesla (only show percentage changes such as +5.7% or −12.3%.); Excel formula such as =(B3-C3)/+C3 and etc.
Calculate a common-size (vertical analysis) for each of the income statement and balance sheet items of Tesla for the most recent year (2022); Excel formula such as =B3/81462 and etc.
Calculate all the ratios*** (except Earnings per share) found in Chapter 9 PowerPoint slides under the following headings, for the two most recent years (2022 and 2021):
o Liquidity Ratios (6 ratios; current ratio, quick ratio, accounts receivable turnover, average days to collect receivables, inventory turnover, average days to sell inventory)
o Solvency Ratios (2 ratios; debt to assets ratio, debt to equity ratio) o Profitability Ratios (3 ratios; net margin, return on investment, return on
equity) o Stock Market Ratios (1 ratio; price-earnings ratio)
Prepare a case study report: answer the question (one to two pages) – would you invest in Tesla? Why or why not based upon your calculations [provide sufficient evidence (such as ratios are improving/deteriorating and/or better/worse than the “rule of thumb” standard, etc.) to support your decision].
***Students must show detailed calculations for all the ratios for the most recent year (2022) {e.g., accounts receivable turnover = net credit sales / average accounts receivable = $81,462 / [($2,952 + $1,913) / 2] = 33.49 for 2022} to ensure that they are not simply copying them from a website.
Additional information: 1. Based on the given information of Tesla, we will treat total revenue as sales, net sales and net credit sales; cost of revenue as cost of goods sold; net receivable as accounts receivable. 2. Inventory and assets listed thereafter on the balance sheet are not quick assets while assets listed before inventory are quick assets.
3. Earnings per share (EPS) = $ for 2022 and use the most recent stock market price (available from Yahoo Finance http://finance.yahoo.com/quote/goog/?p=goog) to calculate the P/E ratio for 2022 only and compare it with the current S&P 500 companies’ average P/E ratio of 16.
Your case study report should be completed with 10- to 12-point font, 1-inch margins on each side.
Format of the report: Title Page
Introduction (a short paragraph about what your case study is about) Analyses (horizontal, vertical and ratio) Decision/Conclusion