MBA 6211 Managerial Decision Making (excel)

MBA6211 – Managerial Decision Making Final Exam – Bayou City Real Estate Investment (150 Points)

Iqbal Latheef© 2021

Mr. Aristotle is a Vice President at Bayou City Real Estate Investment Trust (REIT) and he has presented a

proposal to the board to consider an investment of $200 million in the Houston market. To support his

recommendation, Mr. Aristotle had a forecast model developed for the Houston rental market. Venus, a

Financial Analyst at Bayou City, presented a regression model to forecast the average rent in the Houston

market with an R-square of 0.9956 and said, “We can confidently invest in the Houston market because we have

a ‘perfect’ model to predict future rents.” The REIT’s board wants to conduct further analysis and has hired your

Consulting team to evaluate the proposal and Venus’ model.

Venus used a number of predictive variables in her regression model. She included Vacancy Rate (percentage of

rental properties that are vacant) and Renter Fraction (percentage of renting households as a fraction of total

households). She also included home sales data like the median and average home sales price and number of

single-family home sales. Lastly, she included the WTI crude price and unemployment rate bringing the total to

seven (7) predictor variables. Table 1 shows the data set used by Venus to develop her multiple regression

model.

TABLE 1: Houston Rental Market Data

Year Average Rent

Vacancy Rate

Renter Fraction

Total property

sales

Average Home Sales

Price

Home Median Sales Price

WTI_Crude Price

Unemployment Rate

2017 $1,091 9.73% 39.26% 94,818 $291,340 $229,900 50.8 5.8

2016 $1,084 7.28% 40.83% 91,530 $283,133 $221,000 43.29 4.8

2015 $1,069 6.46% 41.33% 88,764 $280,290 $212,000 48.66 4.6

2014 $1,020 7.13% 40.94% 91,439 $270,182 $199,000 93.17 5.5

2013 $964 8.39% 39.87% 88,080 $248,591 $180,000 97.98 6.6

2012 $956 10.17% 38.65% 74,116 $225,330 $164,500 94.05 7.2

2011 $941 11.64% 38.44% 63,606 $213,723 $155,000 94.88 8.3

2010 $961 13.76% 37.16% 61,005 $211,765 $153,990 79.48 8.7

2009 $984 12.27% 37.74% 63,803 $203,626 $153,000 61.95 6.2

2008 $971 12.55% 36.63% 69,336 $208,266 $152,000 99.67 4.5

2007 $924 13.57% 36.12% 83,736 $206,393 $152,000 72.34 4.6

2006 $913 10.91% 36.52% 87,574 $198,410 $149,079 66.05 5.7

The Bayou City board was concerned about the predictive nature of the variables chosen by Venus and whether

they were truly independent. They also question why some of the variables were considered good predictors of

Houston rents. Venus was confident because her model had an excellent R-square and the F-statistic was well

above the 4.0 required to be considered a good model. Venus’ regression results are shown in Table 2. Mr.

Aristotle was initially happy with the model, but he started to waver under the questioning of some of the board

members. He thought an independent evaluation would help determine if the model was as good as it looked

and whether they could predict Houston rents effectively.

TABLE 2: Venus’ Regression Model

The Bayou City board made several specific requests of your team to help them assess the model and make a

decision on a significant investment in the Houston rental market.

Questions:

1. Review the regression output in Table 2 and provide your critique of the results. What are your

concerns about Venus’ model? (20 pts)

2. Use the data in Table 1 to recreate the regression results in Table 2. (15 pts)

3. Develop a correlation matrix for the data in Table 1. Comment on the values in your matrix and whether

there are any concerns in using these variables in Venus’ multiple regression model. (20 pts)

4. Based on your correlation results, what one (1) variable regression model would give you the best model

from among the seven (7) parameters chosen by Venus? Build a 1-variable regression model with this

variable, write out your equation, and comment on the results. (30 pts)

5. Perform a step-wise regression to reduce the number of independent variables and produce a final

regression model. Write out the forecast equation for your final model. (45 pts)

6. Now that you have a final model, present a pitch as to why your model is better than Venus’ model and

whether Bayou City should use your model to invest in the Houston market. (20 pts)

SUBMISSION:

Upload ONE SOLUTION (i.e., an Excel file) per team using the Turnitin Link on Blackboard. List the names of all

team members. Clearly show your answers…don’t make me guess or assume I know which cell has the

answer.

SUMMARY OUTPUT

Regression Statistics

Multiple R 0.9956

R Square 0.9911

Adjusted R Square 0.9756

Standard Error 9.6431

Observations 12

ANOVA

df SS MS F Significance F

Regression 7 41561.7112 5937.3873 63.8505 0.0006

Residual 4 371.9555 92.9889

Total 11 41933.6667

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0%

Intercept 1162.7786 541.9787 2.1454 0.0985 -341.9956 2667.5527 -341.9956

Vac_Rate -458.8594 729.5177 -0.6290 0.5635 -2484.3254 1566.6065 -2484.3254

Renter_Frac -693.1631 1322.9335 -0.5240 0.6280 -4366.2154 2979.8893 -4366.2154

Tot_Propty_Sale -0.0031 0.0008 -4.0714 0.0152 -0.0052 -0.0010 -0.0052

Avg_Home_Sale_Price -0.0001 0.0016 -0.0890 0.9334 -0.0047 0.0044 -0.0047

Med_Home_Sale_Price 0.0028 0.0017 1.6591 0.1724 -0.0019 0.0075 -0.0019

WTI -0.1933 0.3364 -0.5748 0.5962 -1.1273 0.7406 -1.1273

UnEmp Rate -9.6479 2.9464 -3.2745 0.0307 -17.8284 -1.4675 -17.8284