Attachment_3_PRT_User_Guide.pdf

Property Review Template (PRT) User’s Guide An OPIIS Business Tool

07/15/2015

Contents What’s New in this Release of the Property Review Template (PRT)?

Advantages of Creating a Windows Application (MS Visual Studio) Version of the PRT

Future Tool Maintenance and Enhancements

Requirements Completed with the 2015 Release

Requirements Completed with the 2014 Release

Getting Started

Prerequisite: Must be an OPIIS User

PRT Application Installation

VPN Requirements

Organization of PRT Functionality

Seasoned Property Analysis Overview

Underwriting Analysis Overview

Seasoned Property Analysis

Underwriting Analysis

Sample Approaches to Optimizing PRT Data in Excel

1. Create a Difference Column for Per Unit Income and Expense Data between the Subject and Average of Peer

Data 29

2. Create Trend Lines from Year-Over-Year Operating Data

3. Create a Delta Column between Operating Years to view Absolute Changes in Income and Expense Categories . 29

What’s New in this Release of the Property Review Template (PRT)?

Advantages of Creating a Windows Application (MS Visual Studio) Version of the PRT The principal advantages of converting the PRT from Excel to a Windows application are as follows:

Significant improvement in connectivity to the data mart, which translates into much greater speed with which data is retrieved from data mart into the tool

Significant improvement in usability. Tab lay-out is simpler, more intuitive , faster than radio buttons and worksheet creation process that the Excel version utilized

Significant improvement in overall stability and consistency in performance. Excel version of tool would often halt in the process of bringing up peers. This risk is eliminated by the web application version.

Significant improvement in ability to maintain and improve the tool. Fundamental design of original Excel tool was inefficient and difficult to maintain. Growing the Excel version meant perpetuating and growing the faulty design even more.

Future Tool Maintenance and Enhancements The PRT Windows application was developed through the use of Microsoft Visual Studio, which is standard HUD software.

Enhancements to the tool will continue to be implemented through use of MS Visual Studio.

The PRT Windows application may be enhanced to better support risk management business practices upon application to the OPIIS GTM

Enhanced versions of the tool will be formally submitted to the OCIO for testing and release to production.

Requirements Completed with the 2015 Release

Ability to select multiple counties in which to find peer properties

Ability to select multiple zip codes in which to find peer properties

Requirements Completed with the 2014 Release

Performance (Speed)

Increase the speed at which the Peer Selection and Trend Analysis Complete

Data Connection

Use of the OPIIs Data Mart ‘Mirror’ (and not the production data base) to run PRT analyses. This means that the

PRT will always be operational and will not be interrupted by the Data Mart’s daily update process, during which time the data mart cannot be accessed

Sorting Capability

Added ability to sort by any column for any grid type template.

Enhanced Ability to Export to Multi-Worksheet Excel Workbook

Ability to save data into Excel from any grid type structure

Processing Affirmation

Provide affirmation of process completion or of instructions at the top of screen in RED to guide users through process

Enhanced Peer Selection

Added Zip Code as selection criterion

Added Major Program Type as selection criterion

Automatically populate the Region, Market, and Submarket, MSA, SOA, Major Program Group, and Zip

Disable Criteria not being used in analysis or only criteria to become active once the Peer Checkbox has been selected.

Improved Underwriting Analysis Capability

Created a more streamlined process for comparing Underwriting Proforma with Peer Data

Getting Started

Prerequisite: Must be an OPIIS User

In order to use the PRT application, you must be a registered OPIIS User.

If you are not an OPIIS User, request your Manager to submit a CHAMPS request on your behalf to obtain access to the system.

Request the OPIIS Help Desk to install the PRT on your computer. Follow the instructions below under ‘PRT Application Installation’.

PRT Application Installation Send an email RE: PRT Installation Request to OPIIS@hud.gov

Provide your:

o HID o IP ADDRESS o A window of time in which you will be available

A member of the OPIIS Team will contact you to install the PRT application on your computer via Windows Communicator while you are at your desk

Successful completion of the PRT application will result in the placement of the PRT icon on your desktop:

PRT ICON:

VPN Requirements Windows XP or Above

User Must Know Machines IP Address

VPN access – The PRT is installed on the User’s Desktop Computer. Since it is installed on the Desktop computer special instructions are not required.

mailto:OPIIS@hud.gov

Organization of PRT Functionality The PRT supports two business processes: Seasoned Property Analysis and Underwriting Analysis.

Seasoned Property Analysis Overview This body of functionality supports the benchmarking of existing projects in the HUD Multifamily portfolio against peer (comparable) properties and markets.

The process is constituted of the following steps:

Select property to analyze

Select time period over which to pull:

o Profit and Loss Statements (P&L’s) o Corresponding historical and projected market data for subject property

Export P&L’s and Market data to Excel

For any given operating year:

o Select peer properties or comps based on various criteria o Export data on the peer properties selected o Benchmark the subject property’s (per unit) operating data against the mean/max/min of that of the peer properties selected o Export the operating data comparison between subject property and peers

Seasoned Property Analysis

P R

T A

P P

LI

C

A

TI

O N

C

P R

T_ D

A

TA

Phase

IDENTIFY PROPERTY

BY PROPERTY ID

START

SELECT TIME PERIOD

FOR P&L’S AND

MARKET DATA

YEAR-

OVER-

YEAR

P&L’s

HISTORICAL &

PROJECTED

MARKET

DATA

EXPORT?

EXCEL WORKBOOK

‘PRT_PROPERTY-9

DIGIT PROPERTY ID’

YES

CREATE

SELECT OPERATING

YEAR TO ANALYZE

Worksheet created for each body of data

IDENTIFY PEER

CRITERIA, e.g., MSA, REIS MARKET, SOA, PROGRAM, ZIP, COUNTY

PEER

DATA

EXPORT?

COMPARISON OF

PER UNIT P&L B/W

SUBJECT AND

PEERS

YES

ADD WORKSHEET TO EXISTING WORKBOOK

EXPORT? END

Underwriting Analysis Overview This body of functionality supports the benchmarking of proforma operating statements for new projects that are being underwritten against the HUD Multifamily portfolio. The process is constituted of the following steps:

Enter Proforma (stabilized projected) income and expenses for a new project

For any given operating year:

o Select peer properties or comps based on various criteria o Export data on the peer properties selected o Benchmark the subject property’s (per unit)operating data against the mean/max/min of that of the peer properties selected o Export the operating data comparison between subject and peers

UnderwrIting Analysis

P R

T A

P P

LI

C

A

TI

O N

C

P R

T_ D

A

TA

Phase

ENTER PROPERTY

PROFORMA INCOME

& EXPENSES

START

EXCEL WORKBOOK

‘PRT_PROPERTY-

PROJECT NAME’’

YES

CREATE

SELECT OPERATING

YEAR TO ANALYZE

Worksheet created for each body of data

IDENTIFY PEER

CRITERIA, e.g., MSA, REIS MARKET, SOA, PROGRAM, ZIP, COUNTY

PEER

DATA

EXPORT?

COMPARISON OF

PER UNIT

PROFORMA WITH

PEERS

EXPORT? END

YES

ADD TO EXISTING WORKBOOK

Seasoned Property Analysis This process is demonstrated by example: Property ID 800213296, Colony and Fisher Arms Apartments in Detroit, MI

Click the application icon on your desktop

The application will open to the following screen:

Click ‘Click to Start’ The following screen will be displayed:

Click the ‘Seasoned Analysis’ radio button on the upper left corner of the screen

Enter the property ID, e.g. 800213296

Click ‘Tab’ key to have the application obtain the entire range of financial statements on file for the property with FASS-MF. This action will populate the two dates with the range of HUD fiscal years available.

Select the First Cash Flow year from the range of HUD Fiscal years displayed, e.g., 2008

Select the Last Cash Flow year from the range of HUD Fiscal years displayed, e. g., 2014

Click ‘Search’

This action will populate the project data screen and pull up the P&L’s requested

Proceed to view P&L’s selected by clicking the tab ‘Historical Operating Data’

The REIS market information displays actual market Vacancy, Rent Change, and Gross Revenue/Unit data to date and forecasts for future years

Click the column header ‘Forecast Year’ to re-order the market data

To export data from this page to Excel, begin by clicking ‘Export Property and REIS Data’

This action will cause the application to create a file in C:\PRT_DATA with filename ‘prt_property – 9-digit property id’

The following or similar dialogue box will be displayed:

Click ‘Yes’

Next, click ‘Export Operating Data’

The following dialogue box will be displayed again:

Click ‘Yes’ again

To view the Excel workbook that has been created, go to c:\prt_data, and click the file with property id of the subject under review

Two worksheets will have been created:

o Trending Analysis, which will contain the operating data for the time period selected and o Reis Market Forecast, which will contain the REIS historical and forecasted market data

Perform any work in Excel that you wish to do

Important: Save and close the Excel workbook when done before returning to the PRT tool

This will permit you to add additional worksheets to the existing workbook

Return to the PRT application by clicking the application icon

Note: Supplemental Process – Comparing market data between B/C and A properties

This process permits you to compare historical, current, and projected rents and rent increases between

Class A and Class B/C properties for the market in which the property is located

The tool defaults to market data for B/C properties

To also view data for A properties, click ‘Compare REIS A/BC’

Select a REIS Region, Market, and Submarket

In our example, we select Midwest, Detroit, and Downtown.

Click ‘Compare REIS A’ to view Vacancy Rate, Rent Change, and Gross Revenue/Unit for Class A properties for the region/market/submarket selected

Go to the second set of drop-downs, select the same REIS Region, Market, and Submarket

Click ‘Compare REIS BC’ to view Vacancy Rate, Rent Change, and Gross Revenue/Unit for Class B/C properties for the region/market/submarket selected

To proceed with subject-to-peer benchmarking, click the tab ‘Select Peer Criteria’

Select an operating year from the drop-down on the upper left, e.g., 2013

Select by clicking the check box for any combination of the following peer selection criteria options:

o REIS Region (Pre-populated) o REIS Market (Pre-populated) o REIS Submarket (Pre-populated) o Metropolitan Statistical Area (Pre-populated) o 236 Loan Indicator o Active Properties Only Indicator o Tax Credit Property Indicator o Multifamily Only Indicator o Nursing Home Indicator o Assisted Living Indicator o Board and Care Indicator o Hospitals Indicator o Building Type o MROC (Risk) Category o Major Program Type (Pre-selected) o Tenancy Group o SOA Code (Pre-selected) o PASS Score o Project Based Rental Assistance in Terms of Number of Units o (Mortgagor) Company type o Gross Rent per Unit o Operating Expense per Unit o Unit Count o For any State, select all desired Counties; multiple selections are allowed o Zip Code (Pre-populated)

Multiple zip codes allowed.

Surround zip codes with single quotation marks

Separate zip codes with commas

Note: The check boxes for the Prepopulated criteria will automatically be checked by the application Deselect any criteria you do not wish to use by clicking the check box to remove the check mark.

Click ‘Search’

In the example, the following criteria have been selected:

o MSA = Detroit MSA o Major Program Type = Other Rental o MROC = Green (to benchmark the subject against properties with low risk of claim)

Click ‘Search’ to initiate peer properties meeting the criteria selected

While the OPIIS database is being interrogated by the tool, an hour glass icon will appear on your screen

If properties are found meeting the criteria selected, the following dialogue box will be displayed:

o If no properties are found, such as sometimes occurs when too many criteria are selected, the following message will be displayed:

o If this is the case redefine the criteria or de-select a few criteria to broaden the search parameters

If peer properties are found, click ‘OK’ in the dialogue box.

Click the tab ‘Peer Properties’ to view the properties that met the criteria and for which financial statements were found for the operating year selected. In the example used, 45 properties were found.

o The criteria provided yield 16 properties

To export the descriptive data for the peer properties selected, click ‘Export Peer Review Data’

The following dialogue box will be displayed:

Click ‘Yes’. This action will create a new tab for these data in the existing Excel workbook for the property

Click the tab ‘Subject Property vs Peer Properties Operating Data to proceed with the analysis and to see how the subject property lines up with its peers with respect to income and expenses

To export the comparison of per unit income and expense data between the subject and its peers, click ‘Export

Peer Review Data’

Click ‘Yes’. This action will create a new tab for these data in the existing Excel workbook for the property

To view the updated Excel workbook that has been created, go to c:\prt_data, and click the file with property id of the subject under review

Two additional worksheets will have been created:

o Peer Selection, which will contain the following data elements for the peer properties selected:

pk_property_id max_afs_year tag_property_name tag_prop_address cat_city cat_us_county cat_us_state tag_zip_code

CAT_REIS_MARKET

cat_metro_statistical_area mtr_ltst_phys_inspection_score mtr_latest_mroc_score cat_latest_mroc_score_color mtr_latest_intgrtd_risk_score cat_ltst_intgrtd_rsk_score_cat cat_building_type_name mtr_unit_count cat_company_type mtr_perc_asstd cat_major_program_type cat_soa_name fk_soa_code ind_property_active ind_has_financing mtr_active_loan_count cat_troubled_status cat_latest_dec_referral_status ind_is_on_watchlist mtr_grs_rent_unit mtr_op_expenses_per_unit mtr_debt_service_coverage mtr_obm2_original_score cat_obm2_orig_score_category date_occupancy_year cat_client_group_type ind_is_236_loan ind_dap_tax_credit o Peer Statistics, which will contain the per unit income and expense data for the subject and the mean/median/min/max of the same for the peer properties select

Perform any work in Excel that you wish to do

Important: Save and close the Excel workbook when done before returning to the PRT tool

This will permit you to add additional worksheets to the existing workbook

Return to the PRT application by clicking the application icon

Benchmarking Subject’s Income and Expenses for Additional Operating Years

In the example, peers were selected and income and expenses were benchmarked for the year 2013

If the same needs to be done for other operating years (e.g., 2012 and 2014), repeat the steps above beginning with Page 14

Important: Before doing this, however, rename the two existing worksheets in the Excel workbook that pertain to the year 2013, i.e., o Rename ‘Peer Selection’ to ‘2013 Peer Selection’ o Rename ‘Peer Statistics’ to ‘2013 Peer Statistics’

This will enable the tool to create a new Peer Selection worksheet and a new Peer Statistics worksheet

Repeat this process until as many of the subject’s operating years’ data as are desired have been benchmarked against peer data

Take a Screenshot of the Summary (the Opening) Page

To include an image of the summary page (which does not have an export button), go back to and click the tab

‘Rent and Vacancy Trend Analysis’ and with the Windows Snipping Tool, cut and paste an image of the tab onto an empty worksheet of your Excel workbook, labeling it something like ‘Project Description’

Save (and close) your Excel workbook as needed

To close the PRT application, click the red X in the upper right hand corner of the application screen

File Produced with the Seasoned Property Analysis Above

Successful execution of the sample analysis for Property #800213296 should yield the file imbedded above under your directory C:\PRT_DATA

Underwriting Analysis This process is demonstrated by example: Riverview Towers, a 200-Unit High rise in New York, NY

Click the application icon on your desktop

The application will open to the following screen:

Click ‘Click to Start’

Click the ‘Underwriting Analysis’ radio button on the upper left corner of the screen

This action will open the following screen:

Enter the project name, e.g., Riverview Towers

Enter the following pieces of data for the entire project o Gross Rental Income: 2400000 o Economic Vacancy Loss (5 % as ‘5’ and not ‘.05’): 5 o Other Income: 36000 o Operating Expenses: 1360000 o Number of Units: 200

Click ‘Compute’

This action will result in the computation of Per Unit data as shown in the screen below:

Proceed to select peers

Select the operating year for which you wish to obtain financial statements for any qualifying properties that meet the peer selection criteria, e.g., 2013

Click the tab ‘Select Peer Criteria’, e.g., o MSA = New York, NY o Building Type = Mid/High Rise Apartments o Major Program Type = 221(d)4 and Other Rental

Click ‘Search’

The search criteria used in the example used yielded 17 properties

To export the descriptive data for the peer properties selected, click ‘Export Peer Review Data’

Click ‘Yes’. This will create the file in your c:\PRT_DATA directory

To view how the new project’s Proforma lines up with 2013 operating data for peer properties in HUD’s portfolio, click the tab ‘Subject Property vs Peer Properties Operating Data’

Click ‘Export Peer Review Data’ to store data in the analysis’ Excel workbook

Click ‘Yes’. This will add the Tab ‘Subject and Peer Prop Op Data’ in the analysis’ Excel workbook

Go to C:\prt_data to review the Peer Selection and Peer Statistics worksheets in the new Excel workbook created by the tool

Perform all comparative analysis desired in the workbook

Save and close the Excel workbook

To close the PRT application, click the red X in the upper right hand corner of the application screen

File Produced with the Underwriting Analysis Above

Successful execution of the sample analysis for Riverview Tower should yield the file imbedded above under your directory C:\PRT_DATA

Sample Approaches to Optimizing PRT Data in Excel

1. Create a Difference Column for Per Unit Income and Expense Data between the

Subject and Average of Peer Data In the Peer Statistics worksheet, create a new column D, called Difference, after the Subject and Mean columns

Define D2 as B2 –C2

Replicate the formula for D2 all the way down to the bottom of the worksheet

Sort the data by Difference in ascending order

What will come to the surface are those income and expense items that have the greatest variances from average peer data

2. Create Trend Lines from Year-Over-Year Operating Data In the Trending Analysis worksheet, create a trend line for DSCR for the period 2007 – 2013

The default sequencing of operating data is the most recent to oldest, from left to right

In order to create an intuitive trend line, this sequencing must be reversed

Highlight columns B – H

Click Data

Click Sort

Click Options

Click Sort Left to Right (Excel defaults to Sort Top to Bottom)

Click OK

Click Sort By dropdown

Select Row

Click Order dropdown

Select Oldest to Newest. This action will re-sequence the columns from [2013 to 2007] to [2007 to 2013]

To create a trend line for DSCR o Highlight cells A2…H2 o Click Insert Tab o Click Insert Line Chart o Click 2D o The resultant curve shows that the property approached/stayed at break-even in 2010-2011 after a severe financial losses in 2008, only to fail again precipitously in the last two years, 2012 – 2013

Replicate the steps above to create trend lines for other financial performance metrics

3. Create a Delta Column between Operating Years to view Absolute Changes in Income and Expense Categories Rename the Trending Analysis worksheet from Exercise 2

Save and close the workbook

Go to the Year-over-Year Historical Operating Data tab of the PRT

Click ‘Export Operating Data’ (again) to create another Trending Analysis Worksheet

To explore the steep decline in DSCR in 2012 and 2013, in the Trending Analysis, worksheet create a delta

(change) column between 2011 and 2012 and 2012 and 2013

Insert column between these three columns

Define E3 as D3 – F3

Replicate the formula for E3 for the rest of column E

Replicate the formula for E3 in Column C

Decreases are displayed in red/parenthesis