CIS 418
Due Thursday, May 7 @ 6:00 PM
In the Excel file ¡°Final_Spring2020_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.
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, I 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 or from 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 #
Region
1002
24
2
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
$ (450,032) $ 3,795,556 $ 2,419,985 $ 1,375,571
$ (469,384) $ 3,958,765 $ 2,460,439 $ 1,498,326 $ 487,096 $ 1,011,230
$ 1,011,230
$ (489,379) $ 4,127,408 $ 2,522,414 $ 1,604,994 $ 461,679 $ 1,143,316
$ 1,143,316
$ (506,508) $ 4,271,867 $ 2,615,658 $ 1,656,209 $ 477,837 $ 1,178,371
$ 1,178,371
$ (525,907) $ 4,435,480 $ 2,647,399 $ 1,788,081 $ 545,753 $ 1,242,328
$ 1,242,328
$ (545,313) $ 4,599,149 $ 2,705,284 $ 1,893,865 $ 565,891 $ 1,327,974
$ 26,025,846 $ 27,353,820 6.7%
$ 4,245,588
Year 1
$ 4,428,148
$ 4,616,787
Year 3
$ 4,778,375
Year 4
$ 4,961,387
Year 5
$ 5,144,462
Year 6
Building Class
B
Number of Apartments
171
West
Year 1 Monthly Rent
Rent CAGR
Expense CAGR
Equity Value Growth
$ 2,069
$ $
467,015
908,556 (24,536,790)
Vacancy Rate
10.6% 57.0%
Operating Expense Ratio
Equity Reversion
3.9% Total BTCF
-1.6% Before-tax IRR
6.1%
$ (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
1002
1002
1002
1002
1017
1017
1017
1017
1017
1017
1024
1024
1024
1024
1024
1024
Year
1
24 $ 4,245,588
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
Location ID
Building ID
31
31
31
31
31
31
41
41
41
41
41
41
2
2 24
2
2
2
2
24
24
24
24
20
20
20
20
20
20
6
6
6
6
6
6
Potential Annual Gross Rental Income
$ 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
$ (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)
$ 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
$ 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
Net Operating Income
$ 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
$ 456,548
$ 429,666
$ 389,121
$ 338,935
$ 312,627
$ 275,170
Interest & Depreciation
$ 487,096
$ 461,679
$ 477,837
$ 545,753
$ 565,891
$ 441,556
$ 417,672
$ 349,307
$ 407,589
$ 472,939
$ 493,323
$ 318,051
$ 258,402
$ 284,042
$ 307,322
$ 277,455
$ 301,783
EBT (Before Tax Cash Flow)
1002
1002
$ (450,032)
Effective Gross Income
$ 3,795,556
Operating Expenses
$ 2,419,985
$ 1,375,571
$ 467,015
$ 908,556
$ 1,011,230
$ 1,143,316
$ 1,178,371
$ 1,242,328
$ 1,327,974
$ 568,403
$ 674,264
$ 849,263
$ 883,175
$ 933,681
$ 1,063,001
$ 138,497
$ 171,263
$ 105,080
$ 31,613
$ 35,171
$ (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
(23,628,234)
1,011,230
1,143,316
1,178,371
1,242,328
27,353,820
(22,890,140)
674,264
849,263
883,175
933,681
25,625,735
(14,167,282)
171,263
105,080
31,613
35,171
13,956,521
Location ID
1 993 2
10
11
12
3
4
5
6
7
8
9
Populatio n (1,000 people)
1439
980
522
1717
1712
1818
732
1385
896
930
434
Median Household Income ($1,000s)
50
72.5
47.7
42.4
65.7
40.5
53.1
28.3
60.9
33.7
54.5
28.2
Region
South West
West
South West
Mid West
North East
West
North East
South
West
South
South West
Mid West
Tab 3
Tab 4
Year 2
Building ID
Building Class
Number of Apartments
1
C
164
2
B
166
3
B
96
4
C
239
5
A
121
6
B
144
7
C
412
8
C
126
9
A
79
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 (one paragraph).
Option 1
Option 2
Option 3
Building Class
A
A
A
Number of Apartments
382
435
267
Region
Mid West
Great Lakes
South West
Population(1,000s people)
2526
927
1875
Median Household Income ($1,000s)
$ 36.1
$ 67.4
$ 42.1
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 (1-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:
Year
RowLabels 123456
Average of Potential Annual Gross Rental Income
Great Lakes Mid West North East South South West West
Average of Monthly Rent/Unit
Great Lakes Mid West North East South South West West
Potential AGR Income Growth YOY
Great Lakes Mid West North East South South West West
Total Average of Potential Annual Gross Rental Income
Total Average of Monthly Rent/Unit
Total Potential AGR Income Growth YOY
$1,946,035 $2,118,604 $5,059,369 $1,794,154 $3,458,685 $4,732,595
$989 $1,028 $2,498 $920 $1,666 $2,189
100.00% 100.00% 100.00% 100.00% 100.00% 100.00%
$3,317,286
$1,602
100.00%
$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.95%
98.39% 101.40% 99.94% 101.71% 103.42%
$3,366,565
$1,629
101.49%
$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.98%
98.41% 101.37% 99.98% 101.74% 103.43%
$3,417,776
$1,657
101.52%
$1,886,876 $2,017,879 $5,273,331 $1,792,124 $3,641,169 $5,236,877
$964
$987 $2,623 $926 $1,766 $2,438
99.00%
98.37% 101.40% 99.97% 101.74% 103.44%
$3,470,923
$1,686
101.56%
$1,868,560 $1,986,494 $5,345,665 $1,792,541 $3,704,564 $5,416,192
$957
$974 $2,666 $929 $1,800 $2,528
99.03%
98.44% 101.37% 100.02% 101.74% 103.42%
$3,525,936
$1,716
101.58%
$1,849,623 $1,954,759 $5,422,454 $1,791,652 $3,770,251 $5,602,584
$949
$960 $2,711 $930 $1,836 $2,621
98.99%
98.40% 101.44% 99.95% 101.77% 103.44%
$3,583,263
$1,747
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.