CIS 468
Due Friday, March 5 @ midnight
In the Excel file ¡°Midterm_SpringA2021_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.
You may work in groups of up to 3 people for this exam. Only one copy of the completed work needs to be submitted on Blackboard. Please make sure all group members¡¯ names are clearly indicated on your submission.
Academic Integrity: 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 (or your group) submits should be work done you or someone in your group. Any work submitted that is a copy of something from an external reference or from another student or group¡¯s project will be considered a violation of the university¡¯s academic integrity policy.
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 CIS468.
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
10
11
12
4
5
6
7
8
9
1002
24
171
2
B
522
1717
1712
1818
732
1385
896
930
434
42.4
65.7
40.5
53.1
28.3
60.9
33.7
54.5
28.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
Year 2
$ 4,616,787
Year 3
$ 4,778,375
Year 4
$ 4,961,387
Year 5
$ 5,144,462
Year 6
Region
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
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
1002
1002
1017
1017
1017
1017
1017
1017
1024
1024
1024
1024
1024
1024
Year
2
3
4
5
6
1
2
3
4
5
6
1
2
3
4
5
6
Location
ID
Building
2
31
31
31
31
31
31
41
41
41
41
41
41
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
$ (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
Net Operating Income
$ 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
$ 456,548
$ 429,666
$ 389,121
$ 338,935
$ 312,627
$ 275,170
Interest & Depreciation
$ 467,015
$ 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)
1
ID
2 24
2
24 $ 4,245,588
$ 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
Tab 3
Tab 4
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
Location
ID
1 993
2
3
1439
980
50
72.5
47.7
Equity Reversion
Populatio n (1,000 people)
Median Household Income ($1,000s)
Region
South West
West
South West
Mid West
North East
West
North East
South
West
South
South West
Mid West
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. (25 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
200
350
250
Region
Mid West
Great Lakes
West
Population(1,000s people)
800
1200
1500
Median Household Income ($1,000s)
$ 40.0
$ 50.0
$ 55.0
3. (40 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.