Morehouse Investments Inc. is planning to buy and develop the Rufus M. Rose house located in the SoNo neighborhood of Atlanta. It is a Victorian style house built in 1901 and is the last residence in the neighborhood. Ben Mays, the chairman, believes the house can be developed into apartments for the local doctors and medical professionals who work across the street at Emory University Hospital Midtown, the former Crawford Long Hospital. The current asking price for the house is $1.25 million and he expects to put 25% in a down payment for a 30 year loan. He assumes he would need to pay an extra 18% of the monthly payment per month to cover taxes and insurance. Mays is considering whether this is a good investment to make. From investigating various lending options, he believes the annual interest rate on his loan could be 4.85%, 5%, or 5.15% Having an architect take a quick glance at the building, he has found it is possible to create either 6 apartments (3 that are 2BD/2Ba and 3 that are 1 BD/IBA) or 8 apartments (2 that are 2 BD 2 BA and 6 that are I BDI BA). Data on the next page shows the cost of apartments in various neighborhoods. A model can be created to ascertain the average rental cost of an apartment in a normal market as related to three variables number of bedrooms, number of bathrooms, and a binary variable that denotes if it is an established neighborhood or up and coming. (When predicting the rent assume the SoNo neighborhood is up and coming so pul al in for that variable.) Also, if he did the 8 apartments, the IBD/IBA apartments would be smaller and would always rent for 10% less. You are also expected to measure the impact the rental market will have on the investment. The model creates a forecast for a normal market. In a good market, the apt rents would be 20% higher and in a poor market, the apt rents would be 15% lower. Mays wants you to create an Excel spreadsheet model demonstrating how all of the options would affect his monthly profit or loss. He should be able to look at the first sheet and clearly see what would happen if each condition mentioned is in place and he should be able to change the inputs to immediately see what the impact would be. (For example, if one of the interest rates were to change to 6%, what would happen?) The calculations should be shown on another sheet but marked in a way that he check them for accuracy if he so chooses. Create a graph showing the monthly profit of the various options. Rent #of Bathrooms #of Bedrooms Up and Coming? Neighborhood Sylvan Hills NoNo Midtown Midtown NoNo Vali Buckhead Buckhead Buckhead Sylvan Hills Midtown Midtown NoNo West Atlanta West Atlanta West Atlanta Vali Va Hi $900 SI100 22 $920 110 $975 S1150 S1400 S1000 SIOSO 500 S850 10 SI 220 21 SI100 $1200 22 1 S1000 -oooo- 950 $875 T L 10 $9402 11 Morehouse Investments Inc. is planning to buy and develop the Rufus M. Rose house located in the SoNo neighborhood of Atlanta. It is a Victorian style house built in 1901 and is the last residence in the neighborhood. Ben Mays, the chairman, believes the house can be developed into apartments for the local doctors and medical professionals who work across the street at Emory University Hospital Midtown, the former Crawford Long Hospital. The current asking price for the house is $1.25 million and he expects to put 25% in a down payment for a 30 year loan. He assumes he would need to pay an extra 18% of the monthly payment per month to cover taxes and insurance. Mays is considering whether this is a good investment to make. From investigating various lending options, he believes the annual interest rate on his loan could be 4.85%, 5%, or 5.15% Having an architect take a quick glance at the building, he has found it is possible to create either 6 apartments (3 that are 2BD/2Ba and 3 that are 1 BD/IBA) or 8 apartments (2 that are 2 BD 2 BA and 6 that are I BDI BA). Data on the next page shows the cost of apartments in various neighborhoods. A model can be created to ascertain the average rental cost of an apartment in a normal market as related to three variables number of bedrooms, number of bathrooms, and a binary variable that denotes if it is an established neighborhood or up and coming. (When predicting the rent assume the SoNo neighborhood is up and coming so pul al in for that variable.) Also, if he did the 8 apartments, the IBD/IBA apartments would be smaller and would always rent for 10% less. You are also expected to measure the impact the rental market will have on the investment. The model creates a forecast for a normal market. In a good market, the apt rents would be 20% higher and in a poor market, the apt rents would be 15% lower. Mays wants you to create an Excel spreadsheet model demonstrating how all of the options would affect his monthly profit or loss. He should be able to look at the first sheet and clearly see what would happen if each condition mentioned is in place and he should be able to change the inputs to immediately see what the impact would be. (For example, if one of the interest rates were to change to 6%, what would happen?) The calculations should be shown on another sheet but marked in a way that he check them for accuracy if he so chooses. Create a graph showing the monthly profit of the various options. Rent #of Bathrooms #of Bedrooms Up and Coming? Neighborhood Sylvan Hills NoNo Midtown Midtown NoNo Vali Buckhead Buckhead Buckhead Sylvan Hills Midtown Midtown NoNo West Atlanta West Atlanta West Atlanta Vali Va Hi $900 SI100 22 $920 110 $975 S1150 S1400 S1000 SIOSO 500 S850 10 SI 220 21 SI100 $1200 22 1 S1000 -oooo- 950 $875 T L 10 $9402 11