I learned early in my first semester that my skills with Microsoft Excel were in need of serious improvement. By the second semester, I realized that I might be the least proficient Excel user in the entire program. This is sort of embarrassing considering that I was a finance major and work in the commercial real estate business. That being said, I am determined to improve.
This MP project is very timely for me. It coincides with the need for me to analyze several prospective investments for my company. Recently I have taken the time to review Professor Noonan’s slides and from that decided on some of the skills that I plan to acquire. I have since learned how to use pivot tables as well as the sensitivity analysis feature. I found some youtube videos that really helped me fine tune these skills:
I recently used sensitivity analysis as part of my analysis in evaluating an apartment complex. See below:
| $ 264,976.47 | 37,000 | 38,000 | 39,000 | 40,000 | 41,000 | 42,000 | 43,000 |
| 15,000 | 258,823.53 | 270,588.24 | 282,352.94 | 294,117.65 | 305,882.35 | 317,647.06 | 329,411.76 |
| 16,000 | 247,058.82 | 258,823.53 | 270,588.24 | 282,352.94 | 294,117.65 | 305,882.35 | 317,647.06 |
| 17,000 | 235,294.12 | 247,058.82 | 258,823.53 | 270,588.24 | 282,352.94 | 294,117.65 | 305,882.35 |
| 18,000 | 223,529.41 | 235,294.12 | 247,058.82 | 258,823.53 | 270,588.24 | 282,352.94 | 294,117.65 |
| 19,000 | 211,764.71 | 223,529.41 | 235,294.12 | 247,058.82 | 258,823.53 | 270,588.24 | 282,352.94 |
| 20,000 | 200,000.00 | 211,764.71 | 223,529.41 | 235,294.12 | 247,058.82 | 258,823.53 | 270,588.24 |
| 21,000 | 188,235.29 | 200,000.00 | 211,764.71 | 223,529.41 | 235,294.12 | 247,058.82 | 258,823.53 |
The left-hand column (starting with 15,000) refers the Operating Expenses and upper row (beginning with 37,000) refers to Revenue. The info in the middle shows the resulting value (based on an 8.5 Cap rate). If you’re not in the Real Estate business, a cap rate is NOI/VALUE, essentially a measure of the rate of return. This proved fairly helpful as I went over it with our current apartment manager to confirm our offer.
I also did analyses for revenue sensitivity to price and vacancy and the Operating Expense sensitivity to some of the specific expenses. The Revenue sensitivity illustrates how much vacancy we could bear under certain price levels. The Operating Expense analysis displays the critical expenses.
I am well aware that, for most of you, this is very basic. I am just glad to address this weakness.
I do a lot of sensitivity tables at my job. I have done valuation and loan underwriting for all types of commercial real estate.
I often sensitize the cap rate (going-in capitalization rate or NOI/value for those not familiar) and something else such as market rent. Other combinations I have used include terminal cap rate/discount rate and market rent growth rate/cap rate.
One formatting thing I like to do is to turn the value in the top left corner of the sensitivity table to white font because it often confuses readers who see a number in the top corner. You can’t delete it because it will mess up the table, so I have found that white font accomplishes the same thing while keeping the sensitivity table active.
Another item to note is that ARGUS (an industry software used for more complicated commercial real estate, especially when there are multiple leases involved) has a built-in sensitivity feature that lets you sensitize the capitalization rate (NOI/value). Many people still use ARGUS to generate the cash flows but then sensitize it in Excel on their own because it is easy to do with the table function.
Very helpful. Appreciate the insight.