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.
Copyright By PowCoder代写 加微信 powcoder
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.
Unit Number
Location #
Building #
Building Class
Number of Apartments
Year 1 Monthly Rent
Expense CAGR Equity Value Growth
Effective Gross Income Operating Expenses Net Operating Income Interest & Depreciation
$ 2,069 EBT (Before Tax Cash Flow) Initial Equity
Equity Reversion 3.9% Total BTCF
-1.6% Before-tax IRR 6.1%
$ (450,032) $ 3,795,556 $ 2,419,985 $ 1,375,571 $ 467,015 $ 908,556
(24,536,790) $ (23,628,234)
$ (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%
Vacancy Rate
10.6% 57.0%
Operating Expense Ratio
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
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
$ 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
EBT (Before Tax Cash Flow)
24 $ 4,245,588
Net Operating Income
$ 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
(23,628,234)
27,353,820
(22,890,140)
25,625,735
(14,167,282)
13,956,521
Building ID Building Class
Potential Annual Gross Rental Income
Lost Rent Allowance
South West
North East
North East
South West
$ 4,245,588
$ 4,428,148
$ 4,616,787
$ 4,778,375
Number of Apartments
$ 4,961,387
$ 5,144,462
Populatio n (1,000 people)
Median Household Income ($1,000s)
South 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).
Building Class
Number of Apartments
Great Lakes
Population(1,000s people)
Median Household Income ($1,000s)
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:
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
$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%
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%
$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