Modern Decision Science (excel)

Graves Investment Company is looking at purchasing an office building in the new West End Office Park. The building has 400,000 square feet. Graves has pulled in an architect who has told them that the building can be subdivided into 4 large suites of 50,000 square feet each and 10 smaller suites of 20,000 square feet each. 2 of the large suites will have outdoor access and 4 of the smaller suites do. The building will cost them 25 million dollars. They will pay 30 percent down and finance the rest with a loan. From discussions with the bank, they know the interest rate on the loan will be either 4 percent, 4.25 percent or 4.5 percent over 20 years compounded monthly. Graves has gathered the data on the following page. Given this data you can use regression to find the monthly rent they can expect to charge per square foot and the added benefit from having outdoor access. Their current economic forecasting projections tell them rents are expected to rise 8 percent per year for the first 8 years and then 5 percent per year for the next 12 years. In addition to the cost of the loan. Graves knows they will pay 10 percent of rent revenues to a property management company. On top of that, they will need to pay 26 percent of rent revenues in overhead, maintenance, taxes, etc. It is your job to see if this deal is a worthwhile one for Graves Investment Co. You should be able to calculate a profit or loss for this investment. You will need to calculate Net Present Value of the total revenues over the next 20 years minus the costs of the building over that same period for all three loan costs. Graves is still calculating their cost of capital so you should do this analysis to see if the investment is profitable using a cost of capital of 5 percent, 6 percent and 7 percent. In your Excel model, you should be able to create one table that shows all 9 possible combinations of loan interest and cost of capital. Prepare a graph to accurately depict those results. Graves Investment Company is looking at purchasing an office building in the new West End Office Park. The building has 400,000 square feet. Graves has pulled in an architect who has told them that the building can be subdivided into 4 large suites of 50,000 square feet each and 10 smaller suites of 20,000 square feet each. 2 of the large suites will have outdoor access and 4 of the smaller suites do. The building will cost them 25 million dollars. They will pay 30 percent down and finance the rest with a loan. From discussions with the bank, they know the interest rate on the loan will be either 4 percent, 4.25 percent or 4.5 percent over 20 years compounded monthly. Graves has gathered the data on the following page. Given this data you can use regression to find the monthly rent they can expect to charge per square foot and the added benefit from having outdoor access. Their current economic forecasting projections tell them rents are expected to rise 8 percent per year for the first 8 years and then 5 percent per year for the next 12 years. In addition to the cost of the loan. Graves knows they will pay 10 percent of rent revenues to a property management company. On top of that, they will need to pay 26 percent of rent revenues in overhead, maintenance, taxes, etc. It is your job to see if this deal is a worthwhile one for Graves Investment Co. You should be able to calculate a profit or loss for this investment. You will need to calculate Net Present Value of the total revenues over the next 20 years minus the costs of the building over that same period for all three loan costs. Graves is still calculating their cost of capital so you should do this analysis to see if the investment is profitable using a cost of capital of 5 percent, 6 percent and 7 percent. In your Excel model, you should be able to create one table that shows all 9 possible combinations of loan interest and cost of capital. Prepare a graph to accurately depict those results