FORECAST
From Excel's Help file:
FORECAST(x,known_y's,known_x's)
X is the data point for which you want to predict a value.
Known_y's is the dependent array or range of data.
Known_x's is the independent array or range of data.
In plain English from Peter Noneley's Excel Dictionary (
http://homepage.ntlworld.com/noneley)
=FORECAST(ItemToForeCast,RangeY,RangeX)
ItemToForecast is the point in the future, (or past), for which you need the forecast.
RangeY is the list of values which contain the HISTORICAL DATA to be used as the basis of the forecast, such as Sales figures.
RangeX is the intervals used when recording the HISTORICAL DATA, such as Month number.
1. In A1:A14 put 1 to 14.
2. In B1:B8 put your data for the 8 months.
3. In B9 put =FORECAST(A9,$B$1:B8,$A$1:A8)
4. Copy the above formula to B10:B14
TREND
Again, In plain English from Peter Noneley's Excel Dictionary (
http://homepage.ntlworld.com/noneley).
=TREND(KnownYs,KnownXs,RequiredXs)
The KnownYs is the range of values, such as Sales Figures.
The KnownXs is the intervals used when collecting the data, such as Months.
The RequiredXs is the range for which you want to make the prediction, such as Months.
The TREND function is an array function and must be entered using Ctrl+Shift+Enter.
In your example:
1. In B9 put =TREND(B1:B8,A1:A8,A9:A14) - enter normally.
2. Click B9 and highlight cells B10:B14
4. In the Formula Bar, click the formula.
5. Now hit the following keys together: Ctrl-Shift-Enter
(Excel will add parenthesis around the formulas)
If there is a straight-line correlation in your data, the FORECAST function and the TREND function will return the same result.
GENERAL NOTES
A majority of optimization problems involve linear relationships between variables. Linear problems are represented by a straight-line on a graph. These include problems using simple arithmetic operations such as:
• Addition and subtraction
• Built-in function such as SUM(), FORECAST() AND TREND()
If sales are doubling or trebling, you are seeing an exponential growth. Use the GROWTH function instead of the TREND function.
Regards
Mike
This message was edited by Ekim on 2002-09-01 10:39