Visualizing Market Activity Using Graphs & Trendlines

Dave Towne
Latest posts by Dave Towne (see all)

Visualizing Market Activity Using Graphs & Trendlines. 

Spreadsheets and graphs are powerful tools for appraisers. With proper data, and incorporated into reports, they can enhance your professionalism and decision-making ability. 

Appraisers, this article will highlight the process I use to bring clarity to my research and analysis of ‘comparable’ properties. This completed process becomes an exhibit in all my appraisal reports because it helps the Intended User(s) visually see what the market has been doing over a known time period. The exhibit graph also indicates a daily rate of change for the sales over the time period, which can then be applied as a ‘time’ (market) adjustment to each comp in the report – if warranted.

Before launching into the process, I want to acknowledge appraisers David Braun, Patrick Egger, George Dell, Joe Lynch and Steven R. Smith for teaching the importance of, and processes for, analyzing and using data and graphs in appraisal reports to me and other appraisers. Additional input has been derived from other educational sources, primarily for the use of Excel.

There are some services available to appraisers which provide similar graphs in more of an automated way. MLS Associations, via their property data bases, and secondary appraiser resources, often have graphs available. But I find most of these to be neutered in the way I want to present the sales data information. There is limited customization available to the appraiser when using these services, but they are ‘better than nothing’ as an additional exhibit in reports. It will help you justify your ultimate value decision. The chart of sales and graph I use are developed in MS Excel (because my MLS exports data to that), but virtually any spreadsheet software can be utilized to replicate the information I present.

The process begins with examining the subject property and surrounding area to understand the ‘appraisal problem’ consistent with USPAP Standard 1.

Once I examine the subject property, I determine the primary characteristics. Those start with location, then GLA size, design, site size, and other amenities. Note that I have not mentioned “Price” in this. I normally segregate comp search by GLA size, in a range of 20% smaller to larger than the subject’s GLA. If need be, I narrow that range. The comp selection distance is determined by the subject’s geographic location, urban areas closer, expanded in distance in suburban/rural areas.

Subject property has 3 bedrooms, 3.5 baths, built in 2005, 2,612 sq.ft. in gross living area (GLA), and lot size is 0.140 acre. It was originally listed for $625,000 and list price was lowered to $599,997.

This property is in a dense urban development. But what is somewhat different is the homes are ‘semi-custom.’ This means the builder started with a variable stock house plan on a particular lot, to randomize the look of the neighborhood, and would not begin building until a prospective purchaser entered the process. At that point, the purchaser could select finishes available in the builder catalogue to ‘customize’ the home.

The net result of this builder program is the neighborhood has similar ‘looking’ homes from the exterior, but the insides could be very different in quality finishes, and thus the price points of the purchased homes could be in a wide range. However, in a general way, the home sale price was predicated on GLA size, so that’s an appropriate metric for comp searching.

Now take a look at the graphs below. When I first began this research project, I started with a GLA range 20% smaller to larger than the subject. After graphing those sales, it became obvious that the range was too wide.

So I re-did the search to a 10% range, and the graphs reflect those sizes. This is where graphing is vital. It helps you see the actual sales and how they are positioned in terms of their sales price in the market verses the date of sale.

Once that step is completed, I then add the linear, and later, the polynomial trend lines. In Excel, and other spreadsheet software, this is a user-defined application, so you will have to learn how it’s done in the software you are using.

Now we are getting to the meat of this article. Interpreting the data and making decisions, so that those decisions can be translated into the determination of appropriate market value. It’s the ‘analyst’ becoming prominent that George Dell stresses.

Take a close look at the yellow graph. The diamonds show the sales related to the date of sale. The linear green line shows the OVERALL trend of those sales, with a formula shown in Green as y=58.177. This is an automated process built into Excel, and other spreadsheets, which shows the DAILY rate of change in dollars to the sale prices. This can be used to develop a ‘time’ adjustment to comps in the report.

Normally I’d probably stop right there and just proceed to finish the report. But when this graph is examined closely, you will see that there is a significant gap between the lower and higher priced sales of about $15,000. The linear green line is the demarcation! That tells me that in this neighborhood, the GLA and customization is impacting the sale prices.

Also note that the average and median sale prices are quite a bit below the offered ORIGINAL List Price. That’s a significant data point to consider. It’s a chin scratching moment ‘analysts’ must evaluate and then make a decision to back up their value conclusion.

Once this price differential was revealed ‘visually’ in the graph, I produced a second graph – the blue one – which is limited to those sales above the green linear line in the yellow graph.

At this point, the brains of pure academic data scientists, and some appraisers, are exploding. “How dare you use so few sales. You need at least “X” numbers to be accurate.” To that I say: “Real estate does not always follow predictable courses; real estate sales are impacted highly by buyer emotions. So the data you have available is what it is, where it is, when it is.” You can, and probably should, make a decision with it! Especially if your comp selection is consistent with the subject’s characteristics. Again, this is a key aspect of being an analyst, which is vital for every appraiser.

Back to the blue graph. It reveals that the overall value trend is STABLE (the dark black line), with a rate of change at only $0.135 cents PER DAY! I would not apply a time adjustment to the comps based on that. Secondly, the dashed (red) polynomial line, which precisely tracks the INDIVIDUAL sales, is also stable when the end points of that trend line are analyzed.

When the average and median sales prices are examined, those fall closer to the sales agent’s list prices, and thus to me, those sales between $590,000 and $625,000 would be considered more appropriate as comps in the report.

Now to a point about POLYNOMIAL lines: you can select the “order number” to apply in the graph. Some people advocate using only “order number 3” in conjunction with the linear line. I don’t. Simply because when the “order number” is raised, its precision relative to the sales is improved. When you learn to use the Poly trend line, and vary the “order number”, you will notice how the ‘magnetic forces’ of the line are influenced by the positions of the sales on the graph. That’s why the blue graph looks different from the yellow graph, even though the “order number” in each is the same.

Does this process I use take more time to do? Yes. But is the exhibit I produce much better than ‘nothing’ or other ‘automated’ graphs with limited info? I would say Yes. I now have credible evidence I can use to justify and back up my valuation decision.

Regardless of what you choose to do, adding one or more graph exhibit pages to your report will enable you to be recognized as a more diligent appraiser. Clients appreciate that aspect of professionalism.

Dave Towne

Dave Towne

AGA, MNAA, Accredited Green Appraiser - Licensed in WA State since 2003. Dave Towne on e-AppraisersDirectory.com

You may also like...

4 Responses

  1. Avatar Timothy Thompson says:

    My personal opinion: First, your graphs should attempt to express the rate of change in terms of a percentage rate. Secondly, the first graph cited in your article is self-refuting, and actually demonstrates nothing. And, finally, no offense intended, and I appreciate your article very much, but I think that appraisers should probably study Statistics, just a little, before attempting to graph and analyze data.

    Linear models will always produce a result, even if the actual trend is insignificant — as perfectly illustrated in the first graph! Your graph makes a better case that adjustments for market conditions are not warranted. You will need a much bigger data set to support a significant trend — and so be it!

    It’s important to understand statistical significance. The data set cited in your graph establishes absolutely nothing about market conditions. You make a claim to the contrary at your own hazard.

    Another way to say it: The R-squared (or, Coefficient of Determination) in this graph is extremely low — and I can say that with certainty, at first glance. Your spreadsheet program will calculate the R-squared for you, automatically, if you’d like, and display it with the equation line. If you are WAY BELOW .5, you should think twice about what conclusions you can draw from the graph.

    I’m all for graphing data! The future of appraisal is data analysis, and the future is now!

    4
    • Avatar Bill Johnson says:

      I appreciate the effort and the knowledge, but with most appraisers partnering with AMC’s where its currently about the two’s (two day turn time / fees staring with a two), most lender clients could careless. Outsource to the Philippines, rubberstamp 4 to 9 appraisals a day, and repeat.

      Seek the truth.

      6
  2. Avatar Shawn Prince says:

    Dave i respect your knowledge, history and years. Graphs and stats based on bad reporting i would argue despite making a report look pretty if stats are bad well it’s still bad. And stats have bad reported by worst contributers in recent years. Crap in means crap out. UAD been taking crap since it introduced and amc reva 80% appraisal distribution. Maby just maybe focus on the internet article this weekend on lead news again with the appraiser whatever based on race. On saturday morning i read on lead interent news that appraiser devalue whatever based on race. 400 comments. meanwhile the article was proven false and written for agenda 2 months prior. But its saturday was lead news on internet as someone paid for it to be so. Graphs? when everyone lying? Focus on the lying.

    2
  3. Avatar don says:

    Thank you.

    I have found the researching all of the closed, contingent, active and maybe the expired listings in geographical mapped area, “As the neighborhood” explains most questions to the inexpert reviewers or clients. Also, my graphs illustrate SORT’s rather than straight or wiggly lines which invites special education.

    My graphs also illustrates the activity and I illustrate that by counting the closings in each if the six months researched, that may illustrate seasonality, deflation or inflation, are March sales higher than November and what about next March. What’s your Date of Value?

    My method takes a few more pages, how much is your client worth to you!

    1

Leave a Reply

We welcome critical posts & opposing points of view. We value robust & civil discourse. You may openly disagree, but state your case in an atmosphere of mutual respect, in which everyone has a right to a particular view about the topic of conversation. Please keep remarks about the topic at hand, & PLEASE avoid personal attacks. If the poster gets you upset, it is the Internet, you can walk away from it.

Personal attacks harm the collegial atmosphere we encourage on AppraisersBlogs.

Your email address will not be published. Required fields are marked *

xml sitemap

Visualizing Market Activity Using Graphs & Trendlines

by Dave Towne time to read: 5 min
blank
blank
4