ASSIGNMENT: USING THE CH 11 OFFICE BUILDING PRO-FORMA AS A TEMPLATE, CREATE A SIX YEAR EXCEL SPREADSHEET PRO-FORMA USING THE FOLLOWING INFORMATION (upload to Canvas Assignments when completed):

You have an opportunity to buy the 23 site Plum Creek RV park for $250,000. The park caters to extended stay residents (six months or longer) and charges a market rental rate of $240 per site per month. You expect to be able to raise site rental rates 3% per year to account for inflation. There are no tenant reimbursements or pass-throughs. You plan a five year holding period so you create a six year proforma.

The owner reports an annual vacancy rate of 10% but you believe an annual 15% vacancy rate every year of the holding period is a more realistic estimate. You use 15%.

For expense estimates, you rely on public records (tax office), an income/expense statement provided by the owner/seller, and Darrell Hess & Associates, a national manufactured home park brokerage company that compiles expense data from hundreds of parks nationwide.

The previous years property taxes were $5,042. The new assessment and tax rate has not been set, so the previous years taxes are used in your pro-forma for the first year.

Hazard and liability insurance is estimated to be 2% of effective gross income (EGI) in first year.

Each site is individually metered for electricity and the tenants are responsible for their usage. The park owner reported annual water and trash removal expenses of $5,460 for the previous year, and this amount appears reasonable for your first year projection.

The annual administrative/management expense (part time on-site manager, advertising, legal fees, accounting fees and office expenses, etc.) is estimated to be 29% of EGI by Darrell Hess & Assoc.

The owner did not report any maintenance expense as he did all of the work himself. You believe a $2,000 first year expense is reasonable for the road maintenance, landscape maintenance, etc.

You expect expenses to increase 3% each year of the five year holding period except insurance and management expenses tied to EGI.

You have obtained a loan commitment from a bank for 80% (20% down payment aka equity contribution) of the purchase price with a 20 year amortization at 6% interest.

1) Create a six year Pro-forma statement of cash flows on an Excel spreadsheet. 2) Calculate the following ratios and indicators (all before income tax) by Excel

formulas at the bottom of the spreadsheet to three decimals: A) Operating expense ratio (annual operating expense/EGI); according to Darrell Hess &

Associates, operators can expect a 50% operating expense ratio.

B) All Five Years of Debt Coverage Ratio (NOI/debt service); 1.3 or greater is considered good by the lender.

C) All Five Years Return on Equity (BTCF/equity invested); investor surveys indicate 13% or better is expected for each year.

D) Using the IRV formula, calculate the indicated overall rate (cap rate) RO for Year One to see if it is within the 9-11% range for manufactured home parks indicated by a national investor survey. Use the $250,000 asking price as the value in IRV for the calculation.

E) Calculate the value of the subject after the five year holding period (the reversion) using a 10.5% terminal RO. Remember to carry the pro-forma to a sixth year to obtain Year Six NOI.

F) Calculate the unleveraged IRR of the cash flows using the $250,000 asking price. G) Calculate the NPV of the property using a 12% IRR.