QUESTION 2 [20 Marks] The Pines Cottage is a luxury holiday cottage set in the heart of pretty Burford in Oxfordshire. It was established by Harry and Megan over ten years ago. This property has fifteen self- contained cottages, each nestled in peaceful, isolated surroundings. The occupancy rate is one important metric for determining the profitability of an accommodation business. The occupancy rate is the percentage of available cottages that are actually rented. The occupancy rate of The Pines Cottage is significantly higher than the region's average. However, they realized that three of their cottages have lower occupancy rates than the rest of their cottages. These three cottages were built when Harry and Megan first established The Pines Cottage and are not as luxurious as the others. They want to upgrade these cottages with new paint, spa tubs, new kitchen appliances and larger flatscreen televisions. Harry can undertake most of the renovations himself and estimates that each cottage will cost $20,900. The renovations will take a total of one month. The Pines Cottage currently charges a nightly tariff of $360 for each of these original cottages. The tariff will be increased to $510 once it has been refurbished. Harry and Megan believe that borrowing money from a bank and repaying the debt with the revenues generated by the tariff increase from these three cottages is the best strategy to pay for the renovations. Bank X is offering a business loan with an annual interest rate of 6.38% compounded monthly. Harry and Megan choose to repay the debt in 24 months. When the cottages are not available for rent in the first month, they will use their accumulated cash reserves to service the debt. (i) The three cottages are expected to have an 18.8% occupancy rate once renovated. Calculate the total monthly revenue generated by the tariff increase from all three renovated cottages. This is the estimated repayment amount. Assume that a month is defined as 30 days. (ii) Use the amortization 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? (iii) Use an amortization schedule in Excel to analyze 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? (iv) Harry and Megan realize that they would need to charge more than $510 per cottage per night in order to repay the loan as quickly as they want to. What is the minimum nightly tariff 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 18.8%. (10 marks) (b) The laundry appliances at The Pines Cottage are outdated and ready to be replaced. Harry and Megan would like to purchase a commercial washer for $7688 and a commercial dryer for $7230. The total saving in water and electricity consumption obtained by using these more efficient appliances is estimated to be $855 per quarter. Both the washer and dryer require minor servicing every six months, estimated to cost $600 in total each time. Major services are required every two years and will cost $2500 in total each time in addition to the regular minor services scheduled to be carried out at the same time. They expect the machines to last eight years, after which time they will be resold for $6000 in total. Maintenance will not be carried out at the end of eight years, immediately prior to selling the machines. The new appliances will not be used 24 hours a day. Thus, it will be possible to allow guests to use the laundry facilities for a small fee. They estimate the quarterly income to be $450. In order to analyze whether the new laundry appliances are viable, a Net Present Value calculation is required. Assume that capital is valued at 14.7% per annum, compounded quarterly. Prepare an Excel spreadsheet like the one shown below. Not all time periods are shown - you will need to use the appropriate number of these. Not all cell values are correct - you will need to insert the correct values. You will need to enter the formula for the Net Present Value in a suitable cell. Comment on the outcome of the Net Present Value calculation. Does this seem to be a viable option? 16 17 Period Income Expenditure Cash Flow 18 0 $ Cost of Capital (per annum, compounded xxxx) 19 1 $ 400.00 $ 20 2 Net Present Value 5xx.yy $ 400.00 $ $400.00 $ 21 3 $ 5,000.00 $5,000.00 250.00 $150.00 250.00 $150.00 250.00 $150.00 250.00 $ 150.00 250.00 $ 150.00 250.00 $ 150.00 22 4 $ 400.00 $ 23 5 $ 400.00 $ 24 6 $ 400.00 $ 25 IF www. He 26 I IN 27 in 1 28 29 30 Insert the correct values and 31 period accordingly (4 marks) This should contain the NPV formula (c) Harry and Megan have plans to build a caf on site at The Pines Cottage in six years' time. They expect the cost of building to be $516,000, and the fittings (tables, chairs, fridges and so on) to cost $55,000. Their consultant at the bank has advised that the maximum Harry and Megan will be permitted to borrow is $370,000, meaning that in order to build the caf, they will need to have saved the remaining amount required. Harry and Megan therefore decide to set up a bank account as a sinking fund. They plan to make payments of $2100 per month into the account, which offers 6.9% per annum, compounded monthly. Excel Instructions: Prepare an Excel spreadsheet like the one shown below for each question (i), (ii) and (iii) (not all values shown are correct. you will have to insert the correct ones). A B 1 Sinking Fund 2(c) 2 3 Monthly Payment (PMT) XX 4 Periodic Interest (i) XX 5 Number of Periods (N) 6 Accumulated Amount (A) XX 8 9 Insert the correct values/formula 10 accordingly 11 (1) Calculate the size of the deposit that Harry and Megan need to save. Using Excel, implement the future value of an annuity formula to determine the balance of their sinking fund at the given interest rate of 6.9% per annum after six years if the monthly payments are $2100. By how much will Harry and Megan fall short of their goal? Present the answer in the Excel spreadsheet. (ii) Using Excel, implement the sinking fund formula to compute the size of the monthly payments that Harry and Megan should make in order to save the required deposit amount in six years. (iii) Harry and Megan would prefer to use the original payment amount of $2100 and decide to shop around at other financial institutions for an interest rate that would enable the desired amount to be achieved. Using the Excel Tool Goal Seek (instructions below), determine the appropriate annual interest rate (to four significant figures) for an investment account offering monthly compounding with the monthly payment of $2100 used in part (i). Excel Instructions: Copy the part of your Excel spreadsheet that you used for part (i) to a new section of the spreadsheet and label it (iii). Use Goal Seek to find the required monthly interest rate. In Excel 2007 and 2010, Goal Seek can be found by following: Data Forecast What-If Analysis. In Excel 2003 it is simply Tools Goal Seek. Some versions of Excel on the Mac in- ' -nt Goal Seek and some do not. You may use the Help(?) button 6/7 explanation. 7 QUESTION 2 [20 Marks] The Pines Cottage is a luxury holiday cottage set in the heart of pretty Burford in Oxfordshire. It was established by Harry and Megan over ten years ago. This property has fifteen self- contained cottages, each nestled in peaceful, isolated surroundings. The occupancy rate is one important metric for determining the profitability of an accommodation business. The occupancy rate is the percentage of available cottages that are actually rented. The occupancy rate of The Pines Cottage is significantly higher than the region's average. However, they realized that three of their cottages have lower occupancy rates than the rest of their cottages. These three cottages were built when Harry and Megan first established The Pines Cottage and are not as luxurious as the others. They want to upgrade these cottages with new paint, spa tubs, new kitchen appliances and larger flatscreen televisions. Harry can undertake most of the renovations himself and estimates that each cottage will cost $20,900. The renovations will take a total of one month. The Pines Cottage currently charges a nightly tariff of $360 for each of these original cottages. The tariff will be increased to $510 once it has been refurbished. Harry and Megan believe that borrowing money from a bank and repaying the debt with the revenues generated by the tariff increase from these three cottages is the best strategy to pay for the renovations. Bank X is offering a business loan with an annual interest rate of 6.38% compounded monthly. Harry and Megan choose to repay the debt in 24 months. When the cottages are not available for rent in the first month, they will use their accumulated cash reserves to service the debt. (i) The three cottages are expected to have an 18.8% occupancy rate once renovated. Calculate the total monthly revenue generated by the tariff increase from all three renovated cottages. This is the estimated repayment amount. Assume that a month is defined as 30 days. (ii) Use the amortization 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? (iii) Use an amortization schedule in Excel to analyze 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? (iv) Harry and Megan realize that they would need to charge more than $510 per cottage per night in order to repay the loan as quickly as they want to. What is the minimum nightly tariff 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 18.8%. (10 marks) (b) The laundry appliances at The Pines Cottage are outdated and ready to be replaced. Harry and Megan would like to purchase a commercial washer for $7688 and a commercial dryer for $7230. The total saving in water and electricity consumption obtained by using these more efficient appliances is estimated to be $855 per quarter. Both the washer and dryer require minor servicing every six months, estimated to cost $600 in total each time. Major services are required every two years and will cost $2500 in total each time in addition to the regular minor services scheduled to be carried out at the same time. They expect the machines to last eight years, after which time they will be resold for $6000 in total. Maintenance will not be carried out at the end of eight years, immediately prior to selling the machines. The new appliances will not be used 24 hours a day. Thus, it will be possible to allow guests to use the laundry facilities for a small fee. They estimate the quarterly income to be $450. In order to analyze whether the new laundry appliances are viable, a Net Present Value calculation is required. Assume that capital is valued at 14.7% per annum, compounded quarterly. Prepare an Excel spreadsheet like the one shown below. Not all time periods are shown - you will need to use the appropriate number of these. Not all cell values are correct - you will need to insert the correct values. You will need to enter the formula for the Net Present Value in a suitable cell. Comment on the outcome of the Net Present Value calculation. Does this seem to be a viable option? 16 17 Period Income Expenditure Cash Flow 18 0 $ Cost of Capital (per annum, compounded xxxx) 19 1 $ 400.00 $ 20 2 Net Present Value 5xx.yy $ 400.00 $ $400.00 $ 21 3 $ 5,000.00 $5,000.00 250.00 $150.00 250.00 $150.00 250.00 $150.00 250.00 $ 150.00 250.00 $ 150.00 250.00 $ 150.00 22 4 $ 400.00 $ 23 5 $ 400.00 $ 24 6 $ 400.00 $ 25 IF www. He 26 I IN 27 in 1 28 29 30 Insert the correct values and 31 period accordingly (4 marks) This should contain the NPV formula (c) Harry and Megan have plans to build a caf on site at The Pines Cottage in six years' time. They expect the cost of building to be $516,000, and the fittings (tables, chairs, fridges and so on) to cost $55,000. Their consultant at the bank has advised that the maximum Harry and Megan will be permitted to borrow is $370,000, meaning that in order to build the caf, they will need to have saved the remaining amount required. Harry and Megan therefore decide to set up a bank account as a sinking fund. They plan to make payments of $2100 per month into the account, which offers 6.9% per annum, compounded monthly. Excel Instructions: Prepare an Excel spreadsheet like the one shown below for each question (i), (ii) and (iii) (not all values shown are correct. you will have to insert the correct ones). A B 1 Sinking Fund 2(c) 2 3 Monthly Payment (PMT) XX 4 Periodic Interest (i) XX 5 Number of Periods (N) 6 Accumulated Amount (A) XX 8 9 Insert the correct values/formula 10 accordingly 11 (1) Calculate the size of the deposit that Harry and Megan need to save. Using Excel, implement the future value of an annuity formula to determine the balance of their sinking fund at the given interest rate of 6.9% per annum after six years if the monthly payments are $2100. By how much will Harry and Megan fall short of their goal? Present the answer in the Excel spreadsheet. (ii) Using Excel, implement the sinking fund formula to compute the size of the monthly payments that Harry and Megan should make in order to save the required deposit amount in six years. (iii) Harry and Megan would prefer to use the original payment amount of $2100 and decide to shop around at other financial institutions for an interest rate that would enable the desired amount to be achieved. Using the Excel Tool Goal Seek (instructions below), determine the appropriate annual interest rate (to four significant figures) for an investment account offering monthly compounding with the monthly payment of $2100 used in part (i). Excel Instructions: Copy the part of your Excel spreadsheet that you used for part (i) to a new section of the spreadsheet and label it (iii). Use Goal Seek to find the required monthly interest rate. In Excel 2007 and 2010, Goal Seek can be found by following: Data Forecast What-If Analysis. In Excel 2003 it is simply Tools Goal Seek. Some versions of Excel on the Mac in- ' -nt Goal Seek and some do not. You may use the Help(?) button 6/7 explanation. 7