Graphing Using Spreadsheet – Including Pendings
Pendings can reveal market trends, turns and current activity…
How many of you use visual graphs in your reports to demonstrate sales (and Pending) trend data?
This can be a very important tool to convince your clients that you really have a good understanding of current valuation issues.
I am indebted to David Braun, Patrick Egger, Steve Smith, George Dell, Anthony Young and Joe Lynch (among others) for their presentations about this key feature we can utilize. Frankly, using spreadsheets and their built-in graphing functions is something every appraiser should learn.
I bring this up because an appraiser peer was having a struggle with a property purchase appraisal, where the sale price was bid up excessively ($45,000) with an escalation clause in the contract, and contacted me for my opinion. We both know how to use graphs from spreadsheet data. The concern was “is the contract sale price supportable?” when the list price was about $740,000 and the contract price was about $790,000. In this case, NO!
The key element of this assignment was the current Pendings were indicating a downturn in the market, even though prior sales had been chugging along at about a 1% increase per month for the prior 4 years… (determined using a separate chart and graph of sales).
Note that I said ‘Pendings.’ I’ve seen many instances where appraisers have downplayed the importance of analyzing or even using Pendings in reports. Whenever they get a client request to include Pendings in a report, they roll their eyes, and do it reluctantly. What these appraisers are forgetting or dismiss, is the Pendings represent CURRENT market activity, while the comps typically are RETROSPECTIVE. Without including a time adjustment (in increasing or decreasing markets) to the comps, those are ‘old data.’
Circling back to my intro about using graphs, here’s the one I developed for the peer’s report situation. I have more info below this:
If you are not familiar with graphing, items you need to know are these:
The data points shown on the graph bracket the GLA SIZE of the subject by 20% lower and higher, and AGE 10 years lower/higher, with resulting sales indicated. All properties are in an urban subdivision of similar homes. These were NOT chosen based on PRICE.
- The sales are the blue diamonds – over prior 2 years
- The brown diamonds are current Listings (plotted based on list date)
- The green circles are the current Pendings (plotted based on list date)
- The green line is the linear regression based on ‘least squared’ which is a built-in function of the spreadsheet software. It basically represents the ‘average’ of the sales prices along the two year time period. I don’t claim to be an expert on the math in Excel!!!
- There is a formula below the green line (y = 314.24x ). This formula (auto generated by the software) represents the DAILY dollars increase in sale prices, and can be used to develop a time adjustment to the Sales. It can also show the daily decrease in falling markets.
- The most interesting, and probably most critical, trend on this example is the dashed red line. This shows the trend of the individual Sales, Listings and Pendings over the time period, and on this graph is a 6th Order Polynomial for increased accuracy
For this appraisal problem analysis, I included the Active and Pending properties within the chart of Sales, from which this scatter graph was developed. Notice how the Pendings are mostly below the green linear line. More dramatic is the downturn of the dashed red line. We don’t know yet what the eventual sale price of those Pendings will be, but the data from this existing info is showing a downturn in the market for these similar properties.
Trying to understand the actual market activity is one reason why I always include these two trend lines on the graphs I include in my reports. Often, the trend lines are contradictory – like these are. It then becomes a head scratching moment to decide what the ‘real’ market trend is doing. If we only base our decisioning on straight line linear regression, we can miss the actual market movement close to the report effective date. I do not rely only on the MLS statistics, or any of the other property price trend promoters for this; each assignment graph is based on the comparable/competitive properties in the neighborhood or market area using MLS data.
Using a graph like this can also reveal the outlier properties, those that have sold for significant prices higher or lower than the grouping trend sales prices of most Sales demonstrates. Generally speaking, outliers have reasons why they sold at the prices they did, and probably should be discarded before trend lines are applied any final decisions about market trends are determined.
In closing, Pendings can reveal market trends, turns and current activity. Charts of sales over an extended time period, converted into an easily read graph can provide a visual representation of market activity that is much easier to understand than just ‘writing about it.’
If you are not familiar with spreadsheet use and graphing of charted Sales, Active and Pending data, I would encourage you to take classes on this subject, and then put this info in your reports. Your work will become more valuable to those who really want to know what is actually happening. Doing so will sustain you during this time of many in the industry trying to force appraisers off the ‘buffalo jump’ into the canyon below, smashed and broken on the rocks below.