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 (such as a Good, Fair or Poor market) or that can change (such as length of a loan). You are expected to create an Excel model that allows the individual to see the implications of the various values that the variables can take regarding the decision to be made. (For example, what happens to the profit to be made if the loan length goes from 10 years to 12.)
A good Excel model (and this will be discussed in unit 5):
Has the correct answer for the various calculations.
Has a distinct input and output section making the model easy to read and follow.
Has an output section that allows the individual to assess how the output varies across various levels of the variable under consideration. (In other words, if you are given information about three types of markets, the individual should be able to see the results in all three markets rather than having to input each one individually.)
Has cell references in the formulas so that a change can be input and the model reacts immediately to that change without having to go into the various cell references.
Considers all necessary data to make the decision.
Has clear labels that makes it easy to find the information and calculations. Calculations may be shown in text to demonstrate what is occurring in the cells.
This project will be assigned at the end of unit 5 and is to be submitted within 24 hours of unit 7 Live session. The model must be turned in as an Excel file and not as a PDF. The instructor will test the model to see if the model is built to reflect changes to various parameters.
Here’s the information.
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. Neighborhood Rent # of Bathrooms # of Bedrooms Up and Coming? Sylvan Hills $830 NoNo $900 Midtown $1100 Midtown $920 NoNo $975 VaHi $1150 Buckhead $1400 Buckhead $1000 Buckhead $1050 Sylvan Hills $500 Midtown $850 Midtown $1220 NoNo $1100 West Atlanta $1200 West Atlanta $1000 West Atlanta $950 VaHi $875 Va Hi $940