Please help!

FIN 331 | Fall 2017 | Final Project

1

Acquisition Analysis - Aztec Apartment Communities

Assignment:

1. Create a professionally formatted one to two page Executive Summary report that presents an

overview of your evaluation, key findings, and recommendation for the proposed acquisition.

2. Support the Executive Summary with detailed analysis in MS Excel.

Instructions and Expectations:

 Submit the Executive Summary and Excel analysis on Blackboard for grading

 Executive Summary:

o Format your report and include a cover page

o Report should not be longer than two pages (three max with the cover page)

 Analysis:

o Use cell referencing in the Excel spreadsheets

o Use the financial formulas built into Excel. Do not type numbers calculated externally (e.g.

in a calculator) as partial credit cannot be awarded if there are no formulas in the Excel

spreadsheet.

o Present your answers/calculations in a neatly formatted MS Excel file. Use multiple tabs

as detailed in the instructions below.

Introduction:

You work as an acquisition analyst with SDREIT, a San Diego based apartment REIT. SDREIT, by regulation,

is obligated to pay most of its NOI to its shareholders. However, it is always under pressure from its

shareholders to grow its property portfolio. SDREIT has a limited amount of cash reserves which limits it

potential to enrich its portfolio by acquiring properties. However, SDREIT enjoys a good rapport with local

banks that are willing to lend money to the company so that it can pursue acquisitions.

Your team has identified a multifamily property “Aztec Apartment Communities” (AAC) in the San Diego

area that is available for sale by its current owners. As of today, the owners have not actively listed the

property. The property characteristics match the property holdings of SDREIT and the executive team at

SDREIT is very interested. You have been assigned to analyze the acquisition’s feasibility. The leasing

agents at AAC are cooperative. From them, you have collected comprehensive property-related

information that is displayed on the next page.

FIN 331 | Fall 2017 | Final Project

2

Figure 1 – Apartment Level Information

Unit Type Unit Description # Units Area

(sqft/unit)

Monthly

Rent ($/Unit)

1-S Studio 54 570 $1,550

1-A 1 BR/1BA 102 688 $1,775

1-B 1 BR/1.5 BA 76 800 $1,850

1-C 1 BR/1 BA+D 24 970 $1,975

2-A 2 BR/1 BA 140 1,110 $2,100

2-B 2 BR/1.5 BA 52 1,180 $2,150

2-C 2 BR/2 BA 40 1,250 $2,190

2-D 2 BR/2 BA+D 24 1,478 $2,375

3-A 3 BR/1.5 BA 64 1,500 $2,750

3-B 3 BR/2 BA 66 1,584 $2,900

4-A 4 BR/2 BA 8 1,852 $3,750

Figure 2 –Miscellaneous Income Source # Units Monthly

Rent

Garage (Direct Access) 130 $150

Carports 55 $50

Storage Units 150 $175

An 10% vacancy rate is anticipated for the apartments. Full occupancy (i.e. 0% vacancy) is expected for

the miscellaneous sources of revenue.

Figure 3 – Operating Expenses Expense Annual

PSF

Administration 0.24

Real Estate Taxes 3.85

Advertising 0.34

Insurance 0.45

Repairs & Maintenance 0.76

Management Fee 0.88

Personnel Expense 1.65

Utilities 0.85

The annual operating expenses are on a per square foot (psf) basis. Capital expenditures of $1.25 psf will be placed in reserves each year.

FIN 331 | Fall 2017 | Final Project

3

Project Requirements:

1. Rent Roll: Prepare a Rent Roll in Excel similar to the example below using data from ‘Figure 1’ [Tab 1].

Unit Type

Unit Description

# Units Area (Sqft/Unit)

Monthly Rent/Unit

Monthly Gross Rent

Annual Gross Rent

Total Area

Monthly Rent PSF

1-S Studio 54 570 $1,550 $83,700 $1,004,400 30,780 $2.72

1-A 1 BR/1BA --

1-B 1 BR/1.5 BA --

-- -- --

-- -- --

-- -- --

Total

702,628

2. Misc. Income: Create another table for annual miscellaneous income using ‘Figure 2’ [Tab 1]

3. Property Expenses: Prepare a table for annual operating expenses and capital expenditures [Tab 1]

4. Market Value: Your market analysis suggests that similar properties are selling at a 7.3% cap rate.

4.1 Calculate the property’s Year 1 PGI, EGI and NOI [Tab 1] 4.2 Estimate the market value of AAC. This is the sales price SDREIT will pay to acquire the property [Tab 1]. AAC management is on good terms with SDREIT executives, so no brokerage services will be required. All

legal and other expenses related to the acquisition will be provided by the in-house team at SDREIT; thus

they will not be considered in this analysis. SDREIT’s CFO advises you that she may approve up to $25

million of equity from the cash reserves to acquire this property. The remaining cash must come from

external sources. Your CFO is unwilling to raise additional equity shares at the moment. Thus, you only

have one option if you need additional capital to move forward with the acquisition - to get a loan from

the bank.

5. Loan Information and Analysis [Tab 2]: Your bank loan officer agrees with your evaluation of the

market value of PTAC and its revenue potential. They advise you that the bank will consider two metrics

when determining the loan amount: a maximum Loan-to-Value (LTV) of 80% and a minimum Debt-Service-

Coverage Ratio (DCR) of 1.25. The final loan amount will be the minimum loan amount suggested by these

two metrics (LTV or DCR). The loan term will be 30 years (with monthly payments) at an annual interest

rate of 6.0%.

5.1 Calculate the loan amount based on the LTV metric [Tab 2]

FIN 331 | Fall 2017 | Final Project

4

5.2 Calculate the loan amount based on the DCR metric. Hint: What is the maximum allowed

annual loan payment based on the DCR metric? Divide this amount by 12 to find the maximum

allowed monthly payment. Then calculate the PV of the payments [Tab 2]1

5.3 Which of the two loan amounts you calculated will be selected by the bank? [Tab 2]

5.4 Is the acquisition feasible based on your cash reserves and financing options? Why? [Tab 2]

5.5 What is the annual debt service (ADS)? [Tab 2]

6. Project Evaluation [Tab 3]: SDREIT plans to own and operate this property for five years. At the end of

five years, it will sell the property. Thus, the cash flow for the fifth year will be the sum of the cash flow

from operations and the cash flow from sale. In the next step of your analysis you evaluate how the project

will perform assuming a five year holding period. SDREIT subscribes to various databases and periodically

consults real estate experts to remain up-to-date with market movements and trends. Based on your

research of the San Diego market you use the following assumptions in your projections for the next five

years:

Assumptions for Projections & Pro-forma Development in [Tab 3]

I. There are no up-front financing costs for the loan selected in 5.3

II. Use the Year 1 calculations from tabs [1] and [2] to calculate Year 1’s BTCF

III. PGI will increase at a rate of 3% per year with a strong marketing campaign

IV. Vacancy and Collection losses will remain at 10%

V. Miscellaneous Income will increase at a rate of 2% per year

VI. Operating expenses and capital expenditures will increase at 3% per year

VII. At the end of five years, your analysis suggests that similar properties will be selling at a 8.0%

cap rate2

VIII. Selling costs at the end of the fifth year will be approx. 7% of the sale price

IX. SDREIT typically requires at least a 15% return on their equity investment

Pro-forma, NPV, and IRR [Tab 3]

6.1 Based on the information provided above; estimate the BTCFs for AAC for the next five years.

As a standard practice, yearly cash flows are arranged column-wise3 [Tab 3]

6.2 Calculate the project’s IRR4 [Tab 3]

6.3 Calculate the NPV using SDREIT’s required return as the discount rate [Tab 3]

1 The calculations for 5.1 and 5.2 are the same as the underwriting ratio calculations we covered in slide deck “13 – Commercial Debt and Equity”. 2 Note that the market value at the end of five years is based on the property’s potential NOI in the following (sixth year). Also, assume that market value is the terminal price (i.e. sales price) of the property in year five. 3 Hint: You can use the BTCF excel file posted on Blackboard as a template for your analysis 4 Remember, the initial investment should be treated as a negative cash flow at year-0. The remaining cash flows should be shown as year-1, year-2…., year-5

FIN 331 | Fall 2017 | Final Project

5

7. Executive Summary & Recommendation: Prepare a 1 to 2 page Executive Summary that provides a

synopsis of your analysis. Make it succinct yet informative so that a busy executive from the C-suite can

understand your research and recommendation. Provide key data and findings in your report and make a

recommendation regarding the acquisition. Base your recommendation on the analysis you completed

and discuss the sensitivity of your findings (i.e. conduct a basic sensitivity analysis).5 The Executive

Summary should be completed in a separate MS Word (or pdf) document.

Conduct all your analyses in a single Excel file (using multiple worksheets as detailed in the instructions

above). Name your tabs accordingly. Upload your Executive Summary report (MS Word/PDF) and analysis

(MS Excel) files for grading. Save your files using the following naming convention:

LastName_FirstName_Analysis.xls and LastName_FirstName_ExecutiveSummary.pdf.

Further instructions:

 Use the excel template provided to get started. Make sure all of the assumptions in the excel

template match those listed in this file.

 Use cell referencing and the financial functions in your Excel analysis [Do not perform calculations

outside of excel and then manually type the answers in to the cells].

 Format your excel files and Executive Summary professionally. Include a cover page for your

Executive Summary. Check for and fix obvious typos before submitting your work.

 Where needed, make your own assumptions. However, if you make an assumption clearly state

the assumption in your write-up and excel file.

 This is an individual assignment. Do not share your work with other students taking the class.

Doing so will result in a zero for all students who work together on this assignment.

 If you have any questions about the assignment send me an email ([email protected]), ask

me during or after class, or attend my office hours.

5 You can run a simple sensitivity analysis by changing the expected PGI growth rate to 5% (best case scenario) and -1% (worst case scenario). Would your recommendation change in either scenario?