Name: Rawan Instructions Excel Cash Flow Analysis & Valuation Exam Using Excel, develop a 5-year discounted cash flow to solve the case problem below. Your Manager has asked you to analyze an investment opportunity by performing a five year Excel cash flow analysis with with associated investment returns to answer the five questions below. The subject property is a 100,000 s.f. medical office building with an asking price of $9,500,000. The property has doctors' practices as tenants and the suites range in size from 5,000 to 10,000 s.f. The building is currently 90% leased. Each of the tenants pay $15.00 s.f. on a triple-net basis and the rents increase 3% on an annualized basis. Below is a table with last year's operating expenses. The Landlord pays the expenses and is then reimbursed by the Tenants. The asset management and administrative fees cannot be passed through to the tenants per the lease terms. All expenses increase by 2% on an annual basis. In addition, please include $0.25 s.f. in capital reserves starting in year one below net operating income. Capital reserves should be increased by 2% in each of the subsequent years. The equity investors will contribute $3,000,000. Chase Bank will provide an acquisition loan of $6,500,000 for a five-year term on a twenty-five year amortization at 4.5% interest. The loan origination fee is 1% of the loan amount. REIMBURSABLE EXPENSES Real Estate Taxes Insurance Maintenance Repairs Landscaping Security Expense Statement (Previous 12 months) $79,000 $15,000 $27,000 $19,000 $ 6,000 $12,000 Pest Control Utilities Property Management Fee 3% of EGI (including reimbursable expenses) Administrative Fees Asset Management $ 2,500 $18,500 3% $ 4,000 $20,000 The company is only interested in acquiring the medical office building if it can obtain an annual 11% unlevered and 13% leveraged rate of return over a five-year holding period. At the end of the holding period, the property is anticipated to sell for a 9.5% terminal cap rate (applied to the sixth year NOI) and sale costs are expected to equal 4% of the gross sale proceeds. Please answer the following questions to help your company analyze this investment opportunity: 1. What is the going-in capitalization rate of the investment assuming an acquisition price of $9,500,000 and that year 1 NOI is calculated using "below the line" treatment of capital expenses? 2. What are the estimated net proceeds received from the sale of the property at the end of year 5 based on the assumptions discussed above? 3. 4. What is the maximum amount your firm should be willing to pay for the property based on a discounted cash flow analysis? What are the leveraged and unleveraged NPVS and IRRS generated by the property assuming an $9,500,000 acquisition price? 5. Should your firm invest in the Medical Office Building? EMAIL THE COMPLETED EXCEL SPREADSHEET AND THIS COMPLETED DOCUMENT BY 9:45 PM TODAY TO: Julie.Lynch@UTDallas.edu The file must have your name example: julielynch CF Excel Exam.xls and julielynch investment questions