Prepared a detailed Excel Pro Forma for presentation to the investment committee (your instructor). Format neatly like it would be for a report. Proforma must show: a. All assumptions b. Prepare a timeline c. Prepare a statement of all development costs showing units costs per gross and rentable square foot. d. Prepare an income, expense, and cash flow statement for each year of the development and holding period. The statement will include before and after-tax calculations. e. Provide an amortization schedule for the permanent loan (preferably on a separate tab). f. Provide a summary of the sale disposition calculations on a before and after-tax basis. g. Provide annual return on equity calculations on a before and after-tax basis. Use both actual cash equity and imputed equity (this equity is the difference between the appraised value derived for the permanent loan and the permanent mortgage). Note also that the equity after the permanent loan may be different that the initial investment so in the 2021 you need to show the cashflows that result from paying off the construction loan and taking out the permanent loan). h. Provide a summary of the quality of the investment using individual investment criteria to be given later. These criteria will be randomly assigned later. i. This assignment is designed to test your ability to analyze a deal and prepare a proforma accordingly. Based on the assumptions, there should be a clear answer prior to the input of subjective criteria given it item h.

Building attributes are as follows: 1. 160,000 gross building square feet 2. 140,000 rentable square feet on 5 floors (Floor 1: 25,000sf, Floors 2-4: 30,000sf/floor, Floor 5: 25,000sf) 3. Site size: 5.0 acres Development costs are as follows: 1. Construction period: One year (2020) 2. Land cost: $25/square foot of land 3. Hard construction costs: $175/gross square foot 4. Tenant improvement costs: $50/net rentable square foot without regard to vacancy. 5. Soft costs: $35/gross square foot. Includes all costs including construction interest. 6. 100% of the Tenant Improvements will occur in the year of construction. The construction loan will fund all TIs in 2020. Operating conditions: 1. Building is 100% preleased prior to occupancy. 2. Initial rent to achieve 8.5% return on initial cost in stabilization year (2021). Must do some iteration on Excel or via calculator to establish the starting rent. 3. First and Fifth Floor Rents are initially $2/RSF higher than the other floors 4. Annual increase in rental income: 75% of previous years CPI. CPI years 2021-2025 expected to be 1.5%, 2%, 2.5%, 3.0%, and 3.5% respectively. 5. Expense stop in Years 2021-2026 is $8.50/RSF. This is only on fixed and variable expenses. 6. Annual, expected market vacancy allowance: Years 2021-2026 forecast to be 5%, 8%, 10%, 7%, 5%, and 5% respectively of gross potential rental income (including reimbursements). 7. Expenses and reserves: a. Fixed operating expenses of $4/gross square foot in the first operating year, increasing 2.0% annually b. Variable operating expenses of $4/leased square foot in the first operating year increasing 3.0% annually. Vacancy is not taken on operating expenses. c. Reserves of $1.50/ gross square foot in the first operating year increasing by 2% annually. Reserves will be treated as an expense, so they will not be depreciated. They are not reimbursable. 8. There will be no lease rollover during the forecast period. Sales assumptions: 1. Assume sale near the end of the 5th operating year (2025). 2. Sales price is based on 2026 NOI capitalized at 8.25% rounded down to the nearest $1,000. 3. Selling costs are 4% of the sales price Lending environment: 1. Construction loan: a. Amount: 65% of all development costs. b. 5% interest only until one year after construction (refinance 12/31/2021). Interest during the construction period is included in the building soft costs. Interest-only payment during the first operating year is a cash flow. c. Close on construction loan: 12/31/2019. This will fund 65% of all development costs and land. Initial equity goes in at that time which will pay for the land and closing costs with the balance held by the bank until drawn for development and construction. (Time=0). d. Construction is during 2020. The construction is funded by construction draws that are paid from the advanced equity first and then draws on the loan. The net result is that there are no additional cash outlays during the year of construction. Show treatment of cash flows. e. Construction Loan Maturity: End of 2021. After construction and lease-up (pay off on 12/31/2021 with a Permanent loan). 2. Permanent loan: a. Close after building fully leased up plus one year so close on 12/31/2021. This will pay off the Construction loan. b. Loan amortization: 25 years c. Monthly principal and interest payments d. Loan Term: 10 years e. 4.5% interest rate per annum f. Loan costs: 0.75% of loan amount (treat as loan points for taxable income calculation). g. Loan amount: 70% of appraised amount. Appraised amount is the lesser (rounded down to the nearest $1000) of: i. Stabilized year (2022) NOI capitalized at 8.0% ii. $350/ rentable square foot Taxation conditions: 1. Depreciation is on all costs less land and is depreciated over 39 years since office building. 2. Depreciation commences in the first year of operation. Disregard mid-month convention. 3. Permanent loan costs are amortized over the term of the loan. Any unamortized costs may be used to reduce ordinary income in the year of sale. 4. Reserves will be treated as an expense; so, they will not be depreciated for this project. 5. Tax rates: a. Ordinary income taxed at 28%. b. Depreciation recapture taxed at 25% c. Long term capital gains taxed at 15% d. Assume that losses, if any, can be used to offset other passive income.