See guidelines, Excel spreadsheet should be completed using TSLA1QTR2021 and also 2 pages that include Financial Qtr Comparison, Comparison Analysis, and Short-Term Financing
Overview In preparation for your report in Project Two, you will need to calculate the financial ratios needed to determine your chosen business’s current financial health. Once you’ve calculated these ratios, the results will be used to analyze the business’s current financial position and help them make decisions about how to improve or maintain their financial health. Pay particular attention to working capital management. If liquidity is an issue, consider how the company will meet its short-term obligations. Prompt Using the Project Two Financial Formulas spreadsheet and the balance sheet, income statement, and cash flow statement from Mergent Online for your chosen company, calculate the financial ratios for the most recent fiscal quarter. Then, compare those ratios with the same ratios for the same fiscal quarter from one year prior in Mergent Online, and analyze your results. Specifically, you must address the following rubric criteria: Financial Calculations: Calculate accurate financial ratios to assess the business’s current financial health. Specifically, calculate the following ratios: Working capital Current ratio Debt ratio Earnings per share Price/earnings ratio Total asset turnover ratio Financial leverage Net profit margin Return on assets Return on equity Fiscal Quarter Comparison: Using Mergent Online, summarize the differences between the results from your financial calculations of the most recent fiscal quarter and the results of the same financial calculations of the same fiscal quarter from a year before for your chosen business. For example, if the most recent fiscal quarter available is the 3rd quarter in 2020, then you will compare those results to the same financial calculations from the 3rd quarter in 2019. Comparison Analysis: Explain what the results of your calculations and your comparison indicate about the business’s current financial health, providing examples to support your explanation. You might consider the following questions: Do the results indicate the business is financially healthy or financially unhealthy? Which results indicate this? What might be the cause(s) of the business’s financial success or failure? Is more information needed to determine the business’s financial health? If so, which pieces of information might still be needed? Short-Term Financing: Explain how potential short-term financing sources could help the business raise needed funds to improve its financial health. Base your response on the business’s current financial information. The following resources can help you complete this milestone: Video: Mergent Online: Public Company Financials (4:46) Watch this video from the Shapiro Library to learn more about how to access and use Mergent Online. Shapiro Library FAQ: How Do I Cite a Company Profile From Mergent Online? Use this resource to help answer any questions you have about citing from Mergent Online. Guidelines for Submission Your submission should be a 2 to 3 page Word document with 12-point Times New Roman font, double spacing, and one-inch margins. You must also submit the Project Two Financial Formulas spreadsheet (which you will fill in completely as part of your Project Two submission) and the Excel files for your downloaded balance sheet, income statement, and cash flow statement from Mergent Online. Project Two Milestone Two Rubric CriteriaExemplary (100%)Proficient (85%)Needs Improvement (55%)Not Evident (0%)Value Financial CalculationsN/ACalculates accurate financial ratios to assess the business’s current financial health (100%)Shows progress toward proficiency, but with errors or omissions; areas for improvement may include calculating more accurate financial ratios to assess the business’s current financial health (85%)Does not attempt criterion (0%)21 Fiscal Quarter ComparisonExceeds proficiency in an exceptionally clear, insightful, sophisticated, or creative manner (100%)Summarizes the differences between the results from the financial calculations of the most recent fiscal quarter and the results of the same financial calculations of the same fiscal quarter from a year before for the chosen business (85%)Shows progress toward proficiency, but with errors or omissions; areas for improvement may include summarizing more differences between the results from the financial calculations of the most recent fiscal quarter and the results of the same financial calculations of the same fiscal quarter from a year before for the chosen business (55%)Does not attempt criterion (0%)21 Comparison AnalysisExceeds proficiency in an exceptionally clear, insightful, sophisticated, or creative manner (100%)Explains what the results of the calculations and the comparison indicate about the business’s current financial health, providing examples to support the explanation (85%)Shows progress toward proficiency, but with errors or omissions; areas for improvement may include explaining in more detail what the results of the calculations and the comparison indicate about the business’s current financial health or providing more relevant examples to support the explanation (55%)Does not attempt criterion (0%)22 Short-Term FinancingExceeds proficiency in an exceptionally clear, insightful, sophisticated, or creative manner (100%)Explains how potential short-term financing sources could help the business raise needed funds to improve its financial health and bases the response on the business’s current financial information (85%)Shows progress toward proficiency, but with errors or omissions; areas for improvement may include explaining in more detail how potential short-term financing sources could help the business raise needed funds to improve its financial health, or basing the response on the business’s current financial information (55%)Does not attempt criterion (0%)21 Articulation of ResponseExceeds proficiency in an exceptionally clear, insightful, sophisticated, or creative manner (100%)Clearly conveys meaning with correct grammar, sentence structure, and spelling, demonstrating an understanding of audience and purpose (85%)Shows progress toward proficiency, but with errors in grammar, sentence structure, and spelling, negatively impacting readability (55%)Submission has critical errors in grammar, sentence structure, and spelling, preventing understanding of ideas (0%)10 Citations and AttributionsUses citations for ideas requiring attribution, with few or no minor errors (100%)Uses citations for ideas requiring attribution, with consistent minor errors (85%)rUses citations for ideas requiring attribution, with major errors (55%)Does not use citations for ideas requiring attribution (0%)5 Total:100% SKM_658e21060114020 Monthly Time Value of Money - Monthly Compounding Rate of ReturnYear 1 Initial InvestmentMonth123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Year 2 Month123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Year 3 Month123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Year 4 Month123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Year 5 Month123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Year 6 Month123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Year 7 Month123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Year 8 Month123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Year 9 Month123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Year 10 Month123456789101112 Interest$0$0$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0$0$0 Annual Time Value of Money - Annual Compounding Rate of ReturnYear 12345678910 Initial InvestmentInterest$0$0$0$0$0$0$0$0$0$0 Investment Value$0$0$0$0$0$0$0$0$0$0 PV Time Value of Money - Present Value Annuity Number of Years Rate of Return$0.00 Payment FV Time Value of Money - Future Value Annuity Number of Years Rate of Return$0.00 Payment PV - Lump Sum Time Value of Money - Present Value of Lump Sum Rate Years$0.00 Initial Investment FV - Lump Sum Time Value of Money - Future Value of Lump Sum Rate Years$0.00 Initial Investment NPV Net Present Value (NPV) Calculator Building Initial InvestmentYear12345678910 Annual Cash InflowsCash Flows$0$0$0$0$0$0$0$0$0$0 Discount RateNPV = $0Year11121314151617181920 Number of YearsCash Flows$0$0$0$0$0$0$0$0$0$0 Salvage Value Equipment Initial InvestmentYear12345678910 Annual Cash InflowsCash Flows$0$0$0$0$0$0$0$0$0$0 Discount RateNPV = $0Year11121314151617181920 Number of YearsCash Flows$0$0$0$0$0$0$0$0$0$0 Salvage Value Bonds Initial Investment Annual Cash Inflows Discount RateNPV = $0Year12345678910 Number of YearsCash Flows$0$0$0$0$0$0$0$0$0$0 Principal Returned RATIOS ACCOUNTING & FINANCIAL RATIOS CURRENT RATIO (Current Assets / Current Liabilities)TOTAL ASSET TURNOVER RATIO (Total Revenue / Total Assets) Current AssetsTotal Revenue Current LiabilitiesERROR:#DIV/0!Total AssetsERROR:#DIV/0! WORKING CAPITAL (Current Assets - Current Liabilities) : *Note to students: Be mindful of the scale being used in Mergent Online when filling this out. If a number is written as 12.53, that does not mean the total for that item is $12.53. There could be numerous zeros written after it, depending on the scale labeled above. In this example, 12.53 is actually $12,530,000. (To delete this comment, right-click on the "WORKING CAPITAL" box, then select Delete Comment from the drop-down menu.) FINANCIAL LEVERAGE (Total Assets / Shareholder's Equity) Current AssetsTotal Assets Current Liabilities0Shareholder's EquityERROR:#DIV/0! DEBT RATIO (Total Debt / Total Assets)NET PROFIT MARGIN (Net Income / Total Revenue) Total DebtNet Income Total AssetsERROR:#DIV/0!Total RevenueERROR:#DIV/0! EARNINGS PER SHARE (Net Income / Weighted Average Common Shares Outstanding)RETURN ON ASSETS (Net Income / Total Assets) Net IncomeNet Income Shares OutstandingERROR:#DIV/0!Total AssetsERROR:#DIV/0! PRICE EARNINGS RATIO (Share Price (end of quarter / EPS)RETURN ON EQUITY (Net Income - Preferred Dividends / Shareholder's Equity) Stock PriceNI - Pref. Div. EPSERROR:#DIV/0!Shareholder's EquityERROR:#DIV/0! Untitled Spreadsheet BalanceSheet Powered by Clearbit Tesla Inc (NMS: TSLA) Exchange rate used is that of the Year End reported date Standardized Quarterly Balance Sheet Report Date03/31/2021 1st Quarter CurrencyUSD Audit StatusNot Qualified ConsolidatedYes ScaleThousands Cash & Equivalents17,141,000.00 Cash & Equivs & ST Investments17,141,000.00 Receivables (ST)1,890,000.00 Inventories4,132,000.00 Other Current Assets1,542,000.00 Total Current Assets24,705,000.00 Gross Property Plant & Equip19,257,000.00 Accumulated Depreciation5,389,000.00 Net Property Plant & Equip13,868,000.00 Receivables (LT)- Intangible Assets505,000.00 Other Assets13,894,000.00 Total Assets52,972,000.00 Accounts Payable & Accrued Exps10,139,000.00 Accounts Payable6,648,000.00 Accrued Expenses3,491,000.00 Current Debt1,819,000.00 Other Current Liabilities2,919,000.00 Total Current Liabilities14,877,000.00 LT Debt & Leases9,053,000.00 Deferred LT Liabilities1,438,000.00 Minority Interests1,448,000.00 Other Liabilities3,139,000.00 Total Liabilities29,955,000.00 Common Share Capital1,000.00 Additional Paid-In Capital27,623,000.00 Retained Earnings(4,750,000.00) Accum Other Comprehensive Income143,000.00 Other Equity- 0 Total Equity23,017,000.00 Total Liabilities & Equity52,972,000.00 IncomeStatement Powered by Clearbit Tesla Inc (NMS: TSLA) Exchange rate used is that of the Year End reported date Standardized Quarterly Income Statement Report Date03/31/2021 1st Quarter CurrencyUSD Audit StatusNot Qualified ConsolidatedYes ScaleThousands Sales Revenue8,705,000.00 Other Revenue1,684,000.00 Total Revenue10,389,000.00 Direct Costs8,174,000.00 Gross Profit2,215,000.00 Selling General & Admin1,056,000.00 Research & Development666,000.00 Restruct Remediation & Impair(101,000.00) Other Operating Expense- 0 Total Indirect Operating Costs1,621,000.00 Operating Income594,000.00 Interest Income(89,000.00) Other Non-Operating Income28,000.00 Total Non-Operating Income(61,000.00) Earnings Before Tax533,000.00 Taxation69,000.00 Minority Interests26,000.00 Extraordinary Items- 0 Accounting Changes- 0 Net Income438,000.00 Preference Dividends & Similar- 0 Net Income to Common438,000.00 Average Shares Basic961,000.00 EPS Net Basic0.46 EPS Continuing Basic0.46 Average Shares Diluted1,133,000.00 EPS Net Diluted0.39 EPS Continuing Diluted0.39 Shares Outstanding963,000.00 CashFlow Powered by Clearbit Tesla Inc (NMS: TSLA) Exchange rate used is that of the Year End reported date Standardized Quarterly Cumulative CF Report Date03/31/2021 1st Quarter CurrencyUSD Audit StatusNot Qualified ConsolidatedYes ScaleThousands Net Income (Cumu)464,000.00 Adjustments from Inc to Cash (Cumu)1,189,000.00 Change in Working Capital (Cumu)(12,000.00) Cash Flow from Operations (Cumu)1,641,000.00 Purchase of Pty Plant & Equip (Cumu)(1,360,000.00) Change in Business Activities (Cumu)- Other Investing Cash Flows (Cumu)(1,222,000.00) Cash Flow from Investing (Cumu)(2,582,000.00) Change in LT Debt (Cumu)(1,162,000.00) Change in Equity (Cumu)183,000.00 Payment of Dividends (Cumu)(32,000.00) Other Financing Cash Flows (Cumu)(5,000.00) Cash Flow from Financing (Cumu)(1,016,000.00) Effect of Exchange Rate (Cumu)(221,000.00) Change in Cash (Cumu)(2,178,000.00) Opening Cash (Cumu)19,901,000.00 Closing Cash17,723,000.00 Depn & Amortn (CF) (Cumu)621,000.00 Net Purch of Pty Plant & Equip (Cumu)(1,360,000.00) Untitled Spreadsheet Balance Sheet Powered by Clearbit Tesla