Tag Archives: regression analysis

Excel House Prices Analysis using Zoopla Data

Excel House Prices Analysis: What’s My House Worth?

About this time last year we were bidding on our first house. We didn’t want to overbid and thanks to Zoopla I was able do some quick regression analysis to get the insight I needed to bid with confidence. The chart below shows an indexed trend for the general area we were looking at. It also shows observed house sale prices on the streets we liked for the last 12 years (yes, Zoopla provide that data for free!)

zoopla analysis
This chart can be customized to any street in the UK using Zoopla data

The chart above told me four things:

  1. The general trend for the area has been strong
  2. Sale prices for houses in these particular streets (Callum Park and Fraser Drive) have generally been better than the area
  3. In May 2014 (the current date at the time of this analysis) about £220k is ‘on-trend’ for a house in Callum Park
  4. Provided the 12-year trend holds for the next 5 years, I can expect to get a decent return if selling at that time

My 4-step Excel house prices process

Here is how I did the analysis and put this chart together–again, thanks to Zoopla which is a fantastic resource. Download the source Excel file here as a reference.

1. Get historical average price data for the area

This kind of trend for 5 years is readily obtainable from Zoopla here. Rightmove and the BBC also provide a degree of trend information with a longer history. You want to target the town, suburb or city-segment (e.g. EH1, SE8, etc.) but make sure it is not too big an area (e.g. ‘Newcastle’ would be too big).

2. Get a trend line you are OK with

In my case I looked at the AB32 average price trend on Zoopla/Rightmove and saw it was a fairly consistent 11% year-on-year increase over the last 12 years to 2014. I read some BBC and local news articles on property and economic trends in the area to make assumptions about whether the past trend would continue, and I assumed that it would.

You can use a linear trend line or other type of line to predict overall value growth that goes out as far as the time you plan to sell the property (for me, it was 5yrs–2019). You do this to gauge how much you expect the house will be worth at that time, based on past data and the general sense of how that trend will continue. For me, I saw strong growth in the oil price as well as substantial construction work with multi-national companies building offices near the area. This helped me assume the trend would hold up for the next 5yrs. For more on building a trend see this article.

3. Plot actual observed sales for your street

This next step is easy. Use Zoopla to find all the sales on your street for the past 10yrs or so (example here) then plot them on the chart (in the case of multiple sales in a year you could take the average price for that year). Note: if you cut and paste from Zoopla’s website you will have to tidy up the output a bit to get the values you need into Excel.

Once you have your trend line and observed sales on the street, base your trend line to the earliest observed sale in the street of interest so you can see the area trend on your street. You may have to write a short formula to convert the trend to a series of year-on-year percent changes to do this. In my case the trend starts with the Callum Park sale for £63k in 2002 (see screen shot below).

zoople analysis - rebasing your trend
Get your area trend then base it to the earliest observed sale on your street

After re-basing your trend the result looks something like my chart below (see Excel download here for more detail).

zoopla analysis

4. Bid with confidence

Now that you have done your own market research on the street where the desired house is located you can better gauge:

  1. Whether the price you will bid is over-inflated (due to a property market price bubble)
  2. Whether you can expect to hold the value you pay if re-selling the house in 5-10yrs

One other factor worth being aware of when doing this analysis is that the relative condition of the house should play a part. If we assume houses generally sell in decent condition, a house requiring considerable renovation/DIY work should really be priced below the trend line.

Did you have a go–feel free to comment below about how it went!

Excel Seasonal Forecasting Technique

Dealing with Awkward Actuals

About a year ago I was using a creative excel forecasting technique to send a forecast to the finance team based on a couple of years’ worth of data. The historical sales data were difficult to base a sales forecast on for various reasons. I used a formula (taken from CIMA) to produce the following forecast:

dynamic sales forecast using ts=t*sv*r

Factors that made my historical data hard to extrapolate into a forecast trend were:

  • Rapidly increasing volumes generally
  • Seasonal swings in sales (e.g. winter peaks)
  • Evidence of promotional events causing random spikes in the past trend

The formula for getting around this is actually pretty basic:

TS=T*SV*R

TS=the time series sales forecast you’re aiming for
T=a linear trend (or curvilinear if you wish)
SV=seasonal variance
R=random component

4 Steps for a Better Excel Forecasting Technique

Get my 4-step Excel solution download here.

1. Take random known events out of your actuals to get an adjusted actuals series: A

When deriving a trend line as we will do in step 2 you don’t want to include random events such as an operational decision to layoff half the sales force, or ‘the stock market crash’, etc. So you need to apply historic knowledge of known factors to cut out these impacts.

Remove random component from historical sales data
Remove random component from historical data to get adjusted actuals

2. Get a trend line: T

You can use an Excel chart, the LINEST() function or another method to derive an equation that relates time to sales (e.g. Sales=4*year+2000). To do this use your adjusted trend from step one as your baseline data. That is an important part of this Excel forecasting technique.

3. Find the seasonal variance multiplier: SV

If your trend has any seasonality in it–i.e. movies in autumn, retail energy in winter, etc. then you need to account for that by using a multiplier for each month in your forecast. To do this find the average variance to trend for historical periods–in my case I did it by month using a well placed pivot table, getting the average of A/T (Adjusted Actuals/Trend). Ensure your A/T’s sum up to the number of periods in all ‘seasons’, so in my case 12 (see solution download).

4. Create the Time Series, adding the random component (R) if necessary

Add any known random component factors (e.g. upcoming promotions, economic factors, etc.) then combine it all together by applying the formula TS=T*SV*R.

Resulting sales forecast adjusted for seasonality
Resulting excel forecast adjusted for seasonality
Sales Forecast Chart (incl. seasonality and random component)
Excel Forecast Chart (incl. seasonality and random component) plotted vs. original unadjusted actuals.

This technique applies to any time series forecast, provided there is some correlation between actuals and your trend line. As noted by  Entrepreneur you don’t need to use advanced mathematical techniques for useful forecasting, so hopefully this should be sufficient for most commercial applications. To see my 4-step application of the technique download my workbook here.

Ideas to improve this even more? Did you try this Excel forecasting technique out? Please share your comments below.