Task 1: Ratio Computations
Financial statements for Tesla and Toyota are available in the Excel template file on Blackboard.
- Download the Ratio Template workbook from Blackboard. This workbook contains five worksheets: Ratios, Common Balance Sheet, Common Income Statement, Balance Sheets, and Income Statements. The Balance Sheets and Income Statements worksheets contain the financial statements for Tesla and Toyota (5 marks for worksheet construction)
- Create a common size balance sheet and income statement on the “Common Balance Sheet” and “Common Income Statement” worksheets. Every cell on these worksheets should contain a formula referencing appropriate cells within the workbook. (10 marks for computations on the two common size worksheets)
Actual prices may differ, but for the purposes of this assignment assume the price is $50 000
- Complete the “Ratios” worksheet using formulas that reference cells from the Common Balance Sheet and Income Statement worksheets where appropriate. (5 marks for computations on the Ratios worksheet)
When working with the provided template worksheets, do not change the row headings or order, and do not add or delete any rows or columns in the template. Your results need to be in exactly the same row and column as the provided template. Do not change the worksheet (tab) names of the template worksheets.
Note on file types: Preferred file type for submission is Microsoft Excel using Office 2010 or newer (.xlsx). You can work on the file using Google Sheets, and export to excel for submission. If you use a different spreadsheet software, you should convert your file to excel prior to submission.
Name the submitted file “Task1.xlsx”. Blackboard will add your student number to the file name when we download it for marking.
Task 2: Financial Analysis
Answer the following four questions in essay form. Each answer carries the marks listed and specifies an indicative word limit. Make sure you clearly label which question you are answering.
- Compare the financial health of Tesla and Toyota using a range of financial ratio analyses – list their strengths and weaknesses. (100-200 words)
- Discuss each of the ratios in the DuPont Analysis and what these ratios tell us about the financial health of each company. (100-200 words)
- Based on the financial ratios computed in Task 1, and the analysis in question 1 and 2 above, which car manufacturer would you prefer to purchase from and why? Be sure to refer to your analysis in the previous questions and mention at least one critical ratio. (150-300 words)
- What non-financial information might affect your purchasing decision? You should identify at least two non-financial factors. How important are these factors? For this question, you can cite news stories and/or websites. What we are looking for is a demonstration of an appreciation of how both financial factors and non-financial fit into the overall decision. (175-300 words)
Submit your answers to these questions in a single file (either a Word document or a pdf) and name this file “Task2.docx” or “Task2.pdf”. The combined word limit is 1000 words. Although, you may submit a document of fewer words, it is not advisable to be greatly below the limit. The per-question limits are indicative, but the overall 1000-word limit is a hard limit.
Task 3: Finance Analysis
Once the cars are ready for delivery, you will need to finance the purchase. You have decided to use a car loan with a variable rate, monthly principal and interest payments over 5 years. (N.B. there are other loan types such as fixed rate loans that might actually be a better choice under some circumstances. However, for ease of comparison in this assignment we will only consider standard variable rate loans.) With a standard variable rate loan, the bank has the right to change the interest rate charged on the loan as the level of interest rates changes in the economy. This can lead to an increase or decrease in the interest rate the bank charges you. When interest rates are low, it is prudent to ensure that you can afford an increase in rates. For the purpose of this assignment, we will be assuming that the rate will remain constant over the life of the loan. In the real world, your monthly payment is set on the assumption that the rate will not change. Then, when rates change, your monthly payment is re-computed using the new rate and remaining term of your mortgage.
- Search for a standard variable rate car loans from two different banks: Bank Australia and National Australia Bank. For each loan you should submit BOTH a screenshot of the rate from the website of the lending institution (not from a comparison site), and a pdf downloaded from the institution’s website detailing the fees charged on standard car loans. Be sure to note the URL and date of access for both your screenshot and the fee statement.
- You will need $35,000 to purchase the car. Increase the loan amount so that after paying any up-front loan fees your net loan proceeds will be $35,000. Compute your monthly loan payment including any monthly loan fees. For example, if the lender charges fees of $200 up-front and $5 per month, you will need to borrow $35,200 and add $5 to the computed monthly payment on the loan. Be sure to include all computations – see the submission instructions below.
- Choose one of the two car loans based entirely on the numbers reported above. Explain the theory behind your decision criteria. (no more than 100 words)
- What other factors (besides the interest rate and fees) would you consider in determining which lender to use? You should identify at least two additional factors. For this question, you can cite news stories and/or websites. What we are looking for is a demonstration of an appreciation of how non-interest rate factors might fit in to your choice of lender (no more than 200 words).