In Excel you can use two-way Data Tables (keyboard shortcut: Alt+D+T) to construct a sensitivity analysis output matrix. These matrices can be very insightful as to how a key performance indicator output will change given the simultaneous changing of two input variables.
A two-way data table showing sensitivity of the property's capitalized value to changes in Rent PSF and Sale Cap Rate.
For instance, using the table above as a reference, let’s say we want to see how the capitalized value (output shown in the table interior in black) reacts when changes are made simultaneously to the property Rent PSF (shown across the top-most row in blue) and the Sale Cap Rate (shown down the left-most column in blue).
The Data Table functionality provides us with the spectrum of outputs that result from the intersection of these input variables. For instance, a $64.00 rent and a 7.00% sale cap rate would yield a property value of $4,971,429.
The question becomes, though, what should the range of input values be? What increment should be used for rent and what for sale cap rate?
In other words, should it be this?
Rent increments of $1 PSF and cap rate increments of 25 basis points.
Or should it be this?
Rent increments of $2 PSF and cap rate increments of 100 basis points.
Before we answer this, know that the increment does not need to be constant across the range of the data point (see the very top table rent values as an example). Oftentimes, however, we will use a constant increment for the sake of making the table easier to process mentally.
The answer to the question is really an answer to this: which range of values would be the most realistic and relevant to this transaction? These inputs will produce the most meaningful spectrum of outputs to the intended audience.
So it comes down to your market knowledge. If a lease has never been signed above $67.00 PSF, then maybe the middle table is spot on. If similar properties have traded only above an 8 cap, however, the cap rate inputs are likely too optimistic/aggressive. In this case all three table sale cap rate ranges are not as relevant as they could be.
All of this to say that we should always remember that Excel is a tool — it is not the Gospel… We should use Excel to simulate outcomes that are most meaningful to the specific circumstances at hand, which means using our market knowledge and the basic business judgment with which we have been endowed.
