If you need to do some forecasting, you could struggle with LINEST, or you could simply add a trendline to a chart. Episode 822 will take a look at charting trendlines, including linear, exponential, and polynomial trendlines.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
I want to send a shoutout to David. David’s in Singapore.
A couple of weeks ago, David was having dinner and he happened to be sitting with my brother.
So, when he was introduced and heard that it was Bob Jelen, he said, “Wait a second, you're not related to that MrExcel guy, are you?” which I thought was very cool.
So, David, thanks for freaking my brother out halfway around the world.
Today, I want to talk about trendlines and how to do forecasting with trendlines.
So, I have some sales here for the first 12 months and I want to see what they're going to be for the next 12 months.
Well, we could try and use LINE ST, you know, which is relatively hard to use because it's an array formula, but even easier than that, just right-click on the line, right click on the line, and choose ADD TRENDLINE.
Now, if your data seems to be growing in a linear fashion, so it's adding 20 a month or 30 a month or something like that, we can choose LINEAR.
I always like to see what the equation is, so DISPLAY EQUATION ON THE CHART, and click OK, and it draws a trendline, and I always think that the trendlind is a bit too solid, especially since it's kind of a forecast.
So, I always will format that trendline, and kind of kick it back to thinner and a dotted style, maybe even a different color, click OK, but we'll see here that, basically, they think that we are increasing by about a 100 a month, so, on average, 900 + a 100 each month, 102 each month, and so that's a simple way to do a LINEAR trendline, but, sometimes, your sales aren't following a linear pattern.
Here, I have sales that are actually kind of multiplying each month.
Each month, we're selling, you know, the previous month times some factor.
We're having this this tremendous growth, and if we would add a trendline here, ADD TRENDLINE, and choose LINEAR, it's going to be a horrible trendline.
It, you know, it’s not close anywhere, but we want to change that trendline and choose, instead of LINEAR, maybe EXPONENTIAL.
So, exponential means that, every month, we're selling a percentage over the previous month and so the numbers keep growing.
Now, again here, I want to format that trendline to make it a little bit less prominent, and we can see, in this case, the trendline projects continuous growth.
Now, there's other types of trendlines in there.
For example, here's a polynomial trendline.
That's where if somehow your data would be a constant + some number times the month + some number times the month squared, certainly scientific data would fall into that category.
Probably not something going to see when you're doing sales forecasting, but there's certainly those options there.
Also, LOGARITHMIC and POWERs and MOVING AVERAGEs.
Sometimes, depending if you have seasonal data, the MOVING AVERAGE might be the best way to go.
So, we can create different trendlines there.
Now, in all of these, the one thing that I did upfront was I made sure to include extra series in my chart.
If you hadn't done that, if you would have just created the chart based on the original numbers, then we would have to ask Excel to extend the trendline into the future.
So, when we format the trendline, on the OPTIONS tab, we can say that we want to go forward maybe 12 periods, and that will force Excel to extend the chart.
I always think about it in advance, I make sure to leave it plenty of extra space.
That way, I don't have to go in and change that setting, but, certainly, you can do it.
So, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I want to send a shoutout to David. David’s in Singapore.
A couple of weeks ago, David was having dinner and he happened to be sitting with my brother.
So, when he was introduced and heard that it was Bob Jelen, he said, “Wait a second, you're not related to that MrExcel guy, are you?” which I thought was very cool.
So, David, thanks for freaking my brother out halfway around the world.
Today, I want to talk about trendlines and how to do forecasting with trendlines.
So, I have some sales here for the first 12 months and I want to see what they're going to be for the next 12 months.
Well, we could try and use LINE ST, you know, which is relatively hard to use because it's an array formula, but even easier than that, just right-click on the line, right click on the line, and choose ADD TRENDLINE.
Now, if your data seems to be growing in a linear fashion, so it's adding 20 a month or 30 a month or something like that, we can choose LINEAR.
I always like to see what the equation is, so DISPLAY EQUATION ON THE CHART, and click OK, and it draws a trendline, and I always think that the trendlind is a bit too solid, especially since it's kind of a forecast.
So, I always will format that trendline, and kind of kick it back to thinner and a dotted style, maybe even a different color, click OK, but we'll see here that, basically, they think that we are increasing by about a 100 a month, so, on average, 900 + a 100 each month, 102 each month, and so that's a simple way to do a LINEAR trendline, but, sometimes, your sales aren't following a linear pattern.
Here, I have sales that are actually kind of multiplying each month.
Each month, we're selling, you know, the previous month times some factor.
We're having this this tremendous growth, and if we would add a trendline here, ADD TRENDLINE, and choose LINEAR, it's going to be a horrible trendline.
It, you know, it’s not close anywhere, but we want to change that trendline and choose, instead of LINEAR, maybe EXPONENTIAL.
So, exponential means that, every month, we're selling a percentage over the previous month and so the numbers keep growing.
Now, again here, I want to format that trendline to make it a little bit less prominent, and we can see, in this case, the trendline projects continuous growth.
Now, there's other types of trendlines in there.
For example, here's a polynomial trendline.
That's where if somehow your data would be a constant + some number times the month + some number times the month squared, certainly scientific data would fall into that category.
Probably not something going to see when you're doing sales forecasting, but there's certainly those options there.
Also, LOGARITHMIC and POWERs and MOVING AVERAGEs.
Sometimes, depending if you have seasonal data, the MOVING AVERAGE might be the best way to go.
So, we can create different trendlines there.
Now, in all of these, the one thing that I did upfront was I made sure to include extra series in my chart.
If you hadn't done that, if you would have just created the chart based on the original numbers, then we would have to ask Excel to extend the trendline into the future.
So, when we format the trendline, on the OPTIONS tab, we can say that we want to go forward maybe 12 periods, and that will force Excel to extend the chart.
I always think about it in advance, I make sure to leave it plenty of extra space.
That way, I don't have to go in and change that setting, but, certainly, you can do it.
So, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
