Tag Archives: charts

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.

Excel Industry Analysis

Excel Industry Analysis

I considered that if I could look at the UK share price trends of all UK companies over the past 10 years or so, I could then group these into industry segments and get an idea of which segments are currently moving in positive ways–and which ones are looking like they are dying off. This kind of data was not readily available in a nice .xls file for the taking; you can buy some form of historical share price closing data for about £200. But Yahoo and Google provide the data for free on a ‘one-stock-at-a-time’ basis… which is not much use here. What I ended up doing was writing a kind of scraper using VBA (if anyone can help me write in C# get in touch!) and pulling out monthly closing price data on all current FTSE100 companies for the past five years or so. I then grouped by industry and these are the results (if you want to learn how I did this, check out THIS ARTICLE):

FTSE100 Industries: Combined Adjusted Closing Share Price (GBP)

chart_mtx1 chart_mtx2*Note: Total FTSE100 chart shows the total trend and 2yr change for all companies currently in the FTSE100 (as at Dec 2014) These charts show the adjusted closing price trend and I have also included a 2yr change percentage to hopefully provide a better means of benchmarking since most are evidently in positive growth (as is the Total FTSE100 chart). Some quick observations:

  • Mining and Oil & Gas both show a 2yr negative change suggesting some kind of slump and movement of capital away from these industries
  • Transport air, and Retail (Electronics, Hospitality, Clothing) have the biggest 2yr change which could tell me these are industries on the up and that may be growth areas for the near future (next 5yrs)
  • Supermarket, Mining, Product Testing, and Oil & Gas all show negative 2yr growth suggesting less demand on these industries (see Caveats/Weaknesses below)
  • The overall trend has been steadily positive (since 2009)

Caveats/Weaknesses:

  • This is FTSE100 data–so only includes the giants of industry. Therefore, although supermarkets looks bad this is probably because Aldi and Lidl are sinking Tesco so it would be better to get data for all listed stocks (which I intend to attempt in the future, by the way)
  • Since there are only 100 companies some of these industries (e.g. Generator hire) comprise one company (Aggreko, in this case) which is misleading. Perhaps I could include a small area under each chart listing the companies under the category if I wanted. But, if I can repeat for all listed companies that will wipe this concern out.
  • It may be more beneficial to consider industry segmentation by demand category–so for example if an engineering company services the oil industry, it would be classed as oil instead of engineering. This would yield a better gauge on where the demand is trending.

Thoughts that Sparked this…

The company I work for does a lot of business with Talisman Energy, and the remains of Talisman (it sold half it’s UK assets to Sinopec in 2012) were bought by Repsol on Monday for $8.3B. This prompted a quick search which lead me to a BBC article titled ‘Falling oil prices threaten to transform the industry‘). The article explores how with the steady slide in crude prices this year has led many oil companies to look for ways to save money–something they have not had to try very hard at for the past 20 years or more. Some are considering mergers or more joint-venture activities. Others are going out of business and the landscape of the upstream industry may be set to shift markedly in the coming months. This does not seem untrue in Aberdeen. Perhaps it is a chronic feature of mineral-based E&P industries due to their boom-bust nature (leaving ghost towns in their wake, etc.) but since moving up to Aberdeen I keep hearing stories about the oil running out, supermajors downsizing North Sea operations, and how it is getting prohibitively too expensive to drill and produce hydrocarbons in the North Sea. Indeed, Chevron axed about 1/3 of their 800 Aberdeen staff a couple of months ago, I hear that redundancy rounds (albeit less dramatic) are also going around Shell and BP. I wonder if the North East of Scotland is going to also become a sort of extended ghost town itself in my generation?

Post-Oil Industries

That was one prominent scare-byte parried by the Scottish referendum (Sep 2014) ‘No’ campaigners: ‘the oil is running out’. I gave no credence to that argument since Scotland has a strong history of ingenuity and, although it has helped, we do not require oil to thrive. But, you have to wonder what industries would in the wake of a depleted oil supply? On a more personal note, having moved (earlier in 2014) from a utilities company that was very much in the ‘growth’ phase of its parent industry life-cycle, and having felt a sense of the urgency that entailed (as well as internal restructuring and promotion activities), I have been ruminating on how I might better understand which industries will be thriving in the future. I am thinking it would be better to get on that bandwagon before the oil runs out than after–even if we are talking about a 20 year horizon.