Adjustments to Comparable Sales Using Simple Linear Regression

In a previous post I promised to provide a more in-depth explanation of using simple linear regression to adjust comparable sales. Today I will fulfill that promise. To do so, I will use an example of a size adjustment from a recent commercial appraisal I performed on an office building in Louisville, Kentucky.

Appraisers have known for years that the size of a building impacts the price per square foot. Generally, as the size of a building increases, the price-per-square-foot decreases. This market response reflects the economies of scale achieved with constructing larger buildings. The impact is significant for buildings containing less than 10,000 square feet. We’ve known this for years. But knowing it, and proving it in an appraisal report are two different things. I’m going to walk you through, step-by-step how I did just that using a simple linear regression. Again, if you ever need help, give me a call.

Create a Scatter Graph
The first step is to create a scatter graph. The graph below depicts the per square foot price paid for 342 office buildings in Louisville, Kentucky. The sales were first sorted by size.

We can see an obvious trend in the correlation between the size of a building and the price-per-square-foot (ppsf). But how do we quantify it? Simple. We need to add a trend line. To do that, select a point on the chart and click. Now right-click your mouse button and select “Add Trendline”. Excel should automatically present a menu to the right of your screen presenting you with various options. Here’s what you should see on your screen.

Notice that Excel automatically selects a Linear type trend line. It’s using the least squares method to discover a straight line that minimizes the aggregate distance between all of your data points and the line. However, a straight line does not always provide the maximum predictive power of your data. But we want to find the trend that ‘best fits’ the data under consideration. The measure that enables us to find the type of trendline, among those presented (Exponential, Linear, Logarithmic, etc.) is the R-squared value. You may remember from statistics that R-squared is the percentage of Y value explained by the variable under consideration. In this case, that would be the percentage of the ppsf explained by the difference in square footage. To do this, we need to select to display the linear regression equation on our chart along with the R-squared value.

It’s not as difficult as it sounds. Simply go to the bottom of the Trendline Options menu and check  “Display Equation on Chart” and also check “Display R-squared on Chart”. Excel will normally insert these figures at the most inconvenient part of your graph but you can simply drag and drop it where you can see it clearly. When you’re done, here’s how your chart should look:

Now, let’s select each of the various trendline options, keeping your eye on the R-squared value on the chart. An Exponential Value will return an explanatory value of 5.32%, a Logarithmic will result in an R-squared of 9.76%, a Polynomial – 8.32%, and a Power trendline will return a 10.88% R-squared. Thus, a Power trendline has the greatest explanatory power available among the options presented in our excel spreadsheet.

I like to color my trend line red and widen it to 2 points to make it more visible against the blue points. So my next exhibit will include this change. If you’re with me so far, great, you’re awesome and well on your way to mathematically justifying your adjustments. See the R-squared value on this chart? Again, this R-squared value indicates that 4.68% of Y (the ppsf) is explained by x (the square footage of each building). We want to select a Trendline type that maximizes this R-squared value. We want to find the line that has the highest explanatory value. Once you’ve dragged your equation and R-squared value, you may need to again click on your trendline to select it, then right-click your mouse button and select “Format Trendline” for the options menu to reappear. When you’re done, you should see something like this:

Notice how there is a steep curve in the trendline on the left side of our graph. This indicates that buildings between 1 and 10,000 square feet are more significantly affected by size than buildings ranging between, say, 50,000 and 60,000 square feet when compared on the basis of ppsf. In either case, what we need to do now is to be able to quantify that difference. How much difference, for example, is there between the average price per square foot for a 5,000 square foot vs. an 8,000 square foot building and how can I use this information to adjust a comparable sale? Here’s where the trendline equation comes into play. Again, looking to the top right had corner of our chart we see this equation:

y = 587.92x-0.284

In this case, Y is the predicted value along our trendline for a given size of building. In other words, we can substitute a building size for the x variable in the equation above to predict a specific point along the trendline. As an example, to find a predicted ppsf for a building with a gross building area of 5,000 square feet, the equation would be:

y = 587.92(5,000)-0.284

Now, predicting the ppsf for a 5,000 square foot building is simply a matter of solving the equation. Here’s how that works out for this example:

We have now determined the exact point of our trendline for a 5,000 square foot building. It’s $52.34. Knowing this enables calculating a specific percentage adjustment for the size of a comparable sale relative to the subject property. Again, let’s say that our subject property contains 5,000 square feet. Sales #1, #2, and #3 have gross building areas of 4,300 sf, 6,300 sf, and 8,500 square feet, respectively. Calculating an appropriate adjustment requires calculating a percentage adjustment between the predicted Y value of the subject property and each comparable sale. Given our trendline, the size of the subject property, and the size of our comparable sales, here are the calculations:

Finally, we’re able to adjust our sales based on market supported evidence. Because our methodology is imprecise, it’s probably better to round off the adjustment. Thus, appropriate adjustments might be a 4% negative adjustment to Sale #1, a 7% upward adjustment to Sale #2, and a 16% upward adjustment to sale three.

And that, my friends, concludes my blog post about how to use simple linear regression to adjust your comparable sales. If you have questions or comments regarding linear regression, please shoot me an email or pick up the phone and call.

More questions pertaining to commercial appraisals? Check out more of our blogs here!