REIT Discounted Cash Flow valuation model, Part 7, Sensitivities. Having just calculated the implied share prices using both the multiple method and the terminal value method, we now have the opportunity to run some sensitivities. The discounted cash flow valuation for either method is full of a lot of assumptions. It's full of assumptions in the cash flow forecast. It's full of assumptions in terms of the multiple or the growth rate that's being used. Obviously the WACC is full of another set of assumptions, so being able to sensitize these two valuation approaches is very helpful. I've created down at the bottom some data tables. We're gonna calculate some sensitivities on the multiple method, as well as perpetual growth method or perpetuity method. In the multiple method, we're gonna be sensitizing both the WACC and the exit multiple. And then for the perpetuity method, we're going to be sensitizing the WACC and the long-term growth rate. So the first thing we need to do is put our anchor formula in for the multiple method and that's simply going to be the share price as derived by the multiple method.

Now, I've chosen to use the same color font as the fill color because I don't want that formula to show. It's just a cosmetic thing, but it is there. And now what I need to do is put in some values. In a data table, you can't link directly to the variables that are driving that cell or it won't work. But basically we're gonna kind of approximate what we used. Now the WACC that we calculated in this model was 5.7%, so I would like that to be my center number. And then I'm gonna vary it by a half a percent, or 50 basis points, and the same on the other side.

For the exit multiple, I chose 17, and I'm going to go up one here and then down one here.

To complete the data table, we need to select the entire table including the anchor cell, and then we need to hit, depending on which version of Excel you're using, if you're using prior to 2010, you're gonna hit ALT + D + T. If you're using 2010 and beyond, you're gonna hit ALT + A + W + T. For the row input, we want to sensitize the WACC. We can either scroll up to the top and link to the WACC cell which is in C21, or we can simply type wacc. Now for the column input, we want the EBITDA exit multiple and for that we're gonna type TermMult or we could scroll up to the top and link to cell C31. And we see our table is populated with the various sensitized share prices. The center of the table should match more or less to what our share price is using the terminal multiple. It's not going to be exact because we've manually typed in our WACC as opposed to linking it to the exact calculation. And again, that's just one of the quirks of data tables. So if we scroll down below, we can do the same thing for our perpetual growth method. We are going to link to the share price as calculated in the perpetual growth method. And then we are going to, for our long term growth rate, we're gonna hard code in what our center value was, which was 2.35, and then we're gonna sensitize by 25 basis points. So it's going to equal our center value plus .0025 and then we can copy that up and then subtract .0025.

Copy that down. Now for our WACC, we can just save ourselves the hassle and link to the cells above. Again, I'm gonna select my entire table, ALT + D + T, if you're using prior to 2010, or ALT + A + W + T. My row input is my WACC, my column input, which is here, it's gonna be tg and I click, okay. I see 165.70 is the center value and that is pretty close to what I have here. So I'm comfortable that the table is correct and I can see the range of values for both long-term growth and WACC. We can also see how sensitive the share price is to the slightest increase in the long-term growth rate for the terminal value. If your tables are not displaying the same sensitivities as mine, or if you have the same share price in each of the columns, one of the things you might want to check is to make sure your calculations are set properly. So you're gonna want to go to ALT + F + T and make sure that you either have your calculation set to automatic or automatic except for data tables. If it's set to automatic except for data tables, you're gonna need to hit F9 to update the tables. This completes our sensitivity section and the the entire model for the discounted cash flow valuation.