polynomial trending

[Orion]

I'm trying to discover the default order no. for polynomial trending in Excel
97 and 2000 and if possible, why they chose order no. 4 as the default in
Excel 2003? Can anyone help?

 

[Bernard Liengme]

Do you mean the value in the window when you use Add Trendline and select
Polynomial.
In my versions of XL2003 and XL2007 this is 2. I seem to recall it was
always 2 in versions from 1997. I have never seen 4 as the 'default'
best wishes

 

[Orion]

Hi Bernard,

Thank you so much for answering this question. I see now that what you are
saying is true. I have both 03 and 07 versions.

A couple of questions:

How do you determine what order no. to select for a polynomial trend line?
The Excel Help text says choose an order that is one greater than the no. of
peaks and valleys. I'm not quite sure what constitutes a peak and a valley.
If the data points are 70, 75, 85, 75, 83, 92, 78, 73. Is that 2 peaks and 2
valleys? So the order no. to select is 5? Or?

What is your opinion about the reliability of polynomial trending for
forecasting?

Thank you so much for your response. Best wishes.

 

[smartin]

Orion,

I hope others will comment on this as I am not a statistician, nor am I
intimately familiar with the inner workings of Excel's so-called
"trendlines". Regardless, here are my thoughts.

If you look at it, a polynomial "trendline" really can't trend anything
in your data. Neither can any other sort of Excel "trendline". This is
readily apparent by applying a poly "trendline" to your data.

At order 6, the polynomial does well to interpolate your existing data,
but if you set the "trendline" to "forecast" some number of units
forward, the result is not credible in the least. It should be obvious
what is going on here: Excel fits a curve to your points, but it doesn't
(and can't) recognize a pattern to predict future (or past) results.

So, to your second question, experiment a little, and you will see that
picking an order that is one more than #peaks + #valleys (including
endpoints) makes a nice fit. However, do not rely on this to do any kind
of trending.

To do sensible trending, you need to know how to inspect your data in
the appropriate contexts, and apply reasonable assumptions for the future.

 

[Orion]

Smartin,

Thank you so much for your insights!

As I am still learning about polynomial trending and its value, they are
helpful.

So, let me see if I understand what you are saying. Let's say, for instance,
we are talking about fluctuations in gas prices at the pump over the last x
number of years, and we see how they've gone up and down and up and down and
now are waaaaayyyy down (hallelujah! I paid $2.19 yesterday, a price I never
thought I'd see again in this lifetime), but we cannot predict future prices
based on trending of the data alone without knowing something about the
underlying context and 'reasons' for the fluctuations?

So, how is trending used then for predictive purposes? Does it merely state
probabilities of future performance based on previous fluctuations? If so,
what parameter reveals this probability (is it the R-value?)? So, using our
example above, could we plot a trend line that gives a probability, based on
the data, of gas prices going up or down, for instance?

Thank you, anyone, for enlightening me on this subject. Are there any
statisticians in our midst on this board?

Orion

 

[smartin]

Hi again Orion,

It is good that you brought up a concrete example. Gas prices: right, we
need to know something about how this pricing works. Some things that
might be at play in the data are supply, demand, inflation, catastrophic
events (e.g., Katrina, Ike), the futures market, political environments,
etc. We can look at the net result, but unless we consider the factors
that led to that result, and how those factors might look in the future,
we probably can not make good predictions about future pricing.

So how is trending used? I do not think there is a simple answer to
this. Certainly, looking at historical data is a key piece, but I do not
think we can always look at the data in a vacuum and come up with solid
predictions. Confidence factors, R-value and the like, can help to
ensure that the data at hand is representative and sufficient to analyze.

Another way you might want to look at your data is to consider the
seasonality of trends. This is something I do all the time in my line of
business. Let's go back to the gas price model. Suppose for example the
peak prices represent high demand months such as January and July. To
see whether seasonality is predictive, lay out your data in a table like
this:

Jan Feb etc.
2005
2006
2007
2008

Next, compute the year-over-year factor changes at each point. That is,

Jan | Feb
2005
2006 = Jan 2006 / Jan 2005 | = Feb 2006 / Feb 2005
2007 = Jan 2007 / Jan 2006 | = Feb 2007 / Feb 2006

Look at each column of factors. Are they similar (in each column)? If
they are, that suggests there is a seasonal influence and you might be
able to predict the next couple years by selecting a reasonable factor
for each month and applying the factors to the latest price points.

This is what we might hope the polynomial curve would do for us, but it
is not designed for that.

Note! We need to keep in mind that at any one of these points in time,
there may be special circumstances baked in to the prices/factor
changes. E.g., Katrina and Ike caused huge, temporal price spikes. But
these were not representative of normal changes. Recently, the crumbling
futures market and decreased demand have been driving prices downward,
but I do not think these influences will continue forever (although I'm
enjoying them now!)

In addition to a statistician, an actuary would be able to give great
insight to your questions as well.

Hope this helps!

 

[Orion]

Thank you so much for this discussion! This is very helpful. I really
appreciate you taking your time to explain this. It makes sense.

If there are any other insights anyone else on this board has to offer
regarding the reliability of using polynomial trending for forecasting that
would be greatly appreciated. Especially I am interested in whether there is
a number (calculation?) that shows, given a set of data, what the probability
is that it will continue on an upward or downward trend. Then we could say,
given the past circumstances, and aside from unusual (abnormal)
circumstances, there is x% probability that this trend will continue (up or
down).

Thank you so much for any insights.

Orion