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 (50 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 Di 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 Cabin Rent/Night #of Bedrooms 1 2 # of Bathrooms 1 1 1 2 2 Hot Tub? 1 1 OP 150 180 1 2 1 2 1 O O O Full Moon Dancing Bear Eagles View Mudpuddle Rivers Edge Rising Fawn Ohoopee Oconee's Rest Blue Ridge Escape Mountain Top Valley View Peace in the Valley Rhododendron Forest Deer Run 1 $ $ $ $ $ $ $ $ $ $ $ $ NNNNNN 125 220 200 135 195 240 170 155 160 205 2 0 1 0 2 1 1 1 1 1 1 2 2. IN 0 N 1 1 1 $ 195 2 N 1 $ 245 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 (50 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 Di 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 Cabin Rent/Night #of Bedrooms 1 2 # of Bathrooms 1 1 1 2 2 Hot Tub? 1 1 OP 150 180 1 2 1 2 1 O O O Full Moon Dancing Bear Eagles View Mudpuddle Rivers Edge Rising Fawn Ohoopee Oconee's Rest Blue Ridge Escape Mountain Top Valley View Peace in the Valley Rhododendron Forest Deer Run 1 $ $ $ $ $ $ $ $ $ $ $ $ NNNNNN 125 220 200 135 195 240 170 155 160 205 2 0 1 0 2 1 1 1 1 1 1 2 2. IN 0 N 1 1 1 $ 195 2 N 1 $ 245