calculating various KPIs the blue shaded cells, such as vacancy rate, growth rates for rent, expenses, profitability

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

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

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 on the next page 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 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. 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.  

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme