DA&M Individual Project - Spring 2022 John Hope is considering buying cabin (named Enotah) in the Blue Ridge Mountains to use as an investment property. The 2 bedroom/1 bath cabin (with a hot tub) is priced at $180,000. He plans to use a property manager to oversee the property, advertise it, handle all customers and simply pay him a portion of the proceeds. Dr. Hope plans to put 10% down and finance it for 20 years. He explored various loan options and found that his loan rate would be 3.5%. Other costs he expects over the year would be: HOA $200 per quarter Electricity $130 per month Internet/Cable $80 per month Propane Gas $500 per year + $72 Annual tank Rental fee Property Taxes and Insurance 15% of the mortgage payment Pest Control $167 per quarter Overall Operations and Maintenance 12% of revenue per month Dr. Hope has found a property manager to consider. GA Mtn Cabins takes 35% of all revenue, (GA Mtn Cabins also charges booking fees and cleaning fees to the customer but this does not need to enter into your calculations.) To get a sense of revenues, Dr. Hope has gathered the data on the second page of this assignment that predicts rent per night based on # of bedrooms, # of bathrooms and the presence of a hot tub. By running a regression model, you can predict the rent he can charge per night. Assume for January through March, his forecasted rent is 20% lower than normal. During Sept - December, assume it is 15% higher due to the fall leaves and the holidays. At the moment, he assumes a 60% occupancy rate over the year (so 60% of the nights will be rented). (Do not forget to consider that different months have a different # of days.) You need to create a model that shows Dr. Hope his income per month for a full year and then the total for the year. (Keep in mind that Dr. Hope does not necessarily expect to make a profit. He expects the value of the cabin to appreciate.) You should create an Excel spreadsheet model to be submitted by end of day on Wednesday, April 13, 2022 via BlackBoard, DA&M Individual Project - Spring 2022 John Hope is considering buying cabin (named Enotah) in the Blue Ridge Mountains to use as an investment property. The 2 bedroom/1 bath cabin (with a hot tub) is priced at $180,000. He plans to use a property manager to oversee the property, advertise it, handle all customers and simply pay him a portion of the proceeds. Dr. Hope plans to put 10% down and finance it for 20 years. He explored various loan options and found that his loan rate would be 3.5%. Other costs he expects over the year would be: HOA $200 per quarter Electricity $130 per month Internet/Cable $80 per month Propane Gas $500 per year + $72 Annual tank Rental fee Property Taxes and Insurance 15% of the mortgage payment Pest Control $167 per quarter Overall Operations and Maintenance 12% of revenue per month Dr. Hope has found a property manager to consider. GA Mtn Cabins takes 35% of all revenue, (GA Mtn Cabins also charges booking fees and cleaning fees to the customer but this does not need to enter into your calculations.) To get a sense of revenues, Dr. Hope has gathered the data on the second page of this assignment that predicts rent per night based on # of bedrooms, # of bathrooms and the presence of a hot tub. By running a regression model, you can predict the rent he can charge per night. Assume for January through March, his forecasted rent is 20% lower than normal. During Sept - December, assume it is 15% higher due to the fall leaves and the holidays. At the moment, he assumes a 60% occupancy rate over the year (so 60% of the nights will be rented). (Do not forget to consider that different months have a different # of days.) You need to create a model that shows Dr. Hope his income per month for a full year and then the total for the year. (Keep in mind that Dr. Hope does not necessarily expect to make a profit. He expects the value of the cabin to appreciate.) You should create an Excel spreadsheet model to be submitted by end of day on Wednesday, April 13, 2022 via BlackBoard