For this lesson you need to open the sample file UK House Prices-1 from your Sample Files folder.
This lesson introduces Trend Analysis.
If a value has been increasing for a long time, trend analysis would suggest that it will go on increasing. Some would say that the reverse is true, but Excel remains healthily optimistic that we can predict the future from the past.
Excel provides several different types of trend analysis. We're going to use a Linear Trendline and a Two Period Moving Average, to decide whether it was a good idea to buy a house in 2008.
So let's begin by removing the Minimum Base Rate data series and corresponding Axis from the chart. You learned how to do this in Lesson 5-14.
To do this I'll right click in the center of the chart and then choose Select Data from the Shortcut Menu. And on the left hand side of the dialog you can see both data series. I'll select the Min Base Rate data series and then click the Remove button.
I'll then click OK. And you can see that we now only have one data series, and that the Minimum Base Rate Data Series, along with its Vertical Axis, have now been removed from the chart.
Now we're going to add a Linear Trendline element to forecast where property prices will be in the Year 2014. To do that you first need to activate the chart if it's not already activated, by clicking just inside the border.
You know that the easiest way to add an element is by clicking the Chart Elements button at the top right of the chart. And you can see the Trendline element towards the bottom of the list.
When I hover the mouse over the Trendline element, you can see there's a fly-out menu. So I'll click the fly-out menu arrow and then I'll click More Options to give me complete control over the Trendline I'm going to add.
The Format Trendline Task Pane now appears. I want a Linear Trendline so I'll leave the selection at Linear. And you can see that the Linear Trendline is already being shown on the chart.
Let's now scroll down to some of the options and you can see that I can add a Forecast option. And I wanted to forecast where house prices would be in 2014.
Now because the Axis is moving in single years, and because 2014 is six years after 2008, I'm going to enter 6 in the Forward Forecast box.
I'll now press the Enter key, and you can see on the chart that we can already see the Forecast Trendline.
And I'll now close the Format Trendline Task Pane.
You can see that the chart's unable to display the years at the moment, as it's not wide enough. So I'll also re-size the chart so that the years show clearly.
And you can see that Excel believes that in 2014 the house that you bought for 186,958 in 2008 would be worth well over a quarter of a million.
In this particular example, Excel didn't forecast very well. But let's look at a different type of trendline that might give us a better forecast.
First of all, I'll click on the Trendline to select it, and then I'll press the Delete key on the keyboard to remove the Linear Trendline.
Now I'm going to add a different type of indicator: A Two Period Moving Average. Moving averages are one of the most loved instruments of speculators who predict future values of shares, currencies and commodities, based entirely upon charts.
The theory is that when the price crosses beneath the moving average, it's time to sell.
Once again, we need to add a Trendline element. So I'll click the Chart Elements button and then hover the mouse cursor over the Trendline element. And then I'll click the fly-out menu button.
And you can see that one of the quick options here is a Two Period Moving Average. So I'll click on Two Period Moving Average and a Two Period Moving Average Trendline is added to the chart.
This time the analysis shows that, for the first time in 10 years, 2008 was the year to sell up, because the price has moved below the moving average for the first time.
All that remains is to save your work. And I'll save with the new name: UK House Prices-2. And as usual I'll save to the folder above my Sample Files Folder.
I click the Save button and you've now completed Lesson 5-25: Add a Trendline.