Wednesday, July 17, 2019

How to Analyze and Present Large and Complex Home Sales Data – in 30 Minutes (1 of 2)

-- Intended for New Analysts & Researchers --

If you have very limited time - say 30 minutes - to summarize and present a fairly large and complex home sales dataset, comprising 18 months of data, with 30K rows and 10 variables, here is one approach you might like to consider:

1. Given the limited time, instead of trying to crunch the data in a spreadsheet, invoke your favorite statistical software like SAS. What SAS will do in four short statements (Proc Means, Var, Class and Output) and in matter of minutes, you will need much longer to accomplish the same in spreadsheets. When you are starting out, take full advantage of these types of highly visible - often rewarding - challenges to narrow your competition down.

2. Have a realistic game plan. Instead of shooting for an array of parameters, start with the most significant one, i.e., Monthly Median Sale Price (and the normalized sale price). Since median is not prone to outliers, you do not have to edit the dataset for outliers, saving significant amount of time.  

3. Now that you have the monthly median prices, you are ready to create graphs for the presentation. While you may create one graph depicting both prices (Y1 and Y2) against months (X axis), keep them separated for ease of presentation. 

4. If you are more comfortable graphing in Excel (in fairness to the remaining time), transfer the output from SAS to Excel. Make sure your graphs are properly annotated and dressed up with axis titles, legends, gridlines, etc. Remember, just doing things right is not good enough, learn to do things elegantly as well. 

5. Since you have summarized and rolled up so much of data behind one or two graphs, make sure they not only tell the overall story, but also convey enough business intelligence to make you look like a hero in front of your EVP/SVP. In the presence of clients, it enhances their image as well. So, add trendlines alongside the data trend. Select the primary trendline by eyeballing the data trend (linear, logarithmic, polynomial, etc.). Also, add a moving average trendline to iron out any monthly aberrations. When the series is extended, use 3-month moving averages.     

6. Keep your reporting verbiage clear and concise. Explain the makeup of the dataset; methodology including the use of monthly medians; how the normalized prices add value and help validate the primary; trendlines and their statistical significance; other statistical measures like r-squared, slopes, etc. you might display on the graphs (avoid printing equations on the graphs). 

7. Add business intelligence to your talking points. First off, stick to the market you are presenting but show off your knowledge of that market by highlighting: possible headwinds and tailwinds; how that market would react to an inverted yield curve; is there a structural shift in demand for homes (are more millennial showing interest in that market); what is the NAR's prediction of the summer inventory there; is the inventory of affordable homes on the rise there; any expected change to the FHA to help first-time homebuyers in general, etc. etc. 

8. Try to control the conversation by sticking to what you have, rather than what you don't have. For example, out of the 10 variables, you managed to use only 3 (Sale Price, Sale Date and Bldg SF), so do not start a conversation about the other important variables - Lot size, Age, Bldg Characteristics and Location - you had to leave out ('If I had 30 more minutes' would be a wrong hypothesis to test). If that question comes up, answer it intelligently and truthfully emphasizing, of course, the utility of the 3 you happened to use.

9. Let's assume that you managed to complete the first cycle (as indicated above) in 20 minutes. In that case, go back to SAS and crunch the sales analysis by the sub-markets (Remember: Location! Location! Location!). This is how you walk down on the analysis curve. Have these printouts handy, but do not try to alter the initial presentation.

Ace the next presentation. Be a hero. Prove to your bosses you are a future CEO.

Good Luck!
Sid Som, MBA, MIM
President, Homequant, Inc.

No comments:

Post a Comment