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?