Join Wayne Winston for an in-depth discussion in this video Fitting a linear trend curve, part of Excel Data Analysis: Forecasting.
- You may have heard your boss say "Our sales are trending up by 10,000 dollars a year." or maybe unfortunately, they're trending down by 100,000 dollars a year. In this video we will learn how to determine the upward or downward trend in a time series. If you want to follow along with me, you would go to the chapter three video one folder and open the file trendline start. And what do you see in this file? Well you see the percentage of Caucasians in the United States for the years 1970, 1980, 1990, 2000, 2010.
And if you look at column H you'll see the percentage of Caucasians is dropping. So you might want to estimate how fast is that percentage dropping. And so this requires the creation of a trendline to estimate the trend. A trendline is simply the line that best fits your data. So let's first plot the data. Select our data, we go insert, and then we go to scatter, we'll pick the first choice. So we now have the percentage of Caucasians in the United States each year 1970 through 2010.
Now, when you plot the data, it may not always look like a straight line will fit the data. Here it certainly looks like you could find a straight line that would fit the data well. You'll see in chapter four, that's not the case. And often when you plot data, a straight line will not be the curve that best shows the relationship between your two variables that you're plotting. Well how can we find the line that best fits the data? And what does that even mean? Well, basically what Excel does is take the vertical distance from any line to the points and square them.
That's your sum of squared error. And it picks the line that minimizes the sum of squared errors. That concept was invented by the great German mathematician Carl Friedrich Gauss, who lived in the 19th century. So to get a treadline, recall what we did with moving averages in chapter one. To get a trendline, you right click on the point, you go add trendline, and then we want linear cause we think a straight line is relevant. We want to select display equation, and display r squared. Don't worry about the intersect button there.
And then basically we'll find the line that best fits the points and we'll get the equation of the line. So if we look here and we x out of the trendline box. We probably want to make this a little bigger. So if I select this and I do control one to bring up format cells, I can make this let's say, a 15 point-- We'll say 16 point font. And the best fitting line here is y. Which is the percentage of Caucasians in the United States, would be minus point four times x, which is the year, plus 875 point six.
Now what the r squared means, we'll get to that a little bit later. But in the next video, we'll interpret this trendline and try to explain what it means.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Plotting and displaying time-series data
- Creating a moving average chart
- Accounting for errors and bias
- Using and interpreting trendlines
- Modeling exponential growth
- Calculating compound annual growth rate (CAGR)
- Analyzing the impact of seasonality
- Introducing the ratio-to-moving-average method
- Forecasting with multiple regression