Pro Forma
The object of this analysis is to give you some experience in creating a pro-forma for
analysing the attractiveness of an individual project. Assumptions necessary for the
proforma analysis are attached; use them to examine a seven-year investment in a higher
quality retail centre. The pro-forma should be in nominal dollars. Use the following rules:
i) use a single discounting rate or IRR; ii) no carry forward of operating losses for use
against future income; iii) assume year 0 as the year of acquisition followed by 10 full
years of income, with sale at end of year 10, iv) the purchase includes the maximum
allowable loan, and vi) unless otherwise indicated, all leases are 5 year net leases, vii)
sale price based on estimated year 11 stabilized NOI, viii) passive owner & only one
property. For stabilized income: i) Calculate gross potential rent using current average
rent per occupied sq ft., ii) use actual operating costs, iii) vacancy allowance use the
average occupancy rate in the building over the holding period years 1-10, iii) deduct
estimated average Tis and leasing costs. Assume all TIs and leasing costs are deductible
over the life of the lease.
READ THE ASSUMPTIONS CAREFULLY
1. Purchase Price (150 points - approximately)
Find the acquisition price that gives you a total before tax unleveraged return (CFO and
capital revenues and expenditures) of 600 bp over the yield of a Government of Canada
benchmark bond yield – 10 year (as reported Friday Feb. 4, 2013,
http://www.bankofcanada.ca).
a. Take the maximum “allowable” 1st mortgage.
b. Assume that your three listed primary tenants renew, while all other tenants renew with
the assumed renewal probability. If there is no renewal use the assumed months vacant.
c. Lease terms by tenant are in the rent roll.
d. You have scheduled recoverable capital expenses. These are assumed to be added to
the UCC
All further questions refer to the after tax leveraged return for total cash flow (CFO
and capital revenues and expenditures) that came out of your calculation in 1.
2. Sensitivity Analysis (60 points).
Test for the effect on your returns for a change in each of the following variables (these
are independent not cumulative). Provide the change in basis points (bp) in your return
and the before and after values in your lowest year’s CFO (lowest year is lowest year
after change)
a. Rate of rent appreciation falls to 3.5%
b. Rate of expense appreciation increases to 4%
c. Cap rate at sale is 9.5%
d. Months vacant increases to 6 months
e. Probability of renewal falls to 50%
f. New tenant TIs increase by 5$ psf
3. Downturn (40 Points)
How do your return annual CFO, and sales price change if all of the following changes
occur that create a downturn during your holding period over years 3-6?
a. Market rents fall by 5% per annum for years 3-4, and are then flat year 5
b. Market rents rise by 13% % per annum years 6-8 and then resume the assumed growth
rate
c. Renewal probability falls to 50% for years 3-5 and returns to the original assumption in
year 6
d. Time vacant rises to 11 months for years 3-4 and then 9 months in year 5 before
returning to the original assumption for years 6 and on
4. Main Tenant Leaves (80 Points)
Your largest tenant goes bankrupt at the end of year 3.
a. What is your return and cash flow over years 4-6 if you take the 60,000 square feet and
lease it out to smaller tenants as follows:
i. They will all sign 5 years standard leases
ii. The space is vacant for 6 months, then you sign new tenants at a rate of 10,000 sq ft
per quarter
b. How long (months) could you wait to get a new main tenant and get the same return as
in a? What would be the cash flows years 4-6 if you do this?
c. If you double TIs for the new main tenant, how would your answer in “b” change?