forecasting?

[Helen]

Hello,

Does Excel have a function that could help me predict or forecast, if you
like, what week 7's number in below example could be?

week1 11
week2 21
week3 35
week4 33
week5 39
week6 40
week7 ?


Thanks,

Helen

 

[David Biddulph]

Yes, you guessed right.

The function is FORECAST()

 

[joeu2004]

Does Excel have a function that could help me predict or forecast, if you
like, what week 7's number in below example could be?
week1 11
week2 21
week3 35
week4 33
week5 39
week6 40
week7 ?

That really depends on your interpretation of the data. I suggest
that you use the Chart Wizard to graph the data first. You can
experiment with different Trendlines. Use Options to display the
regression equation and the RSQ value. The closer to 1, the better
the fit generally.

But be sure to ask yourself: why does the data behave this way?

For example, here is what I see -- an inherently flawed analysis
because I know nothing about what's behind the numbers.

The numbers show steep linear growth in weeks 1-3, but significantly
flatter linear growth for weeks 3-6. Depending on your
interpretation, FORECAST(7,B3:B6,A3:A6) might provide a better
(linear) estimate of week 7 than FORECAST(7,B1:B6,A1:B6).

Then again, based on your knowledge of information behind the numbers,
you might decide that a parabolic equation (order-2 polynomial) yields
a better fit to __all__ of the data together, demonstrating an
expected down-turn in week 7. With Chart Trendline Options, you can
extend the regression line "forward" to see the down-turn.

In summary, using Excel functions to forecast blindly is likely to
give you GIGO results -- "garbage in, garbage out".

 

[Jerry W. Lewis]

Blind use of RSQ can also get you into trouble. RSQ will continue to
increase as you add more polynomial terms, but you will quickly begin to
overfit the data. Adjusted R-squared (deflated based on number of parameters
fit) can be more informative and is given by the Regression tool in the
Analysis ToolPak.

I agree that there is no substitute for knowledge of the physical realities
of the process that generated the data.

A simple straight line clearly does not fit the entire data set very well,
but its prediction (49.5 from FORECAST) is likely an upper bound for the week
7 value.

A quadratic would predict a sharp downturn (36.7 at week 7, from TREND) that
seems presumptuous in the absence of external knowledge of the physical
system, and this is likely a lower bound.

In between, there is little information in the numbers alone, to choose
between more reasonable models such as linear in LOG(x), leading to 44.1 at
week 7, or linear in SQRT(x), leading to 46.9 at week 7.

Jerry

 

[pkaraffa]

Blind use of RSQ can also get you into trouble. RSQ will continue to
increase as you add more polynomial terms, but you will quickly begin to
overfit the data. Adjusted R-squared (deflated based on number of parameters
fit) can be more informative and is given by the Regression tool in the
Analysis ToolPak.

I agree that there is no substitute for knowledge of the physical realities
of the process that generated the data.

A simple straight line clearly does not fit the entire data set very well,
but its prediction (49.5 from FORECAST) is likely an upper bound for the week
7 value.

A quadratic would predict a sharp downturn (36.7 at week 7, from TREND) that
seems presumptuous in the absence of external knowledge of the physical
system, and this is likely a lower bound.

In between, there is little information in the numbers alone, to choose
between more reasonable models such as linear in LOG(x), leading to 44.1 at
week 7, or linear in SQRT(x), leading to 46.9 at week 7.

Jerry











- Show quoted text -

Jerry,

I understand how you got the Forecast & Trend Function(quadratic)
fomulas
If the data started in A1: =FORECAST(A7,B1:B6,A1:A6) and =TREND(B1:B6,
{1;2;3;4;5;6}^{1,2},6^{1,2}) but I do not understand what formulas are
you using for more reasonable models such as linear in LOG(x),
leading to 44.1 at week 7, or linear in SQRT(x), leading to 46.9 at
week 7? Could you please explain?

Thanks in advance PJ

 

[Jerry W. Lewis]

If you plot the original data, and add a logarithmic trendline, that fits y
vs. LN(x).

Or you can use
=FORECAST(LN(7),y_data,LN(x_data))
Note that the prediction does not depend on the base of the logarithm, so
you could just as easily use LOG() instead of LN() (it just changes the scale
but not the shape of the presumed relationship). Also, this immediately
adapts to SQRT() instead of LOG().

The basic idea is to slow the growth in x relative to y. Another common
transformation that would shrink x a bit less is SQRT(x). To shrink x a bit
more, you could try linear in 1/SQRT(x), leading to 41.4 at week 7. Linear
in 1/x doesn't fit the data very well, but quadratic in 1/x does, leading to
42.1 at week 7 [ =TREND(y_data,1/x_data^{1,2},1/7^{1,2}) ]. The
possibilities are limitless, for instance linear in x^c where -1<c<1.
Without some knowledge of how x and y should be related, trying to make a
prediction tighter than "somewhere between 39 and 49" is no more than a guess.

Jerry

 

[pkaraffa]

If you plot the original data, and add a logarithmic trendline, that fits y
vs. LN(x).

Or you can use
=FORECAST(LN(7),y_data,LN(x_data))
Note that the prediction does not depend on the base of the logarithm, so
you could just as easily use LOG() instead of LN() (it just changes the scale
but not the shape of the presumed relationship). Also, this immediately
adapts to SQRT() instead of LOG().

The basic idea is to slow the growth in x relative to y. Another common
transformation that would shrink x a bit less is SQRT(x). To shrink x a bit
more, you could try linear in 1/SQRT(x), leading to 41.4 at week 7. Linear
in 1/x doesn't fit the data very well, but quadratic in 1/x does, leading to
42.1 at week 7 [ =TREND(y_data,1/x_data^{1,2},1/7^{1,2}) ]. The
possibilities are limitless, for instance linear in x^c where -1<c<1.
Without some knowledge of how x and y should be related, trying to make a
prediction tighter than "somewhere between 39 and 49" is no more than a guess.

Jerry




- Show quoted text -

Thank you very much Jerry for the information. Much Appreciated!

 

[Gary''s Student]

41

 

[pkaraffa]

41
--
Gary''s Student
gsnu200709







- Show quoted text -

I made a mistake on the formula above it should be
=TREND(B1:B6,{1;2;3;4;5;6}^{1,2},7^{1,2}) Trend Function(quadratic)

 

[DS-NTE]

As always the answer is 42 <vbg>

41

 

[Lori]

A quick way to make a straight line forecast is to select the numbers
and pull down the fill handle in the bottom right corner of the
selection.

For an exponential trend, drag down with the right button and select
growth trend from the shortcut menu.