Tag Archives: trend line

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.

Excel Chart Forecasting Technique

Excel Chart Forecasting Technique

Watch this video to see how to use Excel charts to produce quick and effective forecast trends. It uses EIA oil price data which is available here.

Download the Excel file from the video HERE.

For a more dynamic forecast that accounts for random events and seasonality see THIS article.

Excel Chart Forecasting: The Oil Price

excel trend line forecast
Excel trendlines are a quick way of visualising and forecasting regression trends

Recently the oil industry has been going through some turmoil (see the above chart and this bbc article). This is not really new for E&P companies. But it is also not good for them because they are definitely headlong in the down-phase of a price recession. We don’t know exactly when the price will start to rebound again, but it assuredly will (I would guess around May 2015). When it does we don’t know how long it will surge upward for–but it seems like a safe bet to assume that the price will rise strongly over the long-term since global demand for oil is not diminishing and global supply is not expanding. It will probably rise enough to make even expensive (in current terms) reservoir oil ‘liftable’.

Looking at the trend since 1987, I am inclined think it will follow the blue dotted line in the chart above–not exactly of course, but in a smoothed sense. This line is a 3rd order polynomial trend forecast that shows diminishing increases as we move into the 2020’s. This oil price forecast would account for continuing price increases as we drain down the easy-to-lift stuff and global demand continues to increase; but it is also offset by a gradual increase in energy substitutes as they become more mainstream in 8+ years time.

Out of interest I have also added an exponential trend line to my chart (in the case where the rate at which demand outstrips supply turns out to be an exponential relationship over the period shown) and a linear trend (the case where the relationship between supply and demand remains constant over time).

Feel free to leave any comments you might have below.