Finance HW

FIN 331 | Final Project

San Diego State University | Finance Department

Acquisition Analysis – Beachcomber Apartments

Assignment 1. Create a professionally formatted one- to two-page memo describing your evaluation and

recommendation for the proposed acquisition. 2. Support the memo with detailed analysis in MS Excel. 3. Where needed, make your own assumptions. However, if you make an assumption, clearly state

the assumption in your write-up.

Instructions and Expectations • Analysis – MS Excel

o This portion may be done with a partner enrolled in the class. Pairs only, no triads. o Use both direct capitalization and yield capitalization o Present your answers/calculations in a neatly formatted, print-friendly MS Excel

workbook o Use multiple tabs (sheets) as detailed in the instructions below. o Follow good modeling practices

 Use cell referencing, and absolute cell references where appropriate  Do not put the same input assumption in two places. An input should be in one

cell only and other uses of that value should come to it via a cell reference.  Use the financial formulas built into Excel. Do not type numbers calculated

externally (e.g. in a calculator). A hard-wired input within a formula will cause a big point deduction.

 Formatting counts! Make good use of space. Avoid showing pennies in amounts of less than $1,000. Use bold and shading sparingly and always with purpose.

• Memo – MS Word or PDF o The memo is an individual assignment. o Format your memo professionally. Good writing counts! Keep the tone formal and

dispassionate. It’s fine to believe in the deal, but flowery descriptions can hurt your reputation as a rational analyst. Folksy language will seem either amateurish or immature. Organize your thoughts, make each word count, and proof-read your work.

o Emails have replaced memos for at least 90% of internal written communication in business. But memos are still used for important situations. They are built to last. They carry sender and recipient names. They have descriptive subjects (rather than the random first-thing-that-hits-your-mind topic). And crucially, they contain a date. Please read this excellent piece on when memos are appropriate.  http://www.businesswritingblog.com/business_writing/2015/06/when-to-

write-a-memo-not-an-email.html o Say enough to be convincing and not so much that you lose your audience. One to 1.5

pages should be enough.

FIN 331 | Final Project

San Diego State University | Finance Department

Introduction You work as an acquisition analyst with SDREIT, a San Diego based apartment real estate investment trust. REITs, by regulation, must pay at least 90% of their taxable income to shareholders every year, so reserves are limited. Yet SDREIT is always under pressure from its shareholders to grow its property portfolio. SDREIT can acquire more properties by using debt than by purchasing them with 100% equity. SDREIT enjoys a good rapport with local banks. They are willing to loan money to the company so that it can pursue acquisitions.

Your team has identified a multifamily property “Beachcomber Apartments” in Pacific Beach, owned by Pacific Apartment Communities LLC (PAC). The property characteristics match SDREIT’s target profile and the executive team at SDREIT is very interested. As of today, the owners have not listed the property, but one of the owners was at a social event with your boss and indicated that they might be interested in selling. You have been assigned to analyze the acquisition’s feasibility. PAC’s property manager has provided the following information.

Figure 1 – Apartment Mix and Average Scheduled Rent per Unit Type Unit Type Unit Description # Units Area

(SF/Unit) Monthly Rent

($/Unit) A-1 1 BR / 1 BA 22 624 1,500 A-2 1 BR / 1 BA 16 643 1,520 A-3 1 BD / 1.5 BA 4 690 1,600 B-1 2 BD / 1 BA 10 774 1,770 B-2 2 BD / 1.5 BA 6 796 1,820 B-3 2 BD / 2 BA 22 928 1,985

Figure 2 – Scheduled Miscellaneous Income Source # Units Rent Garages 26 $125 per month Storage Units 16 $50 per month Carports 22 $70 per month Other $10,000 /year

Figure 3 – Historical Operating Expenses

Capital expenditures of $.25 psf are placed in reserve each year.

2014 2015 2016 Expenses R.E. Taxes 75,834.00 77,350.68 78,897.69 Salaries 52,589.00 54,589.00 58,431.00 Utilities 63,474.20 51,305.20 50,401.00 Management Fees 83,639.20 86,154.20 92,010.20 Administration 3,256.00 3,569.00 4,032.00 Marketing 0.00 0.00 0.00 Contract Services 26,040.00 19,440.00 28,440.00 Repairs & Maintenance 36,509.00 115,339.80 192,007.50 Other 0.00 0.00 47,000.00 Total Expenses 341,341.40 407,747.88 551,219.39

FIN 331 | Final Project

San Diego State University | Finance Department

SDREIT’s Valuation Process The riskiest money in any investment is that which is spent during the acquisition process. If you spend a lot of money – your time is money – analyzing a deal that you do not end up buying, that money is lost forever. Therefore, SDREIT has its acquisition analysts take the following steps:

1. Estimate value by direct capitalization. 2. Run the property and the analyst’s valuation by a bank’s loan officer to see how much debt can

be obtained and at what cost. 3. If all looks good at that point, use that information and assumptions about what will happen

during the hold period to create a five-year discounted cash flow and calculate the property value using yield capitalization.

4. If the DCF using SDREIT’s equity-yield requirement ends in a value close to the amount estimated by direct capitalization, write a memo to the executive committee describing the property, your recommendation, the key metrics, and your analysis.

Excel Workbook Part A – Value by Direct Capitalization Recall that direct capitalization uses a one-year-snapshot of a property’s net income. It estimates gross income and vacancy and subtotals the difference as effective gross income. It then subtracts estimated expenses to produce an estimate of net operating income. Finally, it divides that NOI by a market- derived capitalization rate to produce a market-value estimate. Direct capitalization does not display the cost of debt. The returns to debt and equity are built into the capitalization rate.

Direct capitalization is faster than yield capitalization, so it is often the preferred first step in acquisition analysis. But some of the elements created in this endeavor will also be used when you do yield capitalization for Part 3.

Sheet 1 – Scheduled Income Always start a spreadsheet in row 3 or below. Leave room at the top for labeling. Also, good modelers put a date in the workbook, typically in any of the sheets that are likely to be printed and distributed.

1. Apartment Rents: For this first run at valuation, assume that the scheduled rents provided by PAC are market rents. Prepare a summary of those scheduled rents similar to the example that follows. Of course, shading and text color can be your individual choice. Style is fine, but readability rules!

a. As soon as you finish the first row, pause and think about whether you want to type – and whether your reader wants to see – “BR” and “BA” over and over again. Do you really need the title “Unit Description”? What if you wanted to be able to total the bedroom counts? How can you redesign the “Unit Description” column to carry pure data and use the minimum amount of horizontal space?

b. Consider what the “Total” area in square feet should be about. Is the sum of one of each type a meaningful number? Do you see a possible need for the average unit size? Do

FIN 331 | Final Project

San Diego State University | Finance Department

you see how the total living area might be useful? Should there be two summary rows, one for total and one for average?

c. What do you think is relevant for a given row in the last column – the annual rent for a given unit type, or the total annual rent for all of those same-type units?

Unit Type

Unit Description

# Units Area (SF) Monthly Rent/Unit

Monthly Rent/SF

Annual Gross Rent

A-1 1 BR / 1BA 22 624 $1,500 ? ?

A-2A 1 BR/1.5 BA --

-- -- --

-- -- --

-- -- --

-- -- --

Total

2. Miscellaneous Income: Create another table for miscellaneous income using ‘Figure 2’ data. But wait. Why are storage units listed between garages and carports? Is that logical? Should you change the order?

Sheet 2 – Direct Cap 1. Create a row for Rental Income. Link to the total in Sheet 1 (now renamed “Income”). 2. Create a row for Miscellaneous Income. Link to the total in Sheet 1. 3. Apply a market-derived 3% vacancy rate for the apartments and the miscellaneous income. 4. Calculate effective gross income 5. Create a table for annual operating expenses and reserves for capital expenditures.

a. Consider both the reported spending levels from PAC as well as the expense comparables. Management styles vary. A subject property’s historical operating expenses are probably the best predictor, but they are not gospel.

b. Include columns for % of EGI and $/SF. This table should align with the foregoing estimates so that annual income and expenses are in the same column. Remember, ad- valorem property taxes are dependent upon the projected sale price, and the price is influenced by the level of taxes. Hopefully you were in class to learn the mathematical workaround to this circular-reference conundrum.

6. Calculate the property’s expected Year-1 NOI. 7. Solve for value using a market-derived capitalization rate of 4.5%. Presumably, that is market

value and a price at which the owner would be willing to sell.

PAC’s owners are 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. They are part of the company’s overhead and their cost is built into the company’s required return.

SDREIT’s CFO advises you that she may approve up to $8 million of equity from the cash reserves to acquire this property. The remaining cash must come from external sources. Your CFO is unwilling to

FIN 331 | Final Project

San Diego State University | Finance Department

raise additional equity shares at the moment. Thus, you have only one option if you need additional capital to move forward with the acquisition – get a loan.

Part B – Underwriting Sheet 3 Your preferred-bank’s loan officer agrees with your value estimate for Beachcomber and its Year-1 revenue projection. She says the bank will examine 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 of those suggested by these two metrics. The amortization period will be 30 years (with monthly payments) at an annual interest rate of 3.5%. The loan term will be five years.

1. Calculate the loan amount based on the LTV metric 2. Calculate the loan amount based on the DCR metric. 3. Which of the two loan amounts you calculated will be selected by the bank? 4. Is the acquisition feasible based on your cash reserves and financing options? Why?

Part C – Yield Cap Sheet 4

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.

Before-Tax Cash Flow

Use the Year-1 calculations from your direct-capitalization work for Year 1. You will use every row through Net Operating Income. Do not copy and paste the values! Create links to Sheet 2 (by now, renamed “DirectCap”). By now, you should know Excel well enough to make one cell-reference formula and then copy it for the rest of the needed rows.

• Apartment rental income will increase at a rate of 4% per year • Miscellaneous Income will increase at a rate of 3% per year • Vacancy and collection losses will remain at 3% • Operating expenses and capital expenditures will increase at 3% per year except ad-valorem

taxes and property management. o Ad-valorem taxes will increase 2% per year, the maximum allowed by law in California. o Property management will increase as a function of revenue because the management

fee is a percentage of effective gross income.

FIN 331 | Final Project

San Diego State University | Finance Department

• Due to the trend of rising interest rates, you project a resale cap rate in five years of 5.0%.1 • Selling costs at the end of the fifth year will be about 4% of the sale price. • SDREIT typically requires at least a 6% return on their equity investment. • The bank will charge a 1% loan fee at the beginning of the term. Is that a Year-1 cost?

DCF, IRR, and NPV 1. Construct a discounted cash flow based on the preceding information. As a standard practice,

yearly cash flows are arranged column-wise. a. Use the required-equity amount from the underwriting exercise as the Year-0

investment. Calculate the IRR of the project’s BTCFs2 including the reversion. 2. Calculate the NPV using SDREIT’s required return as the discount rate. 3. Bonus Points: Use a data table in Excel to quantitatively illustrate sensitivity with one or two

variables.

Part D - Memo Prepare a 1- to 2-page memo 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. Start with an introductory paragraph about the property with made-up details. Conclude that paragraph with your recommendation. Then provide a table with key metrics (e.g. price, equity, debt). I am leaving other table contents to you to decide what is important. Follow the table with more discussion of your analysis. Discuss the sensitivity of your findings and the main areas of vulnerability.

Submittal Instructions Do all your analyses in a single MS Excel workbook, using multiple worksheets as detailed in the instructions above. Name your sheets accordingly. Upload your analysis (MS Excel) and memo (MS Word/PDF) files for grading. Save your files using the following naming convention:

Excel (partner activity): LastName_LastName_Analysis (note both partners’ last names in file name)

Word (individual activity): LastName_FirstName_Memo

1 Note that the market value at the end of five years is based on the property’s potential NOI in the following (sixth) year. Therefore, the five-year cash flow requires a sixth year going as far down as NOI. But be careful not to count the Year-6 income in your income stream. It is there only for setting the sale price. 2 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