office investment. due in 10 hours
lixin
HW2-officeInvestment.xlsxAssignment
| ASSIGNMENT | ||
| Please Complete the Following: | ||
| 1 | Full Completion of P&L Statement using historical financial report . Also complete the U/W column. | |
| Note: All cells should be active (not simple inputs) and connected to the reference sheets provided | ||
| 2 | Complete TI and Rollover Analysis | |
| 3 | Complete Lease Analysis and Sample LC calculation for Tenant 2 (analyze lease assuming you are at the original starting point of the lease) | |
| Act as if Analysis is in 2016 | ||
| Make Sure to Submit Via Online Portal ON-TIME with Correct Naming Format | ||
P&L
| 2013 | 2014 | 2015 | T12 | UW | Notes | ||||||||||||
| INCOME | $/SF | $/SF | $/SF | $/SF | $/SF | ||||||||||||
| In-Place Base Rent | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | |||||||
| Vacancy Gross Up | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Straight Line Rent | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Rent Steps | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Mark to Market Adjustment | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Total Rent | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | |||||||
| Reimbursements | |||||||||||||||||
| CAM / Other Reimbursements | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Real Estate Taxes Reimbursements | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Insurance Reimbursements | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Reimbursements Vacancy Gross Up | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Total Reimbursements | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | |||||||
| Total Gross Income | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | |||||||
| In-Place Vacancy | 0 | 0.0% | 0 | 0.0% | 0 | 0.0% | 0 | 0.0% | 0 | 0.0% | |||||||
| Underwriting Vacancy Adjustment | 0 | 0.0% | 0 | 0.0% | 0 | 0.0% | 0 | 0.0% | 0 | 0.0% | |||||||
| Total Vacancy | $0 | 0.0% | $0 | 0.0% | $0 | 0.0% | $0 | 0.0% | $0 | 0.0% | |||||||
| Other Income | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| EFFECTIVE GROSS INCOME | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | |||||||
| EXPENSES | |||||||||||||||||
| Management Fee | 0 | 0.00% | 0 | 0.00% | 0 | 0.00% | 0 | 0.00% | 0 | 0.00% | |||||||
| Common Area Maintenance | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Repairs & Maintenance | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Utilities | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| General & Administrative | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Marketing | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Payroll | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Real Estate Taxes | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Insurance | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Ground Rent | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Other | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| TOTAL EXPENSES | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | |||||||
| NET OPERATING INCOME | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | |||||||
| Tenant Improvements | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Leasing Commissions | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| Replacement Reserves | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | 0 | 0.00 | |||||||
| TOTAL LEASING & CAPITAL EXPENSES | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | |||||||
| NET CASH FLOW | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | $0 | $0.00 | |||||||
Rollover Analysis
| TI / LC Assumptions | ||||||||||||
| TI / LC Category | TI NEW | TI Renewal | LC New | LC Renewal | Renewal Probability | Market Rent | Lease Term | Average TI PSF | Average LC PSF | |||
| # | Name | |||||||||||
| 1 | Office | $20.00 | $10.00 | 6.00% | 3.00% | 65% | $33.00 | 10 years | ENTER FORMULA | ENTER FORMULA | ||
| 2 | Enter | Enter | Enter | Enter | Enter | Enter | Enter | Enter | ENTER FORMULA | ENTER FORMULA | ||
| 3 | Enter | Enter | Enter | Enter | Enter | Enter | Enter | Enter | ENTER FORMULA | ENTER FORMULA | ||
| 4 | Enter | Enter | Enter | Enter | Enter | Enter | Enter | Enter | ENTER FORMULA | ENTER FORMULA | ||
| 5 | Enter | Enter | Enter | Enter | Enter | Enter | Enter | Enter | ENTER FORMULA | ENTER FORMULA | ||
| 6 | Enter | Enter | Enter | Enter | Enter | Enter | Enter | Enter | ENTER FORMULA | ENTER FORMULA | ||
| 7 | Enter | Enter | Enter | Enter | Enter | Enter | Enter | Enter | ENTER FORMULA | ENTER FORMULA | ||
| 8 | Enter | Enter | Enter | Enter | Enter | Enter | Enter | Enter | ENTER FORMULA | ENTER FORMULA | ||
| 9 | Enter | Enter | Enter | Enter | Enter | Enter | Enter | Enter | ENTER FORMULA | ENTER FORMULA | ||
| 10 | Enter | Enter | Enter | Enter | Enter | Enter | Enter | Enter | ENTER FORMULA | ENTER FORMULA | ||
| Blended | ||||||||||||
| Lease Expiration Summary - All Rolls | ||||||||||||
| Year | # Leases Expiring | Total Expiring SF | % of Total Expiring SF | TI | LC | Total TI / LC | TI/LC Reserve | CF Sweep | TI/LC Balance | |||
| MTM | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | $0 | $0 | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2015 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2016 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2017 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2018 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2019 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2020 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2021 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2022 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2023 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2024 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| 2025 | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | ERROR:#VALUE! | ERROR:#VALUE! | Enter Formula | Enter Formula | $0 | Enter Formula | |||
| Total | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | ERROR:#VALUE! | ERROR:#VALUE! | $0 | ||||||
| Lease Expiration Summary - First Roll | ||||||||||||
| Year | # Leases Expiring | Total Expiring SF | Cumulative Expiring SF | % of Total Expiring SF | Cumulative % of Expiring SF | UW Base Rent (1) | UW Base Rent PSF (1) | % of UW Base Rent (1) | Cumulative % of UW Base Rent (1) | |||
| MTM | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2015 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2016 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2017 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2018 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2019 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2020 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2021 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2022 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2023 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2024 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| 2025 | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| Beyond | ERROR:#VALUE! | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | 0.0% | |||
| Vacant | NAP | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | 0.0% | NAP | NAP | NAP | ||||
| Total | ERROR:#VALUE! | ERROR:#VALUE! | 0.0% | ERROR:#VALUE! | $0.00 | 0.0% | ||||||
| 1. UW Base Rent and % of UW Base Rent include contractual rent steps through Enter, and exclude any gross up of vacant space. | ||||||||||||
Lease Analysis
| Tenant Name | ||||||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |||
| Base Rent Revenue | ||||||||||||
| Rent Step | ||||||||||||
| Total Base Rent | ||||||||||||
| Reimbursement Revenue | ||||||||||||
| Other Income | ||||||||||||
| Total Income from Tenant | ||||||||||||
| Curren Base Rent | ||||||||||||
| PV of Rent Steps | ||||||||||||
| Rent Step Detail | ||||||||||||
| Escalation | ||||||||||||
| Frequency | ||||||||||||
| Expense Reimbursement Details | ||||||||||||
| Base Stop? |
Typical LC Calculation
| Market Rate | $34.00 | |||||||||||
| Years 1-3 | % | |||||||||||
| Years 4-7 | % | |||||||||||
| Years 8-10 | % | |||||||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |||
| Rent | ||||||||||||
| Commission % | ||||||||||||
| Commission ($) | ||||||||||||
| Total Comission Amount |
Historical Information
T-12
Rent Roll
| Tenant Name | Floor | Rate & Amount | ||||||
| Tenant Type | SF | Per Year | Changes | Changes | Expense | |||
| Tenant # | Lease Date & Term | Share of Building | Per Month | Steps | On | To | Reimbursement | |
| 1 | Tenant A | 3rd Floor | $30.50 | N/A | $6.23 Expense Stop, + Electric | |||
| Office | 8,742 | 266,631 | ||||||
| Nov-2006 to Jun-2017 | 15.90% | $2.54 | ||||||
| $22,219 | ||||||||
| 1 | Tenant A (Expansion) | 3rd Floor | $30.50 | N/A | $6.23 Expense Stop, + Electric | |||
| Office | 1,555 | 47,428 | ||||||
| Sep-2011 to Jun-2017 | 2.83% | $2.54 | ||||||
| 3,952 | ||||||||
| 2 | Tenant B | 4th Floor | $28.50 | Year 1 | 16-Aug-17 | $30.50 | 2016 Base Year , + Electric | |
| Office | 14,644 | 417,354 | Year 2 | 31-Aug-18 | $31.00 | |||
| Feb-2001 to Jul-2026 | 27.74% | $2.38 | Year 3 | 31-Aug-19 | $31.50 | |||
| 34,780 | Year 4 | 31-Aug-20 | $32.00 | |||||
| Year 5 | 31-Aug-21 | $32.50 | ||||||
| Year 6 | 31-Aug-22 | $33.00 | ||||||
| Year 7 | 31-Aug-23 | $33.50 | ||||||
| Year 8 | 31-Aug-24 | $34.00 | ||||||
| Year 9 | 31-Aug-25 | $34.50 | ||||||
| Year 10 | 31-Aug-26 | $35.00 | ||||||
| Year 11 | 31-Aug-27 | $35.50 | ||||||
| 2 | Tenant B (Expansion) | 3rd Floor | $0.00 | Year 1 | 17-Jan-17 | $30.50 | 2016 Base Year , + Electric | |
| Office | 3,574 | $0 | Year 2 | 31-Jan-18 | $31.00 | |||
| Dec-2016 to Jul-2026 | 6.49% | 0 | Year 3 | 31-Jan-19 | $31.50 | |||
| Year 4 | 31-Jan-20 | $32.00 | ||||||
| Year 5 | 31-Jan-21 | $32.50 | ||||||
| Year 6 | 31-Jan-22 | $33.00 | ||||||
| Year 7 | 31-Jan-23 | $33.50 | ||||||
| Year 8 | 31-Jan-24 | $34.00 | ||||||
| Year 9 | 31-Jan-25 | $34.50 | ||||||
| Year 10 | 31-Jan-26 | $35.00 | ||||||
| Year 11 | 31-Jan-27 | $35.50 | ||||||
| 3 | Tenant C | 3rd Floor | $29.75 | Year 1 | 16-Mar-17 | $29.75 | $6.52 Expense Stop, + Electric | |
| Office | 1,440 | 42,840 | Year 2 | 31-Mar-18 | $30.25 | |||
| Apr-2010 to May-2019 | 2.62% | $2.48 | Year 3 | 31-Mar-19 | $30.75 | |||
| 3,570 | Year 4 | 31-Mar-20 | $31.25 | |||||
| 3 | Tenant C (Expansion) | 3rd Floor | $29.75 | Year 1 | 16-Mar-17 | $29.75 | $6.52 Expense Stop, + Electric | |
| Office | 1,206 | 35,879 | Year 2 | 31-Mar-18 | $30.25 | |||
| Mar-2014 to May-2019 | 2.19% | $2.48 | Year 3 | 31-Mar-19 | $30.75 | |||
| 187,708.50 | 2,990 | Year 4 | 31-Mar-20 | $31.25 | ||||
| 4 | Tenant D | 2nd Floor | $29.50 | Year 1 | 15-Nov-17 | $29.50 | $6.23 Expense Stop, + Electric | |
| Office | 6,363 | 187,709 | Year 2 | 30-Nov-18 | $30.00 | |||
| Oct-2011 to Oct-2019 | 11.55% | 2.46 | Year 3 | 30-Nov-19 | $30.50 | |||
| 15,642 | Year 4 | 30-Nov-20 | $31.00 | |||||
| 5 | Tenant E | 3rd Floor | $30.50 | Year 1 | 16-Apr-17 | $30.50 | 2015 Base Year, + Electric | |
| Office | 1,697 | 51,759 | Year 2 | 30-Apr-18 | $31.00 | |||
| Nov-2014 to Mar-2021 | 3.10% | 2.54 | Year 3 | 30-Apr-19 | $31.50 | |||
| $4,313 | Year 4 | 30-Apr-20 | $32.00 | |||||
| Year 5 | 30-Apr-21 | $32.50 | ||||||
| 6 | Tenant F | 3rd Floor | $30.50 | Year 1 | 15-Dec-17 | $30.50 | $6.52 Expense Stop, + Electric | |
| Office | 2,213 | 67,497 | Year 2 | 31-Dec-18 | $31.00 | |||
| Dec-2014 to May-2022 | 4.02% | 2.54 | Year 3 | 31-Dec-19 | $31.50 | |||
| $5,625 | Year 4 | 31-Dec-20 | $32.00 | |||||
| Year 5 | 31-Dec-21 | $32.50 | ||||||
| Year 6 | 31-Dec-22 | $33.00 | ||||||
| Year 7 | 31-Dec-23 | $33.50 | ||||||
| 7 | Tenant G | 2nd Floor | $30.50 | Year 1 | 16-Aug-17 | $31.11 | 2015 Base Year, + Electric | |
| Office | 10,442 | $318,481 | Year 2 | 31-Aug-18 | $31.73 | |||
| Aug-2015 to Jul-2018 | 18.95% | 2.54 | ||||||
| $416,630.00 | $26,540 | |||||||
| 7 | Tenant G (Expansion) | 2nd Floor | $30.50 | Year 1 | 16-Aug-17 | $31.11 | 2015 Base Year, + Electric | |
| Office | 3,218 | $98,149 | Year 2 | 31-Aug-18 | $31.73 | |||
| Aug-2015 to Jul-2018 | 5.84% | 2.54 | ||||||
| $8,179 |
