Background Information Your boss, a real estate business manager, has approached you for financial advice. She is interested in either purchasing or leasing a new car for her personal use. Aware of your financial expertise, she has asked you to develop a Spreadsheet Model that allows her to decide whether to buy or lease the vehicle. The retail price of the car she is interested in is $50,000. Buy Scenario In the Buy Scenario, your boss would like to purchase the car by making an initial down payment of $15,000 dollars and finance the difference with a conventional car loan to be repaid monthly for 3- years at a 5% interest rate. The following table summarises the relevant information for the Buy Scenario. Buy Scenario Car Price $ 50,000.00 Down Payment $ 15,000.00 Interest Rate 5% Term 3 years Lease Scenario In the Lease Scenario, there is no initial down payment. Instead, your boss would like to use a Finance Lease to rent the car for 3 years. At the end of this 3-year period, she plans to purchase the car from the lease financier (lessor) by paying a residual value of $25,000. In this scenario, to rent the car, your boss would have to pay a monthly rent of $850 for 3 years. The following table summarises the relevant information for the Lease Scenario. Lease Scenario Car Price $50,000.00 Residual Value $ 25,000.00 Monthly Rent $850 Term 3 years Note: A Finance Lease is a common way people can use of a car without actually buying it. Under a Finance Lease, the car belongs to the financier (lessor) who rents it out to the borrower (lessee) in exchange for monthly instalments. At the end of the lease term, the lessee has the option to claim ownership of the car by paying a residual value. Question 1 - [30 marks] a. [10 marks] Lay out the decision-making problem, the alternatives, and the overall criteria you would use to evaluate the different alternatives. b. [10 marks] Carefully establish all the inputs and assumptions you would include in the Spreadsheet Model for each scenario. If you include inputs/variables other than the ones provided (e.g. interest rate on savings), justify your choices based on data from the Australian market. c. [10 marks] Establish your model's output. What would you like your model to be able to generate in order to assess the alternatives? Note: The word limit for this question is 500 words. Question 2-[40 marks] a. [20 marks] Based on your answers to Question 1, build a Spreadsheet Model which helps your boss decide whether to buy or lease the vehicle. Make your spreadsheet self-explanatory. b. [10 marks] Perform What If analysis for at least one of your inputs (e.g. down payment). That is , show what would happen to your model's output at, at least, three different values of the chosen input. c. [10 marks] In your spreadsheet, highlight the section you would present to your boss to help her with her decision-making problem. Note: The Spreadsheet Model must be submitted as an Excel file along with your written answers for the other two questions. This question will be marked solely on the basis of the Excel file submitted. There is no need to submit written answers for this question. Question 3 [30 marks] a. [10 marks] Of all the inputs you would include in your model, which one do you think would be the most important in determining whether buying or leasing is the best option for your boss? Provide an explanation. b. [10 marks] Describe the model's limitations and/or aspects that could be improved. What other factors haven't been considered? c. [10 marks] Are there any cognitive biases you would suggest your boss to be aware of when finally making her decision? Note: The word limit for this question is 500 words. Background Information Your boss, a real estate business manager, has approached you for financial advice. She is interested in either purchasing or leasing a new car for her personal use. Aware of your financial expertise, she has asked you to develop a Spreadsheet Model that allows her to decide whether to buy or lease the vehicle. The retail price of the car she is interested in is $50,000. Buy Scenario In the Buy Scenario, your boss would like to purchase the car by making an initial down payment of $15,000 dollars and finance the difference with a conventional car loan to be repaid monthly for 3- years at a 5% interest rate. The following table summarises the relevant information for the Buy Scenario. Buy Scenario Car Price $ 50,000.00 Down Payment $ 15,000.00 Interest Rate 5% Term 3 years Lease Scenario In the Lease Scenario, there is no initial down payment. Instead, your boss would like to use a Finance Lease to rent the car for 3 years. At the end of this 3-year period, she plans to purchase the car from the lease financier (lessor) by paying a residual value of $25,000. In this scenario, to rent the car, your boss would have to pay a monthly rent of $850 for 3 years. The following table summarises the relevant information for the Lease Scenario. Lease Scenario Car Price $50,000.00 Residual Value $ 25,000.00 Monthly Rent $850 Term 3 years Note: A Finance Lease is a common way people can use of a car without actually buying it. Under a Finance Lease, the car belongs to the financier (lessor) who rents it out to the borrower (lessee) in exchange for monthly instalments. At the end of the lease term, the lessee has the option to claim ownership of the car by paying a residual value. Question 1 - [30 marks] a. [10 marks] Lay out the decision-making problem, the alternatives, and the overall criteria you would use to evaluate the different alternatives. b. [10 marks] Carefully establish all the inputs and assumptions you would include in the Spreadsheet Model for each scenario. If you include inputs/variables other than the ones provided (e.g. interest rate on savings), justify your choices based on data from the Australian market. c. [10 marks] Establish your model's output. What would you like your model to be able to generate in order to assess the alternatives? Note: The word limit for this question is 500 words. Question 2-[40 marks] a. [20 marks] Based on your answers to Question 1, build a Spreadsheet Model which helps your boss decide whether to buy or lease the vehicle. Make your spreadsheet self-explanatory. b. [10 marks] Perform What If analysis for at least one of your inputs (e.g. down payment). That is , show what would happen to your model's output at, at least, three different values of the chosen input. c. [10 marks] In your spreadsheet, highlight the section you would present to your boss to help her with her decision-making problem. Note: The Spreadsheet Model must be submitted as an Excel file along with your written answers for the other two questions. This question will be marked solely on the basis of the Excel file submitted. There is no need to submit written answers for this question. Question 3 [30 marks] a. [10 marks] Of all the inputs you would include in your model, which one do you think would be the most important in determining whether buying or leasing is the best option for your boss? Provide an explanation. b. [10 marks] Describe the model's limitations and/or aspects that could be improved. What other factors haven't been considered? c. [10 marks] Are there any cognitive biases you would suggest your boss to be aware of when finally making her decision? Note: The word limit for this question is 500 words