Summer 2021 - RED 437 Midterm: Testing the Basics The midterm has been designed to test two basics. The first is Excel and finance modeling, and the second is underwriting and assessing risk -- in this case, this means using your models to make decisions about how best to operate a property. Your job on the exam is to display mastery. I am less concerned about getting “the right answer” than I am interested in seeing how well you understand pro forma building and thinking about using your pro forma. As such, please be complete in your answers. Your answers need not be long, but I should be able to grasp your argument. You will be graded on what you turn in (not what you meant or what was implied). Even if I think you know more, I can’t give you credit unless you write it down. At the end of three hours (or sooner), you are to upload three files: one file will include your Excel models, the second file will include your analysis memo that addresses the written questions below, and the third is your Attestation that your work is your own and you did not use any outside help nor provide help to others. Please name your files with just your last name. For me that would be “miller.xlsx” and “miller.docx”. Grading This Midterm grade is graded on a 100-point scale (15% of final grade). Your Investment Memo and Recommendation, and Excel Financial model will be evaluated based upon the following general criteria. Approaching Mastery Displays Good Understanding Needs Improvement Clearly identifies all risks in assumptions and mitigation measures. Thorough sensitivity and scenario analysis. Identifies some risks in assumptions, but may lack others, including mitigation measures. Sensitivity analysis on some variables. No attempt to present analytical approach to results. Risk assessment not addressed. Recommendation is supported by financial modeling results and metrics Recommendation may generally be sound, but not supported by modeling results and metrics. Analysis may be based upon unsupported modeling, or risks are too general and common. No sensitivity or scenario analysis. Recommendation is opinion based, and not supported by modeling results and metrics. Formula consistency across all years of the analysis period Minor formula inconsistencies, but generally accurate. Formulas are not dynamic and inconsistent across modeling years. Debt Calcs (Fixed/Variable) (30%) All debt constraints met, and able to adapt to changing assumptions (‘dynamic’) Calculations correct, but not dynamic and requires additional steps for recalibration. Reliance on amortization tables and not simplified debt calcs. Formulas missing, hard coded cells, and unable to react to changing assumptions. Lack of understanding of debt modeling and loan calcs. Sensitivity Analysis (20%) Accurate scenarios for Optimistic, Pessimistic, and Expected outcomes. Single variable sensitivities accurately calculated for identified assumptions. Sensitivities attempted but not calculated accurately. Investment Memo Analysis & Risk (10%) Recommendation (10%) Pro Forma CF Catg. and Metrics (30%) Lacking or Not Attempted No recommendation, and/or no attempt to reference modeling results or metrics. No sensitivity or scenario analysis attempted. 437 bem summer 2021 v1.4 The Questions Your acquisitions team has identified a property to acquire. Your job is to underwrite two holding period strategies and recommend the better of the two for your firm. Your firm won the bidding to acquire the 225-unit Odyssey Apartments and is now in due diligence. Your firm will close on the property soon, but your focus is on how long your firm might hold the property. The acquisition team underwrote it as a short-term hold. They saw the sale price as aggressive for the neighborhood as it is but thought the neighborhood would continue to improve. This would explain how they chose a going-in cap rate and going-out cap rate as both 4.00%. The acquisition team’s pro forma is included in the Midterm folder. Please open their pro forma and activate it. That is, make sure that all of the black cells have the appropriate formulas in them. Note that their strategy was to earn a high IRR in a short, two years of holding the property. After rebuilding the acquisition team’s pro forma, test their return assumptions by changing both the annual growth rate and exit assumptions. Use a 4.25% going out cap rate and 3.5% annual rent growth rate to determine how robust the expected returns are. Please report these in your write up. Comment as needed. Also included in the acquisition team’s underwriting are some assumptions that your asset manager would like you to explore. Rather than try and squeeze rents and flip the property, she thinks that it may be wise to hold the property during what could be a long transition as the neighborhood improves. She would like you to explore a ten-year holding period using the other assumptions provided under the “Parking Your Capital” assumptions (see template). The loan for this longer hold is a more typical amortizing loan; those assumptions have also been provided. After building your ten-year holding pro forma using the asset manager’s assumptions, test these returns by underwriting both optimistic and pessimistic scenarios. The optimistic scenario involves annual rent growth of 3.75% and an exit cap rate of 4.25%. The pessimistic scenario involves annual rent growth of 2.75% and exit cap rate of 4.75%. Please report the returns for both of these scenarios and discuss as needed. In light of your underwriting for both the short hold (“Flip”) and the longer hold (“Parking Your Capital”) options, which one is better? Why? Do you see any underwriting mistakes or problems in the cash flows? Discuss as needed. 437 bem summer 2021 v1.4 Odyssey Apartments: Flip or Park? Hold Option 1 - "Push Rents & Flip" Units Projected Average Unit Rent $/month Vacancy (% PGI) OpEx $/month/unit Annual Rent Growth Rate Annual OpEx Growth Rate Going-In Cap Rate Exit Cap Rate Cost of Sales (% of sale price) LTV DSCR (minimum) Loan Amount Interest Rate Loan Term Amortization Period Origination Fee Monthly Mortgage Payments Annual Debt Service 225 $2,150 5.00% $750 4.00% 3.00% 4.25% 4.25% 1.50% Loan 1 70% 1.3 57,478,235 4.25% years 5 Interest Only 2.0% (203,569) (2,442,825) Hold Option 2 - "Parking Your Capital" Units Projected Average Unit Rent $/month Vacancy (% PGI) OpEx $/month/unit Annual Rent Growth Rate Annual OpEx Growth Rate Going-In Cap Rate Exit Cap Rate Cost of Sales (% of sale price) 225 $2,150 5.00% $750 3.50% 2.50% 4.25% 4.75% 1.00% Purchase Price PGI Vacancy EGI OpEx NOI Sale Price Cost of Sales Unlevered Cash Flow Unlevered IRR Unlevered EM Loan Proceeds Annual Debt Service Loan Repayment Debt Cash Flows Cost of Debt Debt Yield Debt Service Coverage Ratio Levered Cash Flows Levered IRR Levered EM Loan 1 LTV (maximum) Debt Yield (minimum) Interest Rate Loan Term Amortization Period Origination Fee Max Loan (LTV) Max Loan (DY) Loan Amount Annual Debt Service 75% 6.00% 3.50% 10 30 years 0.5% Year 0 (82,111,765) Year 1 5,805,000 (290,250) 5,514,750 (2,025,000) 3,489,750 (82,111,765) 8.05% 1.16 Year 0 56,328,671 3,489,750 Year 1 Year 2 6,037,200 6,278,688 (301,860) (313,934) 5,735,340 5,964,754 (2,085,750) (2,148,323) 3,649,590 3,816,431 89,798,379 (1,346,976) 92,100,993 Year 2 (2,442,825) 56,328,671 5.33% (2,442,825) (57,478,235) (2,442,825) (59,921,060) 6.07% 1.43 (25,783,094) 13.8% 1.29 1,046,925 Year 3 6.35% 1.49 32,179,933 Investment Memo Analysis & Risk (10%) Clearly identifies all risks in assumptions and mitigation measures. Thorough sensitivity and scenario analysis. Identifies some risks in assumptions, but may lack others, including mitigation measures. Sensitivity analysis on some variables. Analysis may be based No attempt to upon unsupported present analytical modeling, or risks are approach to results. too general and Risk assessment not common. addressed. No sensitivity or scenario analysis. Recommendation is No recommendation, opinion based, and not and/or no attempt to supported by modeling reference modeling results and metrics. results or metrics. Recommendation (10%) Recommendation is supported by financial modeling results and metrics Recommendation may generally be sound, but not supported by modeling results and metrics. Pro Form
Purchase answer to see full attachment