Sensitivity Analysis - Professors' Slide Notes.docx

Contents Slide 1/19 - Cover Slide 3 Slide 2/19 – Key Learning Outcomes 4 Slide 3/19 – Transition Slide “What is Sensitivity Analysis” 5 Slide 4/19 – Sensitivity Analysis Used by Professional Investors 7 Slide 5/19 – Evaluate Impact of Risks 9 Slide 6/19 – Sensitivity Tables 10 Slide 7/19 – 1-Sid...

Contents Slide 1/19 - Cover Slide 3 Slide 2/19 – Key Learning Outcomes 4 Slide 3/19 – Transition Slide “What is Sensitivity Analysis” 5 Slide 4/19 – Sensitivity Analysis Used by Professional Investors 7 Slide 5/19 – Evaluate Impact of Risks 9 Slide 6/19 – Sensitivity Tables 10 Slide 7/19 – 1-Sided Sensitivity Table 12 Slide 8/19 – 2-Sided Sensitivity Table 13 Slide 9/19 – Did Investors Sensitize V/L Before the Pandemic? 15 Slide 10/19 – What happens to IRR if V/L increase from 5% to 25%? 17 Slide 11/19 – Transition Slide - 3. Evaluating Risk With Sensitivity Tables 18 Slide 12/19 – Q4 2019 Office Report - San Francisco Case Study 19 Slide 13/19 – Using the assumptions to model returns 21 Slide 14/19 – Q3 2023 Office Report - San Francisco Case Study 23 Slide 15/19 – Returns given 0% growth rate and 7% exit cap rate 25 Slide 16/19 – Transition Slide - Building sensitivity tables 27 Slide 17/19 – Build a 1-sided sensitivity table 29 Slide 18/19 – Build a 2-sided sensitivity table 31 Slide 19/19 – Build a 2-sided sensitivity table 33 Slide 1/19 - Cover Slide Approx 2 mins This lecture covers an introduction to sensitivity analysis and sensitivity tables. Linking back to the previous lecture “Evaluating Risk in Real Estate”, in this lectures students will learn how investors use sensitivity analysis to evaluate risks in Real Estate i.e., evaluate the impact of the identified risks on the returns of the investment. There are 20 slides in this deck. Expect to spend 1 hour on this presentation (average 3 mins per slide). On each slide, there are included example discussion questions you can raise with students, as well as links to additional resources you may want to engage with during class. Slide 2/19 – Key Learning Outcomes Approx 4 mins By the end of this lecture, students will be able to: Understand how and why investors use sensitivity analysis when making investment decisions. Understand how 1-sided and 2-sided sensitivity tables are used in industry. Evaluate a sensitivity table to understand the impact of changing key assumptions on investment returns. After this lecture, students can use the Blended Teaching platform to learn how to: Build 1-sided and 2-sided sensitivity tables in Excel. External Resources Blended Teaching video: 1.1 Module Overview Slide 3/19 – Transition Slide “What is Sensitivity Analysis” Approx 3 mins Let’s start the lecture by what is sensitivity analysis and who uses it? This slide can be used to start an initial discussion and ask students (see potential questions at bottom of page). Discussion Questions: Can anyone tell us what sensitivity analysis is? Who might use sensitivity analysys and why? Slide 4/19 – Sensitivity Analysis Used by Professional Investors Approx 6 mins Sensitivity analysis determines how different independent inputs such as assumptions in a cash flow model, affect a particular dependent variable such as cash flow returns. Recall the 5 steps to evaluate risks from the earlier module called “Evaluating Risk in Real Estate”. An investor must: Identify the key risks in potential investment. Understand the risks Evaluate the impact of those risks on key outputs (such as returns and debt covenants) Mitigate risks by taking steps to ameliorate or prevent them impactin the returns or debt coventants) Price the risk by determining how much the remaining risks reduce the potential returns by. Sensitivity analysis is used by professional investors in steps 3 and 5 of the list above, where investors evaluate the impact of the risks, and then when they come to price the risks that they can’t mitigate. Typical dependent variables that are tested include returns and key risk metrics such as IRR, Cash-on-Cash, V/L, DSCR, etc. Discussion Questions: What do you think are the assumptions (independent variables) investors will typically sensitise? What do you think are the dependent variables? External Resources Blended Teaching video: 1.2 What is Sensitivity Analysis Slide 5/19 – Evaluate Impact of Risks Approx 3 mins Investor will perform sensitivity analysis on assumptions that have the potential to change and significantly impact returns of a deal. For example, an investor won’t be sensitising selling costs or broker commission. They will typically sensitize, assumptions such as: Interest rate LTV Rental growth rate Exit cap rate V/L Etc… They will typically want to understand the impact of changing assumptions on returns i.e., investment metrics such as: IRR Equity Multiple Cash-on-Cash NPV etc… It’s important to note that investors will sensitize any metric that is of interest to them, for example lender metrics such as: Debt yield DCSR External Resources Blended Teaching video: 1.2 What is Sensitivity Analysis Slide 6/19 – Sensitivity Tables Approx 2 mins The next section will cover sensitivity tables – the tool to perform sensitivity analysis in Excel. Discussion Questions: Have you ever heard of sensitivity tables? External Resources Blended Teaching video: 2.1 What Is a Sensitivity Table Slide 7/19 – 1-Sided Sensitivity Table Approx 6 mins A sensitivity table is a tool you can build in Excel to perform a sensitivity analysis on a grand scale, immediately and without errors. There are two key types of sensitivity tables: One-sided sensitivity table – tests the impact of changing one assumption on the returns. Two-sided sensitivity table – tests the impact of changing two assumptions on the returns. This slide focuses on One sided sensitivity tables A one sided sensitivity tables test the impact of varying one assumption on unlimited number of outputs (aka dependant variables). We’re testing the impact of the Going Out Cap Rate assumption (the Independent variable) on the Levered IRR (the dependant vaieable). We could add as many dependent variables as we want after the Levered IRR column, which would show us the impact of changing the Going Out Cap Rate on additional the investment/lender metrics. The cells in the image highlighted in gray represent the base case i.e., the assumptions most likely to occur. So, in the 1-sided sensitivity table shown, we’re sensitizing the Levered IRR by changing the Going Out Cap Rate. The base case (or the current assumption) for exit cap rate is 6.45% so with the 1-sided sensitivity table shown we can understand what happens to our levered IRR and NPV if the cap rate changes by multiples of 50bps. The 1-sided table clearly shows that a 50 bps move in exit cap rate has a significant impact on the returns. For example, if the cap rates dropped to 5.95% the levered IRR would increase by 3.1% and the levered NPV of the deal would increase by almost $700,000. External Resources Blended Teaching video: 2.1 What Is a Sensitivity Table Slide 8/19 – 2-Sided Sensitivity Table Approx 4 mins Moving on to the 2-sided sensitivity table – the key difference is that a 2-sided sensitivity table will help us understand the impact of changing two variables (assumptions) on a single dependent variable. This is important because often assumptions change together, and not in isolation. his allows investors to measure the impact of combined changes. Usually, it’s a lot more significant that an individual independent variable For example, as interest rates rise, LTVs offered by lenders are reduced. So testing the increase in interest rate and fall in LTVs for an investment where sourcing debt is important will help investors understand the true impact of the evolving interest rate environment o their potential future investment. We can clearly see that if Interest Rate increases the Levered IRR decreases. This is obvious as the deal cost will increase due to the increased interest costs. And when the LTV decreases the Levered IRR will also decrease as greater equity is required to be invested. It’s a double whammy on the Levered IRR. External Resources Blended Teaching video: 2.1 What Is a Sensitivity Table Slide 9/19 – Did Investors Sensitize V/L Before the Pandemic? Approx 2 mins This graph shows how the vacancy rates changed between Q4 2019 and Q1 2023 across 10 US metros. This is not the vacancy rate! San Francisco vacancy increases from 5% to 24.8% in two years. At the same time, the vacancy in 9 other metros increased by more than 10% points. External Resources Source Article: Out of Office: US vacancy rates hit record high Slide 10/19 – What happens to IRR if V/L increase from 5% to 25%? Approx 3 mins Using the cash flow from the After Tax DCF Assignment as an example, we can see the impact of V/L increasing from 5% to 25% on the returns. Imagine an investor in San Francisco bought a building in 2019 with the assumption of 5% vacancy. Within 2 years between 2020 and 2023, the average office vacancy in San Francisco increased to 25%. The investor could be much worse (struggling to rent any space) even if the building had an average 25% vacancy the BT IRR would decrease by 16% from approximately 23% to 7%. External Resources Example Excel for students to use: Sensitivity Table in Excel Slide 11/19 – Transition Slide - 3. Evaluating Risk With Sensitivity Tables Approx 3 mins Now let’s talk about how investors use sensitivity analysis to evaluate risks. First – recall what evaluating risks means. There are unlimited risks in the world, so investors need to understand what the most important risks are that are surrounding a deal and what is the possible impact on returns. Slide 12/19 – Q4 2019 Office Report - San Francisco Case Study Approx 5 mins The information provided on the slide are taken from a Q2019 Cushman & Wakefield report on the SF market. The above Q4 2019 San Francisco office market report show the general feeling about the office market at the time. The section headlines include: San Francisco economy at full employment Vacancy remains minimal Asking rents on the rise The graph presents office asking rent increasing every quarter and vacancy constantly decreasing in the period Q1 2015 – Q4 2019. The next slide will show the example returns with assumptions based on the Cushman & Wakefiled Q4 office market report. External Resources Source Article: Q4 2019 San Francisco Office Report Slide 13/19 – Using the assumptions to model returns Approx 3 mins The 5 year average rental growth rate between 2015 and 2020 was approximately 7% and the Cap rate at 5%. With these assumptions an example model shows a deal will return 8.3% levered IRR. Let’s keep this in mind and see how the market changed since Q4 2019. External Resources Source Article: Q4 2019 San Francisco Office Report Slide 14/19 – Q3 2023 Office Report - San Francisco Case Study Approx 4 mins Fast forward to the end of 2023 and a lot has changed. The market sentiment is the exact opposite of 2019. Asking rent is at $70 not $82 anymore and decreasing quarter by quarter. Vacancy is the highest in the country at 30.4% and constantly increasing. Net absorption is at negative 2.3 million square feet, which indicates a collapse in demand – significantly more space is coming in to the market vs what is being leased. External Resources Source Article: Q3 2023 San Francisco Office Report Slide 15/19 – Returns given 0% growth rate and 7% exit cap rate Approx 3 mins Assuming a negative rental growth rate at -3%, which leads to the rent level of $70 psf investors would achieve a -22% IRR! The collapse in demand caused the exit cap rate to increase External Resources Source Article: Q3 2023 San Francisco Office Report Slide 16/19 – Transition Slide - Building sensitivity tables Approx 1 mins The next section will cover building sensitivity tables in Excel. Slide 17/19 – Build a 1-sided sensitivity table Approx 1 mins We won’t be going into any details here, but you can access the Blended Teaching platform here. You will see detailed, step by step video on how to create a sensitivity table in Excel. External Resources Blended Teaching video: 3.1 Building a 1-Sided Sensitivity Table Slide 18/19 – Build a 2-sided sensitivity table Approx 1 mins We won’t be going into any details here, but you can access the Blended Teaching platform here. You will see detailed, step by step video on how to create a sensitivity table in Excel. External Resources Blended Teaching video: 3.2 Building a 2-Sided Sensitivity Table Slide 19/19 – Build a 2-sided sensitivity table