Due Friday, May 6 @ noon ET
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.
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, a past semester’s data or analysis, 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.
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.
Tab 3 Tab 4
Unit Number 1002 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6
Location # 2 Potential Annual Gross Rental Income 4,245,588$ 4,428,148$ 4,616,787$ 4,778,375$ 4,961,387$ 5,144,462$
Building # 24 Lost Rent Allowance (450,032)$ (469,384)$ (489,379)$ (506,508)$ (525,907)$ (545,313)$
Building Class B Effective Gross Income 3,795,556$ 3,958,765$ 4,127,408$ 4,271,867$ 4,435,480$ 4,599,149$
Number of Apartments 171 Operating Expenses 2,419,985$ 2,460,439$ 2,522,414$ 2,615,658$ 2,647,399$ 2,705,284$
Region West Net Operating Income 1,375,571$ 1,498,326$ 1,604,994$ 1,656,209$ 1,788,081$ 1,893,865$
Interest & Depreciation 467,015$ 487,096$ 461,679$ 477,837$ 545,753$ 565,891$
Year 1 Monthly Rent 2,069$ EBT (Before Tax Cash Flow) 908,556$ 1,011,230$ 1,143,316$ 1,178,371$ 1,242,328$ 1,327,974$
Vacancy Rate 10.6% Initial Equity (24,536,790)
Operating Expense Ratio 57.0% Equity Reversion 26,025,846$
Rent CAGR 3.9% Total BTCF (23,628,234)$ 1,011,230$ 1,143,316$ 1,178,371$ 1,242,328$ 27,353,820$
Expense CAGR -1.6% Before-tax IRR 6.7%
Equity Value Growth 6.1%
Unit
Number Year
Location
ID
Building
ID
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 Total BTCF
Location
ID
Populatio
n (1,000
people)
Median
Household
Income
($1,000s) Region
1 993 50 South West
2 1439 72.5 West
3 980 47.7 South West
4 522 42.4 Mid West
5 1717 65.7 North East
6 1712 40.5 West
7 1818 53.1 North East
8 732 28.3 South
9 1385 60.9 West
10 896 33.7 South
11 930 54.5 South West
12 434 28.2 Mid West
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. (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).
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.
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$
Examples for Question 1:
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.
Total Potential AGR Income Growth YOY 100.00% 101.49% 101.52% 101.56% 101.58% 101.63%