Have you noticed a CHANGE in the climate recently? No, I'm not talking about global warming! I'm talking about the new interest, from lending clients, on the analysis aspects of residential appraisals.
That's the way my post Market Area Analysis - What's in YOUR "Workfile"? started out. That came on the heals of Fannie Mae's recently issued guidance to lenders and appraisers on the importance of accurately reporting or disclosing declining markets.
Today we welcome a new author to Appraisal Scoop! Kathleen "Kat" Bryce of Fort Myers, FL. Kat was a Technical Writer in a previous life and took on the challenge of documenting the process of going from raw MLS data to a finished Excel spreadsheet graph with trend line analysis!
I think Kat did a FANTASTIC JOB! If you think so too . . .be SURE to leave a comment! Her source files (data, spreadsheet, and chart) as well as a complete Word document can be found at the end of this post.
It has been said that a picture can say a thousand words. This is even more true when that picture reflects what the value trends in the marketplace is doing. With the markets across the nation in transition, it even more important than ever to be able to relay to our clients just exactly what the market is doing.
One of the best methods of understanding complex ideas such as market trends is to demonstrate them with pictures. Pictures that tell a story.
Using very simple calculations within a spreadsheet programs, these pictures can be easily presented to your clients, in the form of a simple chart. This is a rather simple process, yet provides very dramatic results.
The following steps will walk you through creating a chart from raw market data. This document is not intended to teach basic Excel procedures and assumes the reader knows the basics. For basic Excel training, Microsoft offers excellent training articles. http://office.microsoft.com/en-us/training/default.aspx
The best place to start is to export your data from MLS or other data provider such as RealQuest. The preferred export format is comma delimited or *.csv format for working with Excel. Contact your MLS technical service if you need assistance with exporting your data.
Data for this demonstration is from the MLS system Rapattoni. It allows the creation a custom data export. All examples that follow are for data that was exported from Rapattoni and imported into Microsoft Excel.
One of the most important parts is choosing what data you intend to graph. Based on your assignment problem and the scope of work you have selected, you may want to graph and entire zip code to just a narrow range of the neighborhood. At the very least the date of sale, sale amount and GLA must be exported.
For this demonstration, the community selected is a waterfront city comprised of ¼ acre lots. The average home is a ranch style home built around 2000 consisting of 1,500 Sq.Ft of living space, has a two car garage, three bedrooms and two baths. The search conducted for a one mile radius of off-water homes ranging in age from 1997-2007 and with a gross living area of 1,350 -1650 Sq.Ft. This search was saved in Comma Delimed format (*.csv) by Rapattoni.
When first opened in Microsoft Excel the data appears as follows. Click graphics for a larger view.
At the top of this window make note that the file name ends in .csv, and that the sheet name near the bottom has punctuation market in it. These must first be changed to an Excel format before any charts may be created. Select File > Save As from the menu. Save as File Type “Microsoft Excel Workbook(.xls)”. Give the worksheet an appropriate name, and click Save.
Next you must rename the worksheet name to remove any punctuation marks. To rename the sheet Select the sheet tab, .csv]9209705_RESI_FE4[1] in the demonstration. Double click on it. You may now change the name. In this demonstration the name is named "Raw". Type in the new name and Press Enter.
Preserve the raw data for future reference.
Next create a copy this raw data and copy it to the next sheet. This new sheet will be used for all calculations thereby preserving the initial search on the raw page. To do this, Right mouse click on the tab labeled "raw" at the bottom of this sheet > Select "Move or Copy" from the menu. Select "(move to end)" from the menu and check the "Create a Copy" checkbox. Click OK.
A new sheet is created with a carbon copy of the data and it is named "Raw (2)". Rename this to "Cleaned" by first selecting this tab, then double clicking the name. Change the name to “Cleaned” and press enter.
Now is the time to clean up the data. Resize the cells and remove records that don't fit the model that you intended. You will now have something that looks like follows. Note the Sheet names “Raw” and “Cleaned” below.
Calculate sales based on $/Sq.Ft.
First, insert a column in which to calculate the $/Sq.Ft.. It makes the charting process much simpler if the $/Sq.Ft. column is immediately to the right of the "date of sale" column. To do this, select the letter associated with the field immediately to the right of the field that contains the date of Sale column on the spreadsheet. This is E on my Example. This will highlight that entire column. Type Cntrl + (Control plus the + sign), or, select Insert > Column from the menu.
Move to the first cell in this newly created column. Type the label name for this new column. For the example it is “$/Sq.Ft”, press enter. You should now be in the first empty row below this new name. Type "=" (equal sign) then select the cell that contains your sale price, type "/" for divide and select the cell that contains the GLA. The formula is =F2/J2 in the example. Press enter. This cell should contain your first rows sold price at $/Sq.Ft. If it does your 90% there!
Oops… Does your formula look like a date? To format that column as a number, select Format > Cells from the menu. Then select number from the pop-up window.
To copy this formula to the rest of the data, double click on the lower right hand corner of the box that surrounds this new formula. There is a funny little box in that corner (see yellow arrow below). When double clicked Excel will copy this formula down the column for the rest of the spreadsheet. It will stop at the first blank row it comes to.
Double click on the lower right corner as pointed out by the arrow.
The formula is copied to the last row in the column.
Sort Data
It's nice to have the data sorted by the date sold. As you can see in the example above the dates are not in order. To sort on the date field type Cntrl A to select the entire spreadsheet. Then from the menu select Data > Sort. A menu will open.
First select the "Header row" radio button near the bottom of this menu. Select from the "Sort by" drop list the field name that has the sales date. It is “Selling Date” in the example. Select the corresponding "Ascending" Radio button and click "OK"
Hopefully you now have a worksheet with your data sorted by date of sale and the $/Sq.Ft. is calculated for each row. Now is a good time to save your work again. File > save from the menu.
Now you are ready to chart!
Select the cells that contain the date of sale, "Sale Date" in the example, and "S/Sq.Ft." data for all cells. This may be done by dragging the mouse from the first cell to the bitter end on the second cell. A quicker way is to select the top row cell that contains "Selling Date" (Or whatever your export named it) Then Click Cntrl Shift ↓ (down arrow) all three of these at once. Release all keys. Now hold down the shift key and press the (right arrow). You should now have highlighted the entire date and $/Sq.Ft. columns. If that didn’t work for you use your mouse to select these two columns.
There should be an Icon that looks like a bar chart on your menu.
The chart Wizard will open. Select "XY (Scatter)" from the left window.
Select Next > Next. In the Chart Title box type a name for this chart.
Select the Legend tab and select the "Top" radio Button. Click Next then finish. Tada!!!
Here's your chart. Not very pretty, huh?
Adding a trendline and formatting the chart.
Right mouse click one of the 'dots' (data points) on your chart. Select "Add Trendline" from the menu.
From the Add Trendline menu select Polynomial. Change the order to 3. Press OK.
Your chart should look like as follows. Experiment with different orders on the trendline. A two will straighten the graph and a four may make it more wavy. The Linear works in some cases also.
Formatting the chart.
To make the chart clearer you will need to change some of the formatting. To make the date cleaner (X-Axis), right mouse click over one of the dates and select “Format Axis”. A menu will load to allow you to change the number formatting and scale. Select the number format Tab, from the right window pane select the format of “Mar-01”. Select the scale tab. Change the Major Unit to a value that will allow some readability this is the number of days, the example is set to “120”, set the Minor Unit to “30”.
Clicking near the top in open spaces on your chart will give you numerous options for further formatting your chart. You can add gridlines and change the color by right mouse clicking anywhere in the body of the graph.
Below the (Y-Axis) scale was changed to expand the curve, the colors of the chart and trendline were changed and the trendline was change to order-4.
As you can see the formatting options are endless.
The final step is to insert these charts in your reports. Just right mouse click your report and select copy, to copy it to your clipboard. There is nothing like a chart to show indisputable evidence as to what the current market conditions are. Market Conditions demonstrated by a chart that is easy to understand to virtually any reader of your report.
Source Documents and Data:
- Download Appraisal Graph Demonstration Spreadsheet Data (36.0K)
- Download How To Graph In Excel - Word (453.5K)
- Download Fannie Mae Ann. 07-11-Declining (33.8K).
Author: Kathleen "Kat" Bryce, Fort Myers, FL - State Certified Real Estate Appraiser RD 4424 - http://www.leecountyappraisals.com - Email: appraiserkat@earthlink.net - Phone: 239-940-2225 Fax: 239-275-4130
Recent Comments