Learn about how to use basic trend analysis to make forecasts using an example.
- [Instructor] Now that we've looked at some different methods of forecasting, let's take a look at the data example. Recall our friend, Ed, the economist. Ed worked for a real estate firm and Ed has been asked by his boss to look at property valuations for a commercial building that the company is thinking of buying. In particular, Ed is interested in looking at valuation trends in the area of this new building and he's especially interested in whether past prices predict future prices.
Ed's gathered some data to help do that analysis. I'm in the 02_01_Begin file from the 02_01 folder under the Exercise Files folder. Now as we can see, Ed has gathered a tremendous amount of data here. He's actually got data starting all the way back in January of 1871 and he's got monthly data on interest rates, in particular, the 10-year interest rate, profit margins for real estate buildings in the area, property valuations for real estate buildings in the area in thousands of dollars, and a moving average for those valuations, which we'll talk about in a moment.
So if you observe, the property valuation on a building similar to what Ed's firm is thinking about buying today, the value was $85,000 in January of 1871. Fast forward to our last available data point, in March 2015, we see that the value has risen to roughly $2.1 million. Now, along the way, Ed has computed a 24-month moving average, so for the first 24 months of data, we can't compute anything because again, we need 24 months to average out.
So starting in January of 1873, Ed averages the last 24 months of valuation data and this produces an average that continues all the way up through the end of the data in March of 2015. One thing we might look at is whether or not that moving average reflects the property valuation. What do you think the relationship is between those two values? Moving average and property valuation.
If you said that the moving average is probably smoother and less far little than property valuation, you're spot-on. So let's take a look at this in a graphical setting. I'm going to highlight columns E and F and then go to the Insert tab and insert a line chart. I'm going to remove our legend and add a trend line along with changing our title.
As we see here, it appears that the red line, which represents the moving average is a little bit smoother than the blue line. The blue line tends to bounce up and down above and below the red line. So very simply, if Ed were interested in trying to figure out where valuations might go in the future, a moving average would be a good place to start. But Ed wants to look a little deeper than that. In particular, Ed has interest in applying one of the time series forecasting methods that we mentioned previously.
He is interested in what's called exponential smoothing, so I'm going to add a new column for exponential smoothing. Now, what exponential smoothing is going to do is use past prices on property values to forecast what future prices might be. We're going to need a special tool to do this in Excel. That tool is called the Analysis ToolPak. To get the Analysis ToolPak, we're going to click File, Options, Add-ins, Manage Excel Add-ins and Go.
And then we're going to check to make sure that the box labeled Analysis ToolPak is checked. If it is not checked, go ahead and check it now, then click OK. Once we've done that, let's move to the Data tab and click Data Analysis. We are looking for the function labeled Exponential Smoothing. I'm going to click OK. And it wants to know what data points is I'm interested in smoothing out. I'm interested in the property valuation lines, so I'm going to highlight all of this.
Now it wants to know what the damping factor is. The damping factor simply tells us how much weight we're going to put on past values. This is a range anywhere from zero to one. Let's use a figure of 0.5. Now, I'm going to put in my output range. I want to start my output in column G, so I'm going to highlight column G from G2 through G1732.
Now I'm going to click OK. As we can see, Excel has spit out some data for us. To help make this a little bit more intuitive and easily readable, let's change the formatting on these numbers, shall we? So I'm going to hit Control + Shift and Down and then hit Control + 1 and this will bring me to my formatting cells menu. I'm going to select Number, and two decimal places, and the 1,000 separator with the comma and I'm going to hit OK.
Now this tells me that based on past data points, I can predict future values. The further we go on time, the further forward we go that is. And the more data we have, the better our estimate should be, in theory. So what we see upfront, is initially in February of 1871, we only have one data point to go off of, 85.02, so we're simply going to forecast February's value based on the only information we have, January's value of 85.02.
Once we start going down though, we'll see that we're going to be able to smooth out that pattern In particular, we're now going to use the value from February plus our previous smooth value. And as we see, this is going to create a long sequence of data. Now let's just look visually at whether or not this exponential smoothing is more effective than a simple moving average for predicting values.
Well, as we can see, when we get to March 2015, we're using the data available in February 2015, so this value in cell G1732, is something we would have access to before we actually knew what the March property values are. And our prediction is that the property value should be 2,089.21, which of course represents roughly at $2.1 million property value because again, like the property values column, our exponential smoothing column is in thousands of dollars.
Here we see that in March 2015, the actual property value is $2.1 million dollars, very similar to our forecast with the exponential smoothing technique.
Professor Michael McDonald demonstrates how to harness the wealth of information available on the Internet to forecast statistics such as industry growth, GDP, and unemployment rates, as well as factors that directly affect your business, like property prices and future interest rate hikes. All you need is Microsoft Excel. Michael uses the built-in formulas, functions, and calculations to perform regression analysis, calculate confidence intervals, and stress test your results. He also covers time series exponential smoothing, fixed effects regression, and difference estimators. You'll walk away from the course able to immediately begin creating forecasts for your own business needs.
- Understanding big data and economic forecasting
- Predicting values with regressions
- Analyzing economic trends and economic cycles
- Using fixed-effects regressions and binary regressions for forecasting
- Assessing the accuracy of an economic forecast
- Using scenario analysis