All-in-One (Ai1) Walkthrough #8 – Tenancy Analysis Report for Office, Retail, and Industrial
This walkthrough, our eight in the series, explains how the Tenancy Analysis report for office, retail, and industrial real estate underwriting works in our All-in-One (Ai1) model. The Tenancy Analysis report includes a tenant rollover schedule, a graph illustrating cumulative expiration of net rentable area by year, a summary of weighted average lease term and remaining lease term, a summary of compound average revenue and NOI growth rates, and an auto-updating PivotTable showing the top 5 largest tenants by NRA.
In an effort to provide greater instruction on how to use our All-in-One Underwriting Tool for Real Estate Development and Acquisition, we’re developing this series of walkthrough videos and posts on the methodology behind the various components of the model. Our hope is that if you are empowered with the how, you’ll be more willing/able to provide feedback to improve the model.
If you haven’t already, you can download the model here. This walkthrough uses beta version 0.5.2 of the All-in-One Model.
Video Walkthrough – Using the Tenancy Analysis Report
Components of the Tenancy Analysis Report
The Tenancy Analysis Report is broken into three components, the Rollover Schedule, the Lease Term and Growth Rates section, and the Top 5 Largest Tenants section.
Rollover Schedule. Looks at total square feet (square meters coming soon) expiring per year, percentage of net rentable area expiring per year, and the cumulative percentage of net rentable area expired by year. This data is then graphically illustrated in a column chart.
Lease Term and Growth Rates. This section reports the weighted (by NRA) average lease term of the rent roll, weighted (by NRA) average remaining lease term of the rent roll, and compound average growth rates for effective gross income and net operating income.
Top 5 Largest Tenants. The largest tenants by net rentable area are reported, using an auto-updating Pivot Table. The Pivot Table displays by tenant: net rentable area, monthly rent, lease expiration year, each tenant’s percent of total NRA, and each tenant’s percent of total rent.
Turning the Report On via the Summary Tab
The Tenancy Analysis Report tab automatically becomes available when the ORI Module is turned on (see Summary tab ‘Include Modules?’ section) and the Report Tabs are set to ‘Show’ (see Summary tab ‘Navigation’).
Using the Tenancy Analysis Report
Very little needs to be done to use this report. Simply make sure that you are modeling an office, retail, or industrial deal and that the Report Tabs drop-down in the Navigation section of the Summary tab is set to ‘Show’. Then, go to the Tenancy Analysis tab.
Print areas have been selected for this report, so printing is as easy as going to the report and selecting File>Print.
If you’d like to make changes to the Pivot Table (under Top 5 Largest Tenants), click anywhere in the Pivot Table area and two PivotTable Tools ribbons, Analyze and Design, become available along the top of the window. The data used by the PivotTable is found hidden below row 38 of the Tenancy Analysis tab.
A Macro has been written so that the PivotTable automatically updates when its source data changes. To find that code, hover over the Tenancy Analysis tab along the bottom of the window, right-click, and select ‘View Code’. The code simply states, if the current worksheet is newly activated and the ORI Module is on, update the first (and only) PivotTable on this worksheet, otherwise do nothing.
If you have any questions, please don’t hesitate to reach out.
