代写CIS 468、代写Java，Python编程

日期：2022-05-07 10:43

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%