Need help with the Excel and questions 1-3. Got the other 2
Individual Homework Assignments REVISED Fall 2019 (4) 2 | P a g e WEEK TWO INDIVIDUAL ASSIGNMENTS Intended Learning Outcomes for these individual exercises Reinforce understanding of how to properly setup a financial model and reinforce XLS skills. Recreate the following Back of the Envelope (BOE) setup in XLS. Submit the XLS file. Answer the following questions by responding below. What are the financial and non-financial risks exhibited by this investment? What would you adjust to this model and why? 3 | P a g e QUICK EVALUATION (Back of the Envelope "BOE'") TO DETERMINE IF FO IS A VIABLE INVESTMENT Gross Building Size 12,000 sf Rentable Building Size 10,000 sf Efficiency Ratio 83.3% Cost Per SF Total Purchase Price - BUILDING 141.67 1,700,000 Closing Costs - Transfer/Recordation Costs 2.83 34,000 Closing Costs - Title Insurance 1.42 17,000 Closing Costs - Legal 10,000 Lender 3rd Party Reports (appraisal, hard, soft cost review) 15,000 Total Costs 1,776,000 Total MedStar $15.00 sf 5,000 sf 75,000 RiseCo $20.00 sf 2,500 sf 50,000 Harford Co Gov $20.00 sf 2,500 sf 50,000 Sub-Total 175,000$ Vacancy Loss 5.00% increase per yr (8,750)$ Effective Gross Income 166,250$ Expenses - non-recoverable $1.00 rentable SF (10,000) Reserves $0.20 rentable SF (2,000) Total Cost 1,776,000$ Loan per LTV Ratio 75.00% LTV Ratio Loan Amount 1,332,000$ Interest Rate 5.00% Amortization 25 years Annual Payment (Debt Service) $93,441 Required Equity 444,000$ Year 1 Year 2 Year 3 MedStar 1.00% increase per yr 75,000 75,750 76,508 RiseCo 0.00% increase per yr 50,000 50,000 50,000 Harford Co Gov 2.00% increase per yr 50,000 51,000 52,020 Sub-Total 175,000$ 176,750$ 178,528$ Vacancy Loss 5.00% increase per yr (8,750.00) (8,837.50) (8,926.38) Effective Gross Income 166,250$ 167,913$ 169,601$ Expenses - non-recoverable 2.00% increase per yr (10,000.00) (10,200.00) (10,404.00) Reserves 0.00% increase per yr (2,000.00) (2,000.00) (2,000.00) Net Operating Income 154,250$ 155,713$ 157,197$ Debt Service (93,440.87) (93,440.87) (93,440.87) Cash Flow After Debt Service 60,809$ 62,272$ 63,756$ Terminal Cap Rate 9.00% NOI (Year 3) 157,197 - - Reversion 1,746,635 Less Cost of Sale 5.00% (87,331.74) Net Reversion 1,659,303$ Less Debt Balance 1,245,319 Net Proceeds 413,984$ Acquisition Yr 1 Yr 2 Yr 3 Investment (444,000) Cash Flow 60,809 62,272 63,756 Net Proceeds 413,984 Project Cash Flow (444,000) 60,809 62,272 477,740 Leveraged IRR ???? Operating Income Loan and Equity Sizing Introduction of Time to this Analysis Asset Size Costs to Close Return on Equity Hint…create an IRR formula for the strip shown in grey above… 4 | P a g e WEEK TWO INDIVIDUAL ASSIGNMENTS Intended Learning Outcomes for these individual exercises Reinforce understanding of the Time Value of Money (TVM) formulas both conceptually and mechanically. Many in the industry know the mechanics (using Excel) but do not understand the concepts and their application and mathematical underpinnings. A careful review of Chapter 3 in the text and the sample problems in the chapter is important, especially for those without a deep finance or accounting background. Formulate a business plan and understand its important elements Brueggeman Text Chapter 3 For the following questions, create the following cash flow and reversion in Excel. 1. Why should defining a mission statement and business plan precede the evaluation of Freedom Office (FO) as an investment opportunity? In two paragraphs or less, craft a concise mission statement and then business plan for FO. Hint: One must start with profiling the investor and establish goals for targeted returns (both cash-on-cash returns and reversion price), planned holding period, risk tolerance and overall investment objectives. 2. TVM Principles – Using Excel, provide a specific example in which a real estate investor would consider using a future value calculation to make a real estate investment decision. Please prepare an Excel worksheet to illustrate this example similar to the worksheet below. Please paste below with CTRL~ or submit the Excel worksheet. 3. TVM Principles and Mechanics – Analyze the data below in Excel and then answer the following questions. A real estate investment has the following