GBA 211 Final
Due Wednesday, May 4 @ Noon
In the Excel file ¡°Final_SPring2022_CREdata.xls¡±, you will find raw data related to a firm which invests in commercial real estate development (CRE). Your task is to create an analysis package for this firm based on the given data.
The following two pages describe the data available and the analysis for you to prepare.
Copyright By PowCoder代写 加微信 powcoder
Academic Integrity: Please copy and sign the University of Rochester Honor Pledge below and submit along with your completed final. Since this is a take home exam, you are welcome to consult the class materials on BlackBoard as well as the internet and other texts as references. All work that you submit should be your own. Any work submitted that is a copy of something from an external reference, a past semester¡¯s data or analysis, or another student¡¯s project will be considered a violation of the university¡¯s academic integrity policy.
¡°I affirm that I will not give or receive any unauthorized help on this exam, and that all work will be my own.¡±
Grading Rubric: Your completed work will be evaluated using the rubric below. I encourage you to use your creativity and other business skills (communication, presentation, critical thinking) in addition to the Excel modeling techniques that we have studied this semester in GBA 211.
A A-/B+ B/B- C
The completed project demonstrates both technical competence as well as critical and creative analytical skill. The assumptions made, the methodology and the results are communicated and visualized clearly and effectively.
All required parts of the project are complete and technically correct (with possibly a few minors issues)
Some required parts of the project are missing and/or there are more significant errors
The project has large portions missing and/or major conceptual errors.
The Excel file contains:
Tab 1: Individual Unit Mode. A model for calculating various KPIs (the blue shaded cells), such as vacancy rate, growth rates for rent and expenses and profitability measured by IRR (Internal Rate of Return), for a single investment project. The project involves purchasing equity in an apartment building, operating the property for a 6 year ¡°hold¡± period during which operating income is received, measured by EBT (Earnings Before Taxes), and then selling the building and recovering the equity and any capital gains on the appreciation of the property value. The cells shaded blue and gray contain formulas which you might find helpful in understanding the model.
Unit Number
Location #
Building #
Building Class
Number of Apartments
Potential Annual Gross Rental Income
Lost Rent Allowance
Effective Gross Income Operating Expenses
Net Operating Income Interest & Depreciation
EBT (Before Tax Cash Flow) Initial Equity
Equity Reversion
$ 4,245,588
$ (450,032) $ 3,795,556 $ 2,419,985 $ 1,375,571
$ 4,428,148
$ (469,384) $ 3,958,765 $ 2,460,439 $ 1,498,326 $ 487,096 $ 1,011,230
$ 1,011,230
$ 4,616,787
$ (489,379) $ 4,127,408 $ 2,522,414 $ 1,604,994 $ 461,679 $ 1,143,316
$ 1,143,316
$ 4,778,375
$ (506,508) $ 4,271,867 $ 2,615,658 $ 1,656,209 $ 477,837 $ 1,178,371
$ 1,178,371
$ 4,961,387
$ (525,907) $ 4,435,480 $ 2,647,399 $ 1,788,081 $ 545,753 $ 1,242,328
$ 1,242,328
$ 5,144,462
$ (545,313) $ 4,599,149 $ 2,705,284 $ 1,893,865 $ 565,891 $ 1,327,974
$ 26,025,846 $ 27,353,820 6.7%
Year 1 Monthly Rent
Expense CAGR
Equity Value Growth
908,556 (24,536,790)
Vacancy Rate
10.6% 57.0%
Operating Expense Ratio
3.9% Total BTCF -1.6% Before-tax IRR
$ (23,628,234)
Tab 2: A flat data file containing all the figures from the single unit model above for just over 2,000 investment properties. Each property is identified by a unique Unit Number and each has a Location ID and a Building ID, which can be used to reference demographic data in the ¡°Locations¡± and ¡°BuildingTypes¡± tab.
Unit Number
Location Year ID
Potential Building Annual Gross
Rental Income
24 $ 4,245,588
$ 4,428,148
$ 4,616,787
$ 4,778,375
$ 4,961,387
$ 5,144,462
$ 4,014,144
$ 4,176,717
$ 4,366,340
$ 4,528,768
$ 4,729,392
$ 4,933,229
$ 2,891,376
$ 2,871,136
$ 2,840,415
$ 2,793,832
$ 2,774,555
$ 2,743,480
Lost Rent Allowance
$ (450,032)
$ (469,384)
$ (489,379)
$ (506,508)
$ (525,907)
$ (545,313)
$ (354,850)
$ (369,222)
$ (385,984)
$ (400,343)
$ (418,078)
$ (436,097)
$ (989,140)
$ (982,216)
$ (971,706)
$ (955,770)
$ (949,175)
$ (938,544)
Effective Gross Income
$ 3,795,556
$ 3,958,765
$ 4,127,408
$ 4,271,867
$ 4,435,480
$ 4,599,149
$ 3,659,294
$ 3,807,495
$ 3,980,355
$ 4,128,425
$ 4,311,314
$ 4,497,131
$ 1,902,236
$ 1,888,921
$ 1,868,709
$ 1,838,062
$ 1,825,380
$ 1,804,935
Operating Expenses
$ 2,419,985
$ 2,460,439
$ 2,522,414
$ 2,615,658
$ 2,647,399
$ 2,705,284
$ 2,649,335
$ 2,715,559
$ 2,781,785
$ 2,837,660
$ 2,904,694
$ 2,940,808
$ 1,445,688
$ 1,459,255
$ 1,479,588
$ 1,499,128
$ 1,512,753
$ 1,529,765
$ 1,375,571
$ 1,498,326
$ 1,604,994
$ 1,656,209
$ 1,788,081
$ 1,893,865
$ 1,009,959
$ 1,091,936
$ 1,198,570
$ 1,290,764
$ 1,406,620
$ 1,556,324
Interest & Depreciation
$ 1,011,230
$ 1,143,316
$ 1,178,371
$ 1,242,328
$ 1,327,974
$ 1,063,001
$ (26,612)
Initial Equity
(24,536,790)
(23,458,542)
(14,305,778)
Equity Reversion
26,025,845.77
24,562,734.03
13,983,133.20
Total BTCF
Net Operating Income
EBT (Before Tax Cash Flow)
(23,628,234)
27,353,820
(22,890,140)
25,625,735
(14,167,282)
13,956,521
Location ID
Populatio n (1,000 people)
Building ID Building Class
Median Household Income ($1,000s)
South West
South West
North East
North East
South West
Number of Apartments
Your finished project:
1. (20 points) Create at least 2 Pivot Table and at least 2 Charts/Graphs that illustrate descriptive information or a pattern/trend that you think is important to understanding the CRE market and the relative profitability of investments such as these, along with captions (1-3 sentences/bullet points) that describe the data shown. There are many ways to slice and dice the data that you are given. One potential table and one potential bar chart are shown on the next page as examples.
2. (20 points) The company is considering moving forward with one of three potential new investment opportunities, all Class A buildings. Build a model for the estimated profitability of Class A buildings and use it to make a recommendation for which opportunity should be selected. Explain your methodology and conclusion criteria (1-2 paragraphs).
Building Class
Number of Apartments
Great Lakes
Population(1,000s people)
Median Household Income ($1,000s)
3. (45 points) Use the investment opportunity that you selected from the table above and build a simulation model to visualize the estimated distribution of the chosen project¡¯s IRR. Use the data given to decide which parameters should be treated as random variables and to calculate the relevant historical averages for means and standard deviations to use in your simulation. Describe your model, its output and the assumptions that you used to create it (2-3 paragraphs).
Presentation: (15 points) You may put your analysis together using Excel, Word and/or PowerPoint. Remember to consider the clarity and effectiveness of your analysis and its presentation. Quality > Quantity! The more clearly organized and communicated your analysis is, the more effective it will be.
Examples for Question 1:
Average of Potential Annual Gross Rental Income
Great Lakes Mid West North East South South West West
Average of Monthly Rent/Unit
Great Lakes
North East
South $920 South West $1,666 West $2,189
RowLabels 123456
Potential AGR Income Growth YOY
Great Lakes Mid West North East South South West West
100.00% 100.00% 100.00% 100.00% 100.00% 100.00%
$1,946,035 $2,118,604 $5,059,369 $1,794,154 $3,458,685 $4,732,595
$1,925,510 $2,084,569 $5,130,319 $1,793,035 $3,517,772 $4,894,678
$981 $1,014 $2,539 $922 $1,698 $2,269
98.39% 101.40% 99.94% 101.71% 103.42%
$1,905,923 $2,051,379 $5,200,447 $1,792,702 $3,578,919 $5,062,619
$972 $1,000 $2,580 $924 $1,732 $2,352
98.41% 101.37% 99.98% 101.74% 103.43%
$1,886,876 $2,017,879 $5,273,331 $1,792,124 $3,641,169 $5,236,877
$987 $2,623 $926 $1,766 $2,438
98.37% 101.40% 99.97% 101.74% 103.44%
$1,868,560 $1,986,494 $5,345,665 $1,792,541 $3,704,564 $5,416,192
$974 $2,666 $929 $1,800 $2,528
98.44% 101.37% 100.02% 101.74% 103.42%
$1,849,623 $1,954,759 $5,422,454 $1,791,652 $3,770,251 $5,602,584
$960 $2,711 $930 $1,836 $2,621
98.40% 101.44% 99.95% 101.77% 103.44%
$989 $1,028 $2,498
Total Average of Potential Annual Gross Rental Income $3,317,286 $3,366,565 $3,417,776 $3,470,923 $3,525,936 $3,583,263
Total Average of Monthly Rent/Unit $1,602 $1,629 $1,657 $1,686 $1,716 $1,747
Total Potential AGR Income Growth YOY 100.00% 101.49% 101.52% 101.56% 101.58% 101.63%
Exhibit 1: AGR Income, Monthly Rent and AGR Income Year Over Year Growth by Region. Monthly rent levels are highest in the West and lowest in the South and Great Lakes. Annual growth rate for rents is roughly 3.5% in the West and between 1% and 2% annually in the North East and South West, while rents in the Mid West are declining by 1.5% each year.
Exhibit 2: Vacancy Rates by Building Class, Building Size (# of Apts) and City Size (Population). Vacancy rates for all building classes and sizes are highest in cities with fewer than 1,000,000 residents. For small buildings (those with fewer than 100 apartments) the vacancy rate in large cities is roughly 1/3 of the rate in small cities. For large buildings (those with more than 300 apartments), the vacancy rate in large cities is 1/2 that in small cities.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com