please also give explanation from the calculation
Problem 1 - Renovating at The Sullivan Habitat Occupancy rate is one useful measure of the profitability of an accommodation venture. The occupancy rate is the proportion of the number of available cottages that are actually rented out. The occupancy rate at The Sullivan Habitat is well above the average for the region. However, Liz has noticed that the occupancy rates for three of the cottages are lower than the rest of their cottages. These three cottages were built when Roger and Liz first started The Sullivan Habitat and are not as luxurious as the others. Roger and Liz would like to upgrade these cottages with fresh paint, spa baths, new kitchen appliances and larger flatscreen TVs. Roger is able to do most of the renovations himself and estimates that renovations for each cottage will cost $18,300. The renovations will take one month in total. Currently, The Sullivan Habitat charges a nightly tariff of $325 for each of these original cottages. Once refurbished, the tariff will increase to $425. Liz thinks the best way to pay for the renovations is to borrow the money from the bank, and use the funds generated from the tariff increase from these three cottages to repay the loan. The bank is offering a business loan of 9.75% per annum compounded monthly. Roger and Liz would like to repay the loan within 24 months. In the first month, when the cottages are not available to be rented out, Roger and Liz will instead use their accumulated cash reserves to service the loan. (a) (2 marks) Once refurbished, the three cottages are expected to have an occupancy rate of 25%. Calculate the combined monthly funds generated from the tariff increase from all three refurbished cottages. (Use 30 days as the length of a month.) This is the planned repayment amount. (b) (5 marks) Use the amortisation formula to calculate the monthly repayment amount needed to pay off the renovations in 24 months. What is the total interest paid on the loan? Is the planned repayment amount sufficient to repay the loan? () (4 marks) Use an amortisation schedule in Excel (you can base it on the one used in Week 3 lectures) to analyse the outcome of this loan with repayments as calculated in (a). How many months will it take to pay off the loan? How much is still outstanding at the end of 24 months? Provide a printout of your Excel spreadsheet in this Appendix. Excel Instructions: Put a descriptive heading on your amortisation schedule that includes your username, for example sitio01 Amortisation schedule for renovation loan. In addition, each column heading should begin with your initials, e.g. AA Payment number, (d) (2 marks) Roger and Liz realise that they would need to charge more than $425 per cottage per per cottage required so that the proceeds of the tariff increase will repay the loan in 24 months? Assume that the occupancy rate will still be 25%
