Reference no: EM132298915
GBA Project -
In the attached Excel file, 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 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.
Your finished project:
1. 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 in attached file as examples.
2. 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 a CRE investment 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
|
250
|
190
|
375
|
|
Region
|
South
|
West
|
South West
|
|
Population (1,000s people)
|
1800
|
450
|
1875
|
|
Median Household Income ($1,000s)
|
$58.4
|
$70.2
|
$42.1
|
3. 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: 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.
Attachment:- Assignment Files.rar