Trendline Function Extraction

[Guest]

Yet another forecasting question.....

If given dates and amount of items received you can easily plot the data and
have Excel produce a fitting formula that's linear, polynomial, logorithmic,
etc.

I am working with different types of data, some linear (which is easy) some
cyclical. For the cyclical data I would like to use either a 3rd or 4th
degree polynomial to approximate the upcoming volume. Again, Excel will plot
the points and give you a formula, but you can't use it to forecast anything.

I thought of using a linear algebra approach and using this formula:

a1=INDEX(LINEST(C3:C10,B3:B10^{1,2,3,4,5}),1)
a2=INDEX(LINEST(C3:C10,B3:B10^{1,2,3,4,5}),1,2)
a3=INDEX(LINEST(C3:C10,B3:B10^{1,2,3,4,5}),1,3)
a4=INDEX(LINEST(C3:C10,B3:B10^{1,2,3,4,5}),1,4)
a5=INDEX(LINEST(C3:C10,B3:B10^{1,2,3,4,5}),1,5)
a6=INDEX(LINEST(C3:C10,B3:B10^{1,2,3,4,5}),1,6)

Where column C houses volumes and B houses dates. I thought that you could
then apply this

f(x) = (a1*x^5) + (a2*x^4) + (a3*x^3) + (a4*x^2) + (a5*x) + a6

where X is the date you are projecting volume for.

Using MS Excel XP if it matters. Any ideas?

Thanks in advance - Kris.

 

[Bernard Liengme]

See example on my ExcelTips page
best wishes

 

[Guest]

That's what I'm doing, I just don't get the same coefficients the Excel graph
comes up with.

 

[Michael R Middleton]

Kris -

(1) Your example uses a fifth-order polynomial to fit eight points. It is
likely that you are overfitting the historical data, and the results are not
likely to be useful for extrapolation into the future.

(2) On the other hand, you may really believe there is a good underlying
reason why the data are cyclical and why a fifth-order polynomial is
appropriate.

(3) If your data are seasonal instead of cyclical, and if you have enough
data to detect seasonal patterns (e.g., two or preferably more years of
monthly data), then you could use a model with trend and seasonal
components, and you might obtain useful forecasts. The "Time Series
Seasonality" chapter of my book "Data Analysis Using Microsoft Excel" has
step-by-step instructions for three methods: indicator variables,
autoregression, and classical time series decomposition.

(4) If you continue to use a fifth-order polynomial, be aware of possible
numerical instability with LINEST in pre-2003 versions of Excel. For some
discussion, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;828533
Or, go to Google Groups and search for ways to extract the more accurate Add
Trendline coefficients.

(5) Also, if you continue to use a polynomial, you can get your forecasts
using the TREND worksheet function.

- Mike
www.mikemiddleton.com

 

[Jerry W. Lewis]

If you are getting different coefficients from pre-2003 LINEST and the
chart trendline, then you have likely created an ill-conditioned fitting
problem. In that case, the chart trend is probably more correct, but
must be displayed to full precision to be computationally useful (right
click and format to scientific notation with 14 decimal places).
However, you must be careful that you are not overfitting the data, as
others have noted.

The exception would be if you are usin a "Line" chart instead of an "XY
(Scatter)" chart. The Line chart is misleadingly named, and for
trendline purposes assumes that your x-data is 1,2,3,... regardless of
what you may have specified.

Jerry

 

[Padma Rao]

I have a much more basic question. What do you use for x when x is a date?

Thanks!

 

[Guest]

Hi Padma,

I'm assuming you are trying to extract the trendline equation using SLOPE
and INTERCEPT and the x you are referring to is the one in the equation Y=Mx
+ C
If that is the case you can just use the cell reference to the cell that
contains the date or you can use the serial value of the date. i.e. Today is
the 27/03/2007 the serial value is 39168. Double click on your X values in
your chart and go to the Scale Tab and look at the maximum and minimum values
and you should get a clearer picture of what is going on.

HTH
Martin