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!
I just opened the file and it’s working fine on my end. You may need to disable your popup blocker or use a different browser. I sent you a copy of the spreadsheet just in case. Best regards!
donna lafond says
Please send ma a copy of the spreadsheet. Thank you, Donna Lafond
Thank you very much for explaining this.
Russell Roberts says
You are most welcome, Bryan. If you need a copy of the spreadsheet, just shoot me an email.
What a great article, I just learned very valuable information in a very short amount of time. Thanks for making this so simple and easy to understand.
Taft, thank you for your kind words. They are much appreciated!
John goddard says
Could you please send a copy of linear regression spreadsheet. I am a newer appraiser and find it very interesting.
Kim Schaefer says
Thank you for this succinct and insightful article about using linear regression to support market driven size adjustments ! I would love a copy of the spreadsheet if you would be kind enough to share it.
Kim, I don’t think I have the spreadsheet with the scatter graph anymore. I would be happy to send you a spreadsheet that calculates the appropriate adjustment. Please let me know if you want me to send you a copy.
Kim Schaefer says
If you could send me the excel chart with the formulas for calculating the adjustments, that would be greatly appreciated. I can pull the scatter charts. Thanks so much for the article and for sharing your data!
Tom Payne says
Thank you for the article! It is great to have another effective tool to support our adjustments.
You are most welcome, sir! I’m glad you found the article helpful.
Thank you so much for the easy to understand explanations ! If at all possible, may I have a copy of your recent spreadsheet ? This is such a helpful tool!
I would love a copy of the spreadsheet.
Digital Network says
If you have a tiny scale business, then you can prefer
to opt for their cheapest hosts but if you’ve got a bigger
business if you would like to make a profit faster, you can attempt your higher priced options.
They allow for quick updating of a website, user logins, and great customization, but web hosting for dynamic websites is much more complicated than that for
static pages. This will enable that you reap the most effective out
of the company that you simply select.
Matt Lambright says
I found your article very informative. I would love to take a look at the spreadsheet template you have.