ALL Answers to be shown using excel tables & formulas

Business is bad. One of the coach operators is down-sizing. It has offered to sell one of its coaches to Merlion Holiday for a mere $180,000. This vehicle costed $250,000 some 2 years ago. Its useful commercial lifespan (due to COE in Singapore) is now 8 years. Annual depreciation is $20,000 and it can be sold for scrap at $15,000 at the end of 8 years. The coach is in excellent condition. No repair is required, and it can be deployed immediately. Merlion Holiday will only need to hire a driver and pay for diesel and related maintenance expenses. Driver salary and vehicle expenses per month are $2,500 and $900, respectively

(a) Merlion Holiday is interested. However, cashflow is tight. It will need a bank loan to cover the entire purchase price. Jack wants to know how much monthly repayment is required if the company were to take up a loan for any period between 2 to 4 years. He has noted that the prevailing interest rates vary with loan tenure as shown in Table 4 below. He intends to pay off the loan at the end of the loan period in a lump sum using the proceeds from selling off the coach.

Interest Rates Table
Tenure (years)	Rate
0	1.8%
2	1.8%
3	2.0%
4	2.4%
5	3.0%

Table 4: Prevailing Interest rates

Compose a model to analyse the bank loan needed to finance the purchase of the coach. What is the monthly repayment if the loan tenure is 2 years? State any assumptions needed by your model. (8 Marks)

(b) Jack expects the current COVID19 situation to last between 2 and 4 years. He wants to determine how the monthly repayment and interest rate will change for loan tenures between 2 and 4 years in steps of 0.5 year (ie 6 months). Construct a Data Table to show these values. Apply a suitable colour-scale to the monthly repayment figures. Enhance your model to automatically determine the lowest repayment amount and the corresponding loan tenure and interest rate. What would these values be if the coach price was reduced to $175,000? (8 Marks)

(c) Indicate whether Merlion Holiday should buy the coach? Justify your answer quantitatively. (8 Marks)