Final Exam – Bayou City Real Estate Investment (150 Points) Mr

Subject:AccountingPrice:18.99 Bought3

Share With

Final Exam – Bayou City Real Estate Investment (150 Points)

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.9918 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 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
2019	$1,176	8.59%	39.92%	102,593.00	$305,959	$245,000	56.99	3.8
2018	$1,150	9.49%	39.70%	98,323.00	$298,982	$237,500	64.94	4.4
2017	$1,091	9.73%	39.26%	94,818.00	$291,340	$229,900	50.80	5.0
2016	$1,084	7.28%	40.83%	91,530.00	$283,133	$221,000	43.29	5.3
2015	$1,069	6.46%	41.33%	88,764.00	$280,290	$212,000	48.66	4.6
2014	$1,020	7.13%	40.94%	91,439.00	$270,182	$199,000	93.17	5.0
2013	$964	8.39%	39.87%	88,080.00	$248,591	$180,000	97.98	6.1
2012	$956	10.17%	38.65%	74,116.00	$225,330	$164,500	94.05	6.6
2011	$941	11.64%	38.44%	63,606.00	$213,723	$155,000	94.88	8.1
2010	$961	13.76%	37.16%	61,005.00	$211,765	$153,990	79.48	8.3
2009	$984	12.27%	37.74%	63,803.00	$203,626	$153,000	61.95	7.5
2008	$971	12.55%	36.63%	69,336.00	$208,266	$152,000	99.67	4.8
2007	$924	13.57%	36.12%	83,736.00	$206,393	$152,000	72.34	4.3
2006	$913	10.91%	36.52%	87,574.00	$198,410	$149,079	66.05	5.1

 

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 (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.