You will be given, during unit 5, a problem where an individual is trying to make a decision in a business/investment environment. The problem will have multiple variables that have different levels (

DA&M Individual Project

Morehouse Investments Inc. is planning to buy and develop the Rufus M. Rose house located in the SoHo 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.5 million and he expects to put 20% in a down payment for a 30 year loan. He assumes he would need to pay an extra 17% of the monthly payment per month to cover taxes and insurance. Assume it costs an additional 10% per month for utilities/maintenance/overhead. 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 3.0%, 3.15%, or 3.25%.

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/1BA) or 8 apartments (2 that are 2 BD/2 BA and 6 that are 1 BD/1 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 SoHo neighborhood is up and coming so put a 1 in for that variable.) Also, if he did the 8 apartments, the 1 BD/ 1BA 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 (layout, interest rates, apt market) 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.

You should submit the Excel model for the investment in digital campus.